Custom cell format with trailing zeros

[kdjd]

Hi,

I'm hoping to apply custom formatting to cells but I require trailing zeros (instead of applying the formatting by reading the number left to right and add leading zeros, it would apply right to left with trailing zeros).

Eg. Desired format: 00-00-0000-0000-0000-000

If 01-29-5512-8844 is entered, I would want this to appear as: 01-29-5512-8844-0000-000. Instead the custom number format gives me 00-00-0000-1295-5128-844

Any way I change leading zeros to trailing zeros?

[Ron Coderre]

Please post more examples that show the kinds of patterns you need to accommodate.

[LJMetzger]

Hi kdjd,

Try the following custom format:

Please Login or Register  to view this content.

Formatting before the first semi-colon is for positive numbers
Formatting after the first semi-colon is for negative numbers
Formatting after the second semi-colon is for zero values
Formatting after the 3rd semi-colon is for text

Lewis

[kdjd]

Thanks, Ron, here are some more examples:

If I enter:
023722015046, would want this to appear as: 02-37-2201-5046-0000-000
0237220150465004677, would want this to appear as: 02-37-2201-5046-5004-677
0237220150465004, would want this to appear as: 02-37-2201-5046-5004-000

Basically, regardless of how many numbers are entered (maximum of 19), I'd like the segments filled with zeros at the backend.

Thanks very much for your reply, Lewis, I should have used more examples as it won't always be 0000-000 that will need to entered at the end, it will depend on how many numbers are entered to start with.

Thank again!

[Ron Coderre]

With your source text in A1
this regular formula converts it to the format you want

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS COMMENT (05/31/2014):
Well, color me surprised! I didn't remember that the TEXT function coerces numeric text into numbers.
Consequently, the double-minus is unnecessary in the above formula and this works:

Please Login or Register  to view this content.

Is that something you can work with?

[kdjd]

That's a beautiful formula, Ron! Thank you! And it works perfectly. But just confirming there is no way to get this format when the entry is made in the cell, it needs to be converted to the correct format by a formula in another cell?

Thanks again, your help is greatly appreciated.

[Ron Coderre]

Excel can only handle up to 15 significant digits.

If you type this 19 digit number into a cell: 1234567890123456789
and press ENTER, it will convert to this: 1234567890123450000

Your "numbers" can have up to 19 significant digits. Consequently, they must be converted to text.

[kdjd]

Gotcha. Thanks very much, Ron, greatly appreciated.

[Ron Coderre]

Glad I could help

Be sure to mark this thread as SOLVED (from the Thread Tools menu)