QUICK FORMAT WITH APOSTROPHE

W

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?
 
J

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top