Formula Needed to Add Missing Leading Zeros to Number String

[Vega10]

Hello.

Thank you in advance for any help/guidance you all can provide.

My issue is as follows:
I have standardized report I use on a daily basis, this report contains social security numbers(SSN) without the leading zeros. I need to add the leading zeros to be able to use the correct SSN for other reporting needs.

I have tried to use the Special Number formatting for SSNs, the issue I have with this is that it only changes the appearance of the number, the actual number data does not change.

Is there a function, or set of functions I can use to easily add the leading zeros to a column?

Example below: [TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Current Report Style
[/TD]
[TD]Needed Report Style
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]0035
[/TD]
[/TR]
[TR]
[TD]60522402
[/TD]
[TD]060522402
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[circledchicken]

Hi and welcome to the forum,

I am not familiar with SSN's - what is the rule for determining how many leading zero's should precede each number?

 

[Vega10]

Hello!

It depends, it could be anywhere from 1 to 3. It's different for each SSN. The standard format of the SSN is 000-00-000, my report does not include the dashs and I have two reports, one with the full set of numbers (000-00-000) and one with the last 4 (0000).

Thank you,
EV

 

[circledchicken]

Sorry - can you explain that again and maybe provide a few more examples?

What does it depend on? How do you know how many 0's you need to add to a given number?

 

[shawnhet]

For the full set of numbers this should work

=REPT(0,8-LEN(A1))&A1

and for 4

=REPT(0,4-LEN(A1))&A1

Cheers,

 

[circledchicken]

For the full set of numbers this should work

=REPT(0,8-LEN(A1))&A1

That wouldn't work with the second example in the original post though.

 

[Vega10]

You are amazing, shawnhet! Truly!!

It worked, with one minor correction:

"For the full set of numbers this should work

=REPT(0,9-LEN(A1))&A1
"
For some reason the length of the full SSN registers as 9 not 8.

Thank you! Thank you!!!
and Thank you to circledchicken for trying.

 

[drgracer]

Social Security numbers are nine digits, not eight, with format of 000-00-0000. You need to change the above formula to =REPT(0,9-LEN(A1))&A1

 

[Vega10]

Yes, you are right! That was my mistake in my original posting. I forgot the last zero(0).

Thank you for the help! =)

 

[shawnhet]

Thanks for the kind words, Vega! Glad I could help!