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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HOW DO I CHANGE THE FORMAT OF A SS# FROM 123-45-6789 (text) TO 123456789
(number) in a cell ?
 
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
 
Yeah, taking out the dashes makes a lot more sense that keeping the
numbers.
If you need it in a formula:
=SUBSTITUTE(A1,"-","")
 
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
 
You must log in or register to reply here.

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
How do I sum here using criteria there? 10
SSN Format 5
string formatting 5
Finding count of unique number within a specific criteria? 7
Convert Text field to Number Field 2
Multiple formats for a single cell value. 1
Masking Characters 6

Back
Top