Add Leading Zero's to a Text Number in Excel 2000

[gahill]

Hello:
I'm importing a file into Excel 2000.
Column A comes in as a text field with numbers.
I need a formula to right justify the number in column B and add leading Zero's.
This must remain a text field for further exporting to another application.

 

[macropod]

Hi gahill,

That's fairly easy to do.

For example, if your data in Column A are fixed length, and you simply want to add three leading 0s, you could use ="000"&A1 in B1, then copy down.

Alternatively, if your data in Column A are variable length, and you want to add enough leading 0s to pad the data out to five digits, you could use =Text(A1,"00000") in B1, then copy down.

Cheers

 

[shetlandbob]

Or select you column and hit control-1 (or format-cells) and in the catagory select custom and enter the number of digits you want to have in your columns, i.e. this will make

1
12
123
1234
12345

change format to "00000"

00001
00012
00123
01234
12345

 

[fredericofonseca]

Changing the format is not the solution on this case as that does not change the storage format, and this is a requirement on this case according to

This must remain a text field for further exporting to another application.

gahill,

macropod has given you one solution. If you are using ADO/SQL to retrieve the records into Excel you may have other options also, but I won't explain them unless you are. Advise if you need.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[shetlandbob]

fredericofonseca :
there is no mention of the storage format in question, changing the format will work in certain situations...

 

[macropod]

Hi shetlandbob,

Changing the format won't add leading 0s on a text field either. Your solution will only work if the numbers are formatted as such, which is not what the OP wants. Also, you'd still need to use a formula to get the values into column B, as sought by the OP.

Cheers

 

[gahill]

Hello Everyone:
Thanks for your response.
I solved the problem this way.
Column A
1
12
1234
12345
Formula in Column B
=Rept("0",10-len(A2))&A2
The field being exported back to the Accounting system is 10 long without decimals, so this does the trick.
Thanks again everyone for their quick response.

Gary W. Hill

 

[KenWright]

You could have just used MacroBobs example though, padded out to 10 0s:-

=Text(A1,"0000000000")

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------