Md. Sourov Hossain Mithun
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.
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.
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.
The output without dashes-
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
- How to Remove Blank Rows in Excel Pivot Table (4 Methods)
- Delete Empty Rows and Columns in Excel VBA (4 Methods)
- How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)
- Find and Delete Rows in Excel (5 Ways)
- How to Remove Metadata from Excel File (3 Methods)
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.
Soon after you will get all the output including zero at the beginning.
⏬ 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.
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.
Soon after, the Power Query Editor window will appear.
Our dataset will look like the following image after the Power Query Editor opens.
Next, Click successively-
Home > Replace Values.
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.
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.
