I was given a spreadsheet with a numeric column that should have been
formatted as text. The column should be a 3 character ID. The ID's
that happen to have all numeric characters are formatted wrong. For
example "030" shows up as 30. I know that I can format the cell to
display it correctly, but I really want the column to have the correct
data.

So my question is - Can I format the column to show 030, create a new
colunm that is formatted as text and then copy the DISPLAYED value to
my new column? I want to copy and paste 030 not 30.

I know this would be a simple macro, but I was wondering if it was
possible to accomplish without VBA.

Hi,

Try this:

In a helper or blank column type:

=TEXT(A1,"000")
copy down as far as needed then copy and paste special "Value". Make sure
the result is what you're looking for before copying and pasting.

HTH
Jean-Guy

"The ants are driving me crazy" wrote:

I was given a spreadsheet with a numeric column that should have been
formatted as text. The column should be a 3 character ID. The ID's
that happen to have all numeric characters are formatted wrong. For
example "030" shows up as 30. I know that I can format the cell to
display it correctly, but I really want the column to have the correct
data.

So my question is - Can I format the column to show 030, create a new
colunm that is formatted as text and then copy the DISPLAYED value to
my new column? I want to copy and paste 030 not 30.

I know this would be a simple macro, but I was wondering if it was
possible to accomplish without VBA.

Excel 2003

Column of numbers 30 to 50

In adjacent column enter ="0" & A1

Drag/copy down.

When happy, select adjacent column and copy the paste specialvlaues.ok.esc.


Gord Dibben MS Excel MVP


On Mon, 19 Nov 2007 14:01:04 -0800 (PST), The ants are driving me crazy
wrote:

I was given a spreadsheet with a numeric column that should have been
formatted as text. The column should be a 3 character ID. The ID's
that happen to have all numeric characters are formatted wrong. For
example "030" shows up as 30. I know that I can format the cell to
display it correctly, but I really want the column to have the correct
data.

So my question is - Can I format the column to show 030, create a new
colunm that is formatted as text and then copy the DISPLAYED value to
my new column? I want to copy and paste 030 not 30.

I know this would be a simple macro, but I was wondering if it was
possible to accomplish without VBA.