Forum Discussion
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. Does anyone know a solution to this challenge? Thank you.
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.
11 Replies
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.
- Hi there, bingo, that works perfectly! Thank you so much, that is going to help me with lots of variables!
- Hi 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.
One way to remove an apostrophe in Excel is to select all the cells with data, then click Ctrl + H or navigate to Home >> Editing >> Find & Select >> Replace.
In the Find what field, insert an apostrophe, and leave the Replace with field empty.
Another approach is to use wildcard characters as comparison criteria for text filters, and when you’re searching and replacing content.
- Hi there, thank you. Yes, I know how to use Find and Replace. However, the apostrophe that I type into the Find box is not recognised as the same as the apostrophe that is in the data. So I get the Cannot find a match window. I am not sure that the wildcards will help with this. Any other ideas? Thank you for responding.
