QUICK FORMAT WITH APOSTROPHE

[William]

I have a column with several thousand rows in the 5-digit Special zipcode
format. I'd like to keep the 5-digit values the same (some begin with a
leading zero), but need to have the data in the text format that BEGINS WITH
AN APOSTRAPHE. Is there a way to convert the entire column quickly?

 

[jb_tenor1]

This may seem convoluted, but it will work the fastest. Say, for instance
that your data is in Column 'A'. Highlight column 'B,' and format the cells
as text. Then open Notepad and copy column 'A' from Excel (make sure that
you copied them while they were in the 5-Digit special zip format) and paste
them into Notepad. They should have retained their preceeding zeroes.

Then copy the values out of Notepad, put your cursor into cell B1 and simply
paste them back into Excel. All of the zip codes will be left-aligned in the
cell with a green carrot in the upper, left corner, indicating that a number
has been stored as text. Then, in cell C1, type the following formula:

="'"&B1

Copy the formula all the way down. Once that's done, copy column 'C' and
Paste Special > Values to remove the formulas.

Like I said, a little convoluted, but it gets the job done quickly.


Jeff

 

[Gord Dibben]

I always thought "carrots" were orange?


Gord Dibben MS Excel MVP

 

[Bob I]

The part of a carrot exposed to the sun while it is growing will be
green. ;-)

 

[William]

Thanks, Jeff. Worked perfectly and saved me a lot of grief.

 

[jb_tenor1]

Excellent! Aside from what I've posted, I have not found another easy way to
do this. I will be interested to see if anyone else has a different approach.

Jeff