Convert SSN to Number

[Deb]

I need to convert SSN to number currently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

 

[Deb]

I need toconvertSSNtonumbercurrently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance

I found a posting from a couple of years ago and it works, but now I
need to add 0 to SSN where 0's are in fromt.
ex: 001111111 or 022222222

thanks again

 

[Gord Dibben]

Edit>Replace

What: -

With: nothing

Watch out for leading zeros that can get dropped using this method.

i.e. 011-01-0111 wil lose the first zero and become 11010111


Gord Dibben MS Excel MVP

 

[Kristin Broggi]

Try using Edit/Replace.
In the Replace what enter a -
In the with leave it blank
~Kristin

 

[Deb]

Thanks for your comments but I think I need to clarify.

Currently by cell does not include leading zeros but I need the
leading zeros.

Current Cell 111111111
Update to 011111111

or

Current Cell 1111111
Update to 001111111

Thanks again

 

[Deb]

Thanks for your comments but I think I need to clarify.

Currently by cell does not include leading zeros but I need the
leading zeros.

Current Cell 111111111
Update to 011111111

or

Current Cell 1111111
Update to 001111111

Thanks again

 

[Gord Dibben]

If all SSN's are same length to start with you can custom format as 000000000(9
zeros) or enter this in an adjacent or helper column.

=IF(LEN(A1)<9,"0"&A1,A1) copy down.

Assumes column A has the edited SSN's

Format all cells as text.

Guess it depends on whether or not you use the data for calculations.

If you want them to be numbers there would be no reason to add back the leading
zero.


Gord

 

[David Biddulph]

=REPT(0,9-LEN(A1))&A1 if you want text, or just format the cell to 000000000
if you want to leave as a number.