Deleting dashes from Social Security Number

[Echo]

I have a column of social security numbers and I want to delete the dashes between the numbers so the social security number is just 123456789. When I use find and replace it works but if the social security number starts with a 0 I lose that leading zero. Please help.

 

[Yogi Anand]

Hi Echo:

Welcome to the Board!

Try ... =substitute(A1,"-","") where A1 houses 123-45-6789

the result will be 123456789

 

[Echo]

Thanks for the quick reply. I keep getting an error message that Excel cannot calculate a formula. Cell references in the formula refer to formulas result creating circular reference. Now what am I doing wrong!!

 

[Yogi Anand]

Hi Echo:

If the Social Security Number with dashes is entered in cell A1, then you have to write the formula I gave you in another cell, say cell B1. Try that first, if that works for you, post back if you have additional requirements or concerns to discuss.

 

[Tricky]

If the original social security number is in cell A1, then the formula will work in any cell other than A1. If you tried to use the formula in A1, then you would have received the circular reference error.

Let's assume that the original social security number is in cell B3 and you want the number displayed in C3 without the dashes. You would type the following formula in C3 and hit the enter key: =substitute(B3,"-",""). This will simply replace or substitute the dashes with nothing ("") as there is nothing between the quotes.

 

[Echo]

Ah yes, I see now. However, that's not quite what I need to do. I have Column C with 250 social security numbers from c2 through c251 and I need to take out the dashes in each social security number but leave the number itself in columns c2 through c251. Help very much appreciated.

 

[Yogi Anand]

Hi Echo:

You have two choices -- 1. aformula based solution; 2. VBA solution.

The formula that Tricky and I have given you -- one single formula will be usable for all the entries from C2 to C250.

Is your column D empty -- if it is not -- insert a new column D. Then in cell D2 write the formula ...

=substitute("C2,"-","")

then copy this down from cell C2 through C250.

Once you have done that, we can talk about copying the results back to cells C2 through C250 and even delete the column that was inserted.

But, let us go one step at a time -- let us not try to solve every thing in one step. Let us go one step at a time.

 

[Echo]

Thanks. yes I did insert new column D and copy the formula and it worked great. Now to get the results back to Column C...

 

[Yogi Anand]

Hi Echo:

And the VBA solution -- put the following code in a module or assign it to a Button, or a Command_Button ...

Sub yFixSSnumber()
    For Each cell In [C2:C250]
        cell.Value = WorksheetFunction.Substitute(cell.Value, "-", "")
    Next
End Sub

When you execute this macro, all the Social Security Numbers in cells C2:C250 will be automatically fixes -- all the intermediary work is handled by VBA -- no formulas to copy, no columns to insert.

This solution probably best suits your need.

 

[Echo]

This also works but again have the same problem of losing the leading 0 if a social security number starts with zero??