Zero Fill a range of unique numbers

[tl17339]

Thanks for your advice. I have thousands of lines of unique 16 digit numbers. I would like to front zero fill digits 17 thru 20. Thanks again - Tom

 

[footoo]

Format the cells with twenty 0's

 

[tl17339]

Thanks, but not sure what you mean

 

[shg]

I have thousands of lines of unique 16 digit numbers.

You will need to store them as Text, because Excel will only never show more than 15 significant digits for numbers. Then you can use a formula:

	A​	B​	C​
1​	Input​	Output​	​
2​	1980039667511667	00001980039667511667	B2: =RIGHT(REPT(0, 20) & A2, 20)
3​	160024896898520	00000160024896898520
4​	15737642057	00000000015737642057
5​	37108985297597	00000037108985297597
6​	8620660	00000000000008620660

 

[Rick Rothstein]

You will need to store them as Text, because Excel will only never show more than 15 significant digits for numbers. Then you can use a formula:

	A​	B​	C​
1​	Input​	Output​
2​	1980039667511667	00001980039667511667	B2: =RIGHT(REPT(0, 20) & A2, 20)
3​	160024896898520	00000160024896898520
4​	15737642057	00000000015737642057
5​	37108985297597	00000037108985297597
6​	8620660	00000000000008620660

<tbody>
</tbody>

This formula in cell B2 will also work...

=TEXT(A2,REPT(0,20))

as will this one if you want to eliminate one of the function calls...

=TEXT(A2,"00000000000000000000")

 

[shg]

I don't think so:

	A​	B​	C​
2​	01234567890123456	00001234567890123450	B2: =TEXT(A2,REPT(0,20))

 

[Rick Rothstein]

I don't think so:

	A​	B​	C​
2​	01234567890123456	00001234567890123450	B2: =TEXT(A2,REPT(0,20))

Hmm, you are right (of course)... bad testing on my part.

 

[footoo]

If all the numbers are 16 digits (as stated in post #1 ) and presumably formatted as text, all that is needed is :

="0000" & A2