Convert SSN to Number

  • Thread starter Thread starter Deb
  • Start date Start date
D

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
 
I need toconvertSSNtonumbercurrently my cell has 111-11-1111 and
I need to format to numberican value of 111111111

Thanks for you assistance
Click to expand...

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
 
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
 
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
 
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
Click to expand...
 
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
 
=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.
 
Back
Top