format ssn to show w/o dashes

[kristin]

I am trying to format a sheet with cells like 000-00-
0000. I need ti to read 000000000. I have tried everthing
in the format cell menu, please help??

 

[Chris Leonard]

I am trying to format a sheet with cells like 000-00-

0000. I need ti to read 000000000. I have tried everthing
in the format cell menu, please help??

You really need to use some VB to recursively search a string and replace
the "-" with a "".

It's possible to do it in the sheet with just worksheet functions but it's
messy - unless someone else knows a better way.

Assume Cell A1 has this in 000-000-00
1. Add some extra columns at the side of the one your number is in. If your
number will always contain 2 - marks then add 2 columns

2. Place this formula in the first new column - B
REPLACE(A1,SEARCH("-",A1,1),1,"")

3. Place this formula in the second new column - C
REPLACE(B1,SEARCH("-",B1,1),1,"")

Column C will now look like this 00000000

Messy but it works!

HTH

Chris

 

[Harlan Grove]

You really need to use some VB to recursively search a string and replace
the "-" with a "".

Recursion isn't needed for this.

It's possible to do it in the sheet with just worksheet functions but it's
messy - unless someone else knows a better way.

...

=SUBSTITUTE("123-45-6789","-","")

may be cumbersome, but messy?

As for eliminating cumbersome, format the range containing the SSNs as Text,
that is, the *number* format Text, then Edit > Replace, enter a single hyphen in
the 'Find what' field, and make sure the 'Replace with' field is empty, then
click on the 'Replace All' button.

 

[Chris Leonard]

=SUBSTITUTE("123-45-6789","-","")Nice one! Didn't know there was a substitute function. you learn something
new everyday!