Copy cell data as formatted

[The ants are driving me crazy]

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.

 

[Guest]

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

 

[Gord Dibben]

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 special>vlaues.ok.esc.


Gord Dibben MS Excel MVP