Add an ending zero to a bunch of numbers

[siasarma]

I am trying to add a zero to the end of a series of numbers. I have a list of 20,000 customer numbers, some with 5 digits and some with 6 digits. Example some are 123456 and some are 12345. I need to make them all 6 digits long, so I decided to add a leading zero to them. I made a custom format and selected 000000, which made it 012345, which worked perfectly. Now however I found out that I need the zero on the end, so 123450. Any ideas on how to format this???

 

[xld]

custom format of

[<99999]00000\0;General

 

[ExcelChampion]

FYI, this will not actually add a zero to the end of the series. It will simply *appear* with the zero. So, if you perfrom, say, a lookup on said series you may get improper results if you're trying to lookup "012345" but your formatted series is actually "12345".

 

[texasalynn]

this will require you to create this formula in another column
=IF(LEN(H18<6),(H18&0)+0,H18)
note change the cell reference to your criteria and copy it down.
You can then copy the formula column and paste special - values over the original cells. Then delete the column with the formula

HTH

 

[texasalynn]

Also if you have the ASAP utility this is a feature that it can perform for you
www.asap-utilities.com

 

[siasarma]

I would like to keep the formula in the same row and not have to put it into a different row. So thank you XLD!!! Your format worked perfectly! Can you explain it to me...I'm not sure I quite get it. Thanks for your help!

 

[Scott Huish]

Since you already understand the 00000 part, I won't explain that.

For numbers less than 99999, it will format as 00000 with the literal character 0 to be followed by this. That's what the backslash means, that the next character is literally that character, so it will ignore the usual function of 0 and put the character 0 at the end. Negative numbers will be presented in the General format.