Apostrophe’s everywhere

Topic

New Reply

I have name and address data imported from Outlook in an Excel 2010 data sheet. For some reason there is an apostrophe (‘) at the beginning of each field. The apostrophe doesn’t show up in any of the Excel cells, but it shows up in the formula bar when a cell is selected. I’ve tried replacing all apostrophes with nothing, but they remain in place. Any ideas on how to get rid of these or why they are there in the first place?

Reply | Quote

Replies

Putting an apostrophe in front of a numeric field makes it appear as text to Excel…

For example, this enables you to enter ‘001376 and have it appear in the cell as 001376, not transformed to become the numeric value 1376. Sometimes you actually want to display leading zeroes!

BATcher

Plethora means a lot to me.

Reply | Quote

I’ve been trying to get rid of useless apostrophes for year’s. Oops, there’s another one

Reply | Quote

Chuck,

If you copy the cells in Excel and then Paste Special Values right back into the same cells it will get rid of the apostrophies.
Also Exporting the file from Excel to a .CSV and then reloading the .CSV seems to get rid of them also. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks to everyone. I now have a solution to my problem.

Reply | Quote

FWIW, clearing formats should also remove the apostrophes.

Reply | Quote

Plenty of good suggestions above. I found in older versions of Excel that cells formatted as text were given a “hidden” apostrophe at the beginning of the cell that did not show up in the formula bar. On formatting as a number that hidden apostrophe remained and the cell continued to behave as text. Nevertheless, editing the cell in the formula bar, putting the cursor at the left edge and hitting backspace DID delete the apostrophe and all returned to sanity.

I believe you can trace that apostrophe all the way back to Visicalc. It was certainly a common feature of Lotus 123. These days it should be consigned to the dustbin of history.

Incidentally, trivial point in an Excel discussion but I think it is good practice in data handling to either replace all apostrophes with another character like “`” (from the key to the left of the number row) or remove them. If you ever feed your data into SQL Server or another database apostrophe is a text delimiter and can trigger disaster. For a humorous reason why, see http://xkcd.com/327/

Reply | Quote