Change #'s to Text macro

[Guest]

I need to be able to do the following:

I have a list of store #'s in column D (ex below). I need to format the
column as text and for any number that is less than 10, I need it to be
changed so that it has a leading 0 that will stay when saved as a CSV file.

I'm stuck on this so any help would be great. Thanks

Current How I want it

1 01
2 02
3 03
4 04
5 05
6 06
7 07
8 08
9 09
10 10

 

[Guest]

in B1:

=TEXT(A1,"00")

where A1=1 etc

HTH

 

[Nick Hodge]

Mike

Pre-formatting the data as text will retain the leading zero's in the csv
file. Try opening it in Notepad an you'll see them.

It's the re-opening in Excel that strips the zeroes. About the only way
around this is to remove the csv extension, or save it as .txt and use
file>open in Excel and then use the text import wizard (step 3) to mark the
column as text and all will be well

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Dave Peterson]

Select the column
format|Cells|Number tab|custom category
type:
00

And save as your .csv file.

Then use Notepad to verify that it worked--don't open the .csv file in excel.
Excel will strip the leading 0's in your .csv file when it opens the file.