converting numbers to text

[PeterM]

I have a cell that is formatted as a number, I need it converted to text.
The issue is that they are 3, 4 or 5 digit numbers in those cells. I need
the following conversions to happen:

492 converts to 00492
1132 converts to 01132
80605 converts to 80605

and I need the cell in text format...I've tried using Text to Data,
reformatting the cell, everything I can think of...can anyone help?

thanks in advance for your help!

 

[Dave Peterson]

Maybe you can just give the column a custom number format:
format|cells|number tab|Custom:
00000

or you can insert another column and then use:
=text(a1,"00000")

drag it down, convert it to values and delete the original.

With the first method, the value will still be a number. In the second method,
you'll be working with text.

 

[Shane Devenshire]

Hi Peter,

If you choose the TEXT(A1,"00000") idea, then you may want to select all the
formula and choose Copy, Edit, Paste Special, Values. Finally, replace the
original numbers with the converted text.

Cheers,
Shane Devenshire
Microsoft Excel MVP