Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Please help...
Is there a way to remove hyphen from SSN without stripping out the leading zeros? I used the replace function under the Edit menu -- Find "-" and Replace with "". It worked on some SSNs but didn't on the SSNs with leading zeros. 001-23-4567 ==1234567 (expected result = 001234567) |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=substitute(a1,"-","")
will return text =--substitute(a1,"-","") will return numbers. You could use format|cells|custom and give it a custom format of 000000000. Or select your cells edit|replace what: - (hyphen) with: (leave blank) replace all And use that same custom format. NH wrote: Please help... Is there a way to remove hyphen from SSN without stripping out the leading zeros? I used the replace function under the Edit menu -- Find "-" and Replace with "". It worked on some SSNs but didn't on the SSNs with leading zeros. 001-23-4567 ==1234567 (expected result = 001234567) -- Dave Peterson |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
first format the cells containing the SSN as text.
Bob Umlas Excel MVP "NH" wrote: Please help... Is there a way to remove hyphen from SSN without stripping out the leading zeros? I used the replace function under the Edit menu -- Find "-" and Replace with "". It worked on some SSNs but didn't on the SSNs with leading zeros. 001-23-4567 ==1234567 (expected result = 001234567) |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
try this in a helper column. Could be a macro.
=SUBSTITUTE(SUBSTITUTE(a3,"-",""),"-","") -- Don Guillett SalesAid Software "NH" wrote in message ... Please help... Is there a way to remove hyphen from SSN without stripping out the leading zeros? I used the replace function under the Edit menu -- Find "-" and Replace with "". It worked on some SSNs but didn't on the SSNs with leading zeros. 001-23-4567 ==1234567 (expected result = 001234567) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Remove hyphens in text | Excel Worksheet Functions | |||
How do I remove hyphens from a product UPC? | Excel Worksheet Functions | |||
How can I remove all hyphens in a column without retyping? | Excel Discussion (Misc queries) | |||
how do i remove hyphens from between numbers | Excel Worksheet Functions | |||
How do i remove the hyphens between numbers without having to edi | Excel Discussion (Misc queries) |