Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Find and replace apostrophe

Copper Contributor

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.

11 Replies

@Kevin_Burchell 

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.

best response confirmed by Kevin_Burchell (Copper Contributor)
Solution

@Kevin_Burchell 

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.

substitute.JPG

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.
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.

@Kevin_Burchell 

=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.

replace text with 1.JPG

Hi @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

@Kevin_Burchell 

=IF(ISTEXT(A2),1,"")

 

This is the adapted formula since there are empty cells or cells with text. The formula is in cell A14 and filled across the range A14:F24. Filling the formula across this range changes the referenced cell. In the attached file you can select any cell in range A14:F24 and see which cell is referenced.

 

For example:

In cell A14 the formula is =IF(ISTEXT(A2),1,"")

In cell A15 the formula is =IF(ISTEXT(A3),1,"")

In cell B14 the formula is =IF(ISTEXT(B2),1,"")

 

if istext.JPG

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

@Kevin_Burchell 

There is actually an easier solution which i've just come across. 

find and replace.JPG

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.

 

result.JPG

Yes, 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
1 best response

Accepted Solutions
best response confirmed by Kevin_Burchell (Copper Contributor)
Solution

@Kevin_Burchell 

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.

substitute.JPG

View solution in original post