Forum Discussion
Kevin_Burchell
Jun 15, 2023Copper Contributor
Find and replace apostrophe
I am trying to use find and replace but the version of the apostrophe that is in the data (I need to get rid of that to do tests) is not recognised by the apostrophe that you type into the Find box. ...
- Jun 15, 2023
Does it work if you copy the apostrophe that is in the data and paste it into the find box?
=SUBSTITUTE(A2,"'","")
This formula seems to work in my sheet. If the apostrophe that i type didn't work i'd try to copy the apostrophe that is in the data and paste it into the formula.
Kevin_Burchell
Jun 15, 2023Copper Contributor
Hi, thank you.
I have made some progress. I have used the formula to create the new data below the original data (if is very difficult as I have 1400 cases). However, when I try to copy the new data into where the old data was (or delete the old data cells and shift up), everything disappears and I have to start again. I need to do this so that the new data is in amongst the rest of the original data that I want to keep.
Can you tell me a way to use the Find and Replace function to do this. Is there something I can put in Find that will do this for me. It seems so simple.
Thanks for your help, sorry to take up your time.
Thanks
Kevin
I have made some progress. I have used the formula to create the new data below the original data (if is very difficult as I have 1400 cases). However, when I try to copy the new data into where the old data was (or delete the old data cells and shift up), everything disappears and I have to start again. I need to do this so that the new data is in amongst the rest of the original data that I want to keep.
Can you tell me a way to use the Find and Replace function to do this. Is there something I can put in Find that will do this for me. It seems so simple.
Thanks for your help, sorry to take up your time.
Thanks
Kevin
OliverScheurich
Jun 15, 2023Gold Contributor
There is actually an easier solution which i've just come across.
You can select the range which has the empty cells and text cells and within the find and replace screen you can choose:
Find what: *
Replace with: 1
and then click "replace all" ("Alle ersetzen" in the screenshot).
Find what is "Suchen nach" in the screenshot.
Replace with is "Ersetzen durch" in the screenshot.
- Kevin_BurchellJun 15, 2023Copper ContributorYes, that's it! Years ago, I knew that and I am so grateful that you have reminded me. Thanks for all you help and I hope you have a great day.
Kevin