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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Input​

[/td][td="bgcolor:#F3F3F3"]

Output​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]1980039667511667[/td][td="bgcolor:#CCFFCC"]00001980039667511667[/td][td="bgcolor:#CCFFCC"]B2: =RIGHT(REPT(0, 20) & A2, 20)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]160024896898520[/td][td="bgcolor:#CCFFCC"]00000160024896898520[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]15737642057[/td][td="bgcolor:#CCFFCC"]00000000015737642057[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]37108985297597[/td][td="bgcolor:#CCFFCC"]00000037108985297597[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]8620660[/td][td="bgcolor:#CCFFCC"]00000000000008620660[/td][td][/td][/tr]
[/table]

 

[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:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Input​

[/TD]
[TD="bgcolor: #F3F3F3"]

Output​

[/TD]
[TD="bgcolor: #F3F3F3"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]1980039667511667[/TD]
[TD="bgcolor: #CCFFCC"]00001980039667511667[/TD]
[TD="bgcolor: #CCFFCC"]B2: =RIGHT(REPT(0, 20) & A2, 20)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]160024896898520[/TD]
[TD="bgcolor: #CCFFCC"]00000160024896898520[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]15737642057[/TD]
[TD="bgcolor: #CCFFCC"]00000000015737642057[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]37108985297597[/TD]
[TD="bgcolor: #CCFFCC"]00000037108985297597[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]8620660[/TD]
[TD="bgcolor: #CCFFCC"]00000000000008620660[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]01234567890123456[/td][td]00001234567890123450[/td][td]B2: =TEXT(A2,REPT(0,20))[/td][/tr]
[/table]

 

[Rick Rothstein]

I don't think so:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]01234567890123456[/td][td]00001234567890123450[/td][td]B2: =TEXT(A2,REPT(0,20))[/td][/tr]
[/table]

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