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

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

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

Edit replace - with nothing

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"doug@sunwest" wrote in message
...
HOW DO I CHANGE THE FORMAT OF A SS# FROM 123-45-6789 (text) TO 123456789
(number) in a cell ?

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

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

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

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

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

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


" wrote:

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