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)

=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

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)

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)