format ssn to show w/o dashes

  • Thread starter Thread starter kristin
  • Start date Start date
K

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??
 
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??
Click to expand...

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
 
You really need to use some VB to recursively search a string and replace
the "-" with a "".
Click to expand...

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.
Click to expand...
...

=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.
 
=SUBSTITUTE("123-45-6789","-","")Nice one! Didn't know there was a substitute function. you learn something
new everyday!
 
Back
Top