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.
OliverScheurich
Jun 15, 2023Gold Contributor
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 there, bingo, that works perfectly! Thank you so much, that is going to help me with lots of variables!
- Kevin_BurchellJun 15, 2023Copper ContributorHi again, could I ask a follow-up question. I have a bunch of cells with text in (different text in different columns) (and some without) and I would like to change all the cells with text in to 1. Can I easily specify 'find any text' and replace with 1? Thank you.
- OliverScheurichJun 15, 2023Gold Contributor
=IF(ISTEXT(A2),1,A2)
Does this return the intended result? If you want to return the results in range A2:F12 you can copy range A14:F24 and paste only the values into range A2:F12.
- Kevin_BurchellJun 15, 2023Copper ContributorHi OliverScheurich
Thank you.
You are operating at a level that is way beyond me! Can I ask: in =IF(ISTEXT(A2),1,A2) I can see that you specify A2 but I am wondering how you are specifying F12. Does that make sense? BTW, I do not have any cells with numbers in them, only cells with text.
Cheers
Kevin