Adding Zeros

[mooretr7]

I have a list of number, various number of digits in each cell. is there a function that I can say "put preceding zeros totaling 20 characters" so each cells has 20 characters with my original number plus preceding zeros.

thanks
Tom

[FDibbins]

excel will only take 15 digits in a number, anything over that gets replaced with 0 anyway...
123456789012345
1234567890123450
12345678901234500
etc.
Anything more than 15, you will need to enter using text format

If you have numbers with less digits than that, you can apply custom formatting with the amount of 0's to indicate how many numbers you want shown.

[mooretr7]

Great, thanks for your help

tom

[mooretr7]

I tried the custom format and it added the zeros in front of my number, but when I do the text to column (because I only need the last 4 digits) it does not see the cells as 15 digits each, it only see the original numbers. any ideas?

tom

[newdoverman]

This number format will pad the number with zeros on the right of the number to fill the width of the column. If the column width is for 20 characters, then enough zeros will be added to fill the column. The wider you make the column the more zeros are added. This however does not change the actual number in the cell. It is strictly a visual thing.


CUSTOM NUMBER FORMAT: 0#0*0

	A
1	1234567890123450000
2	1234567890123450000
3	1234567890123450000
4	1234567890123450000
5	1234567890123450000
6	1234567890123450000
7	1234567890123450000
8	1234567890123450000
9	1234567890123450000

[AlKey]

Add zeros with formula

=TEXT(A1,"00000000000000000000")

Row\Col	A	B
1	123456789012345	00000123456789012345
2	1234567890123450	00001234567890123450
3	12345678901234500	00012345678901234500