How to Remove Dashes from SSN in Excel (4 Quick Methods)

The general SSN (Social Security Numbers) numbers are created by 9 digits with two dashes. The pattern is AAA-BB-CCCC. When we want to make a list of SSNs in an Excel worksheet, we may want to remove the dashes in some cases. Microsoft Excel offers several ways to do it. This article will guide you with 4 quick and effective methods to remove dashes from SSN in Excel with sharp steps and smart illustrations.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


4 Quick Methods to Remove Dashes from SSN in Excel

To explore the methods, we’ll use the following dataset which contains some random SSNs.


Method 1: Apply Find and Replace Tool to Erase Dashes from SSN in Excel

In our very first method, we’ll use the Excel Find and Replace tool to remove dashes from SSN in Excel. Using this tool is quite time-saving. Let’s see the following steps.

Steps:

Select the data range by using your mouse.

Then press Ctrl+H on your keyboard to open the Find and Replace tool.

Find and Replace Tool to Erase Dashes from SSN in Excel

After the Find and Replace window appears, type a hyphen(-) at the Find what: box, and keep the Replace with: box empty.

Finally, just press the Replace All button.

Find and Replace Tool to Erase Dashes from SSN in Excel

Now you will see that the Find and Replace tool has removed all the dashes from the SSNs.  A pop-up message box shows how many replacements are done.

But have a look at the last SSN. 👇 The zero (0) digit in the first place is gone too.

Clearly, the Find and Replace tool can’t keep zero at the beginning of a number. We can overcome this issue by using the next methods.

Read More: How to Remove Leading Zeros in Excel (7 Easy Ways + VBA)


Method 2: Use SUBSTITUTE Function to Remove Dashes from SSN in Excel

One of the best ways to replace or substitute anything in Excel is using the SUBSTITUTE function. Additionally, the advantage of using the SUBSTITUTE function is, it can keep zero in the first place of an SSN. How is that? Let’s see and execute the steps below.

Steps:

Activate Cell C5 by pressing it.

Later, type the following formula in it-

=SUBSTITUTE(B5,"-","")

Then hit the Enter button and get the output.

SUBSTITUTE Function to Remove Dashes from SSN in Excel

The output without dashes-

SUBSTITUTE Function to Remove Dashes from SSN in Excel

Now drag down the Fill Handle icon all the way to copy the formula down and remove dashes from other SSNs.

Consequently, here are our final results. And have a look! This time we haven’t lost the zero digit in the first place of the SSNs.

Read More: How to Remove Formulas in Excel: 7 Easy Ways


Similar Readings


Method 3: Combine LEFT, MID, and RIGHT Functions in Excel to Delete Dashes from SSN

We can apply a combination of LEFT, MID, and RIGHT functions to remove dashes from SSN in Excel. And it will also keep the zero at the beginning of an SSN.

Steps:

In Cell C5 type the following formula-

=LEFT(B5,3)&MID(B5,5,2)&RIGHT(B5,4)

Then just press the Enter button to get the result.

Finally, to copy the formula for the other cells, drag the Fill Handle icon over the cells all the way down.

LEFT, MID, and RIGHT Functions in Excel to Delete Dashes from SSN

Soon after you will get all the output including zero at the beginning.

LEFT, MID, and RIGHT Functions in Excel to Delete Dashes from SSN

⏬ Formula Breakdown:

➥ LEFT(B5,3)
The LEFT function will keep the first three digits of the number in Cell B5. It will return:
“675”

➥ MID(B5,5,2)
Then the MID function will keep the two digits starting from the 5th digit of the number in Cell B5. The output is:
“48”

➥ RIGHT(B5,4)
Later, the RIGHT function will return the last 4 digits of the number in Cell B5 that will return:
“8921”

➥ LEFT(B5,3)&MID(B5,5,2)&RIGHT(B5,4)
And finally, those three previous outputs will be combined using &, as a result, the final output will return:
“675488921”


Method 4: Use the Power Query Tool to Remove Dashes from SSN

Lastly, we’ll remove dashes from SSN in Excel in a different way using the Power Query tool of MS Excel It’s a bit longer compared to the previous methods but an effective and flawless way. Just execute the following steps.

Steps:

Select the range of cells B4:B9 including the header.

After that click as follows-

Data > From Table/Range.

A dialog box will open up.

Power Query Tool to Remove Dashes from SSN

It will show our selected data range in a pop-up window form where you can check if the selection is done properly. Make sure that you have marked My table has headers option.

Now just press OK.

Power Query Tool to Remove Dashes from SSN

Soon after, the Power Query Editor window will appear.

Our dataset will look like the following image after the Power Query Editor opens.

Power Query Tool to Remove Dashes from SSN

Next, Click successively-

Home > Replace Values.

Power Query Tool to Remove Dashes from SSN

A dialog box named Replace Values will appear.

Type a hyphen(-) in the Value to Find box and keep the Replace With box empty.

Finally, just press OK.

Power Query Tool to Remove Dashes from SSN

Soon after, you will see that the Power Query has deleted all the dashes. And Power Query also keeps the leading zeros before intact.

Now in the Power Query Editor window, click- File > Close &Load. Then the Power Query window will be closed and the output will be transferred automatically to a new worksheet in your workbook.

Here’s the new worksheet with the output of Power Query.


Conclusion

I hope the procedures described above will be good enough to remove dashes from SSN in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo