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

D

doug@sunwest

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

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
 
J

justinfoote

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

Ron Coderre

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting Text in cells that already have text entered 5
Masking Characters 6
How do I sum here using criteria there? 10
Finding count of unique number within a specific criteria? 7
Formatting question: how to format duration (time spent)? 0
07 Pivot Table side by side row labels 3
Formatting a Combo box 12
Countif on zip codes that vary in format 6

Top