Some of the Excel files I work on have hidden quotes in the cells. For example:
123 Main St is what you see displayed.
'123 Main St is what you see when you click on a cell.
Here’s the thing: I want to format the ZIP codes because many of them are on the East Coast and are missing leading zeroes. The hidden quote won’t allow me to do this. I’d rather not change them individually.
How do I turn off the single quotes?
I believe the ’ in Excel is put in manually so Excel doesn’t choose the formatting. I don’t believe you can turn off the single quotes unless you do it manually.
The single quote tells Excel that the content of the cell is to be treated as a string of text, instead of whatever numeric format (number, date, etc) that Excel would try to shoehorn it into.
Have you tried using search and replace to get rid of the quotes?
As has been said, it’s a text string marker, from back in the days when Excel needed 100% Lotus 1-2-3 compatibility. Search and Replace should take care of it.
Explain what you can’t do again, though, because I’m not getting it. Having the ’ before the zip code should prevent Excel from dropping that leading zero.
Just click on the column and do a search and replace.
I know a trick I invented for converting leading zeros. Start a new column. Just use: right(100000+your_cell,5). Fill down. Convert to a hard value rather than just a formula. If you understand what I am saying, it should take less than 1 minute for the whole process.
Excel includes a number format for ZIP codes. Highlight the cells to be changed and then press CTRL+1 (format cells) and go to the Number tab. Under “special” there are choices for Zip Code and Zip Code + 4.
When I look at the ZIP code column, East Coast ZIPS are missing leading zeroes. So I formatted the column as Special|ZIP codes. The leading zeroes still do not appear. I tried Custom |000000. Same result (or lack of result). When an individual cell is opened (double-clicked, or as seen in the formula bar) there is a ’ before the number. This is true for all cells, not just the ZIPs. But the ZIP codes are what I need to reformat.
Tried that. It doesn’t see them.
Not sure what you’re getting at. What do I do after I right-click? Also, if I cut and paste the ZIPs, they are pasted with the 's.
That’s the first thing I tried.
If the cells already have the ’ marker, then, no, changing the format won’t fix anything. The ’ marker overrides the cell formatting. It is old magic from a time before cell formatting. If search and replace doesn’t see the ’ markers, I think you’ve got no other choice but to take them out manually.
Unless you want to try exporting that column as text, and see if Notepad can see them or something.
Its a neat trick thanks.
Put in his formula next to your column and drag it all the way down, it will make a cell next to the first one identical except with a zero in front.
Insert new column next to your column of zip codes.
Enter this formula:
=VALUE(RIGHT(A1,5))
(assuming the cell in A1 has your zip with the apostrophe), then format as zip code.
Copy to all rows.
Copy all cells in the new column and paste “values” on top.
Delete original column with bad zips.
Wait, you’ve got a column of ZIP codes, some of which are only 4 digits long because they lack leading zeros?
Here’s the kinda-manual way without having to touch each cell. For simplicity, I’m gonna say your column is column A. Add a new column B. In the row where the ZIP codes start (I’m gonna say row 1), type the formula
=len(a1)
Now copy that formula all the way down. Now sort by column B so all your 4s are together. Add a new column C, and in C1 type the formula
="0"&A1
Using the concatenate function ("&") tells Excel it’s text, so you shouldn’t have a problem. Now just copy and paste your new 5-digit, leading-zero-having ZIP codes over the old ones.
Great minds think different some times, too. 
I still don’t know what you mean about ‘right’. If I right-click I get a menu of things to do, with nowhere to type stuff in. I have to choose an option (such as Format or Column width).
But taking a clue from the addition, this is what I did:
[ul][li]Insert two columns.[/li][li]In the top column, type 1000000000+G2-1000000000[/li][li]Copy the cell[/li][li]Highlight the next cell and Shift+click on the bottom cell[/li][li]Copy the column[/li][li]Paste Special, Formula into the second added column[/li][li]Format as ZIP code[/li][li]Delete the original column and formula column[/ul][/li]
So problem solved! Thanks for the help.
EDIT: Thanks for the suggestions posted while I was typing this. I’ll try them next time. (Although for this particular file I’m going to write an Easytrieve to compare a current file with the previous file and use the previous information for matched records.)
Reverse copying the column and pasting the formula. Gotta paste the formula before copying the column.