Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,"-","") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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,"-","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
access dates in excel, how to change format? | Excel Discussion (Misc queries) | |||
How do I change the date format when importing a txt file? | Excel Worksheet Functions | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
How do i change numbers in text format to number format? | New Users to Excel | |||
change bahttext format to different format style | Excel Worksheet Functions |