Trying to get rid of leading apostrophes in text cells

I've been searching all morning for a solution and nothing has worked. I also notice something strange:

If I manually remove the apostrophe, enter out of the cell, and select it again, the apostrophe is back.

This is freaky. Any ideas what's going on here?

You could use this formula in the adjacent cell:

=RIGHT(A1,LEN(A1)-1)

then copy this down. Then you could fix the values and then delete column A.

Hope this helps.

Pete

Nothing is working.
I'm attaching a sample that has about 20 cells. Some have the apostrophe and others don't.

The data cam from multiple source. I'm going to start tracing back where these strange ones came from.

Thanks for all the help.

Here's one thing I did:

Save the sheet as a CSV
Reopen it

All of my formatting is gone but those apostrophes are gone, too.

This is a solution for now but this is freaky. This started because someone had multiple entries when he synced his phone, Outlook, iPad and other devices. Maybe something added the apostrophe and started seeing Mary and 'Mary as being unique? But why won't that apostrophe just go away?

I don't see the problem when I opened your file. I can only see the apostrophe in the Formula Bar when I select a cell.

I think this has something to do with Lotus compatibility. Click on the File button, then Excel Options (at the bottom of the panel) and then on Advanced. Scroll down to the bottom and you will see 3 options for Lotus compatibility - on my machine none of them are ticked, so if you have any of them ticked then un-tick them and click OK. Does that make any difference?

Hope this helps.

Pete

You're right. Not all of the files were Excel. There were originally 5 files that were brought together. Some were text others were CSV.

However, the problem was that there were 1500 rows stretching out to column AE. Finding everything with a hidden apostrophe and manually correcting would have been a nightmare.

That did it! RESOLVED!

Originally Posted by aelgadi

The trick is to use the ".ClearFormat" for entire sheet as follows

Public Sub Remove_Apostrophe()
    Sheet1.Cells.ClearFormats
End Sub

or for a range as follows

Public Sub Remove_Apostrophe()
    Dim sRange As Range
    ' you need to define the range first  i.e set sRange = .....
    sRange.Cells.ClearFormats
End Sub

That did it! RESOLVED!!
Thank you!

I have been fighting with this problem for an hour. Finally read that the leading apostrophe instructs Excel to "align left". I had tried using the CLEAN function, but it had not worked. This time I used the CLEAN function and then, while the formula was still in the cells, I highlighted and used the "Align Left" command. Next I copied and pasted as values and VOILIA!! the dratted leading apostrophe was gone! WOOT WOOT!!! My vlookup now works perfectly.
d.c.1