HOW DO I CHANGE THE FORMAT OF A SS# FROM 123-45-6789 TO 123456789

[Guest]

HOW DO I CHANGE THE FORMAT OF A SS# FROM 123-45-6789 (text) TO 123456789
(number) in a cell ?

 

[Peo Sjoblom]

Edit > replace - with nothing

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[justinfoote]

If you only have ss numbers - no other formats you could use this
=LEFT(A1,3)&MID(A1,5,2)&RIGHT(A1,4)
where A1 contains 123-45-6789

 

[justinfoote]

Yeah, taking out the dashes makes a lot more sense that keeping the
numbers.
If you need it in a formula:
=SUBSTITUTE(A1,"-","")

 

[Guest]

Actually, it may HAVE to be done with a formula.....

Some parts of the US issue Social Security Numbers that begin with zero (The
1st part of the SSN indicates region of the country).

Consequently, using <edit><replace> to eliminate the dashes converts the
text strings to numbers and lops off the leading zeros. Is there a non-vba,
non-formulaic technique I don't know about that will prevent that from
happening?

Special Number Format (Social Sec Num) *might* work. It would make it
appear that the leading zero is actually there, but the actual SSN values
might be required.

***********
Regards,
Ron

XL2002, WinXP-Pro