How to Remove Dashes from SSN in Excel (5 Useful Techniques)

When working with HR records, payroll files, or government forms, Social Security Numbers (SSNs) often appear in the standard format with dashes (e.g., 123-45-6789). While this formatting improves readability, many backend systems and data analysis tools require SSNs to be stored as pure numeric strings (e.g., 123456789) for validation, processing, or database matching. Cleaning this up manually isn’t ideal, especially with large datasets.

In this article, we’ll walk you through multiple effective methods to remove dashes from SSNs in Excel using formulas, built-in tools, and even VBA and Power Query. Let’s begin.

If you’re looking for the fastest way to remove dashes from Social Security Numbers without using formulas or tools, Excel’s built-in Find and Replace feature is your best option. This method is perfect for one-time cleanups or when working with static data that won’t need ongoing updates. It directly edits the original values, so there’s no need for helper columns or complex logic to remove the dashes.

This is the sample dataset we will be using:

Steps:

➤ Select the range of cells that contain the SSNs (e.g., A2:A11).
➤ Press  Ctrl  +  H  to open the Find and Replace dialog box.
➤ In the Find what field, type a hyphen (–).
➤ Leave the Replace with field completely blank.
➤ Click Replace All.

All dashes will be instantly removed from the selected cells, converting entries like 123-45-6789 to 123456789.

If you prefer a non-destructive and flexible solution, the SUBSTITUTE function is ideal. Unlike Find and Replace, this formula keeps your original SSNs intact and displays the cleaned-up version in a separate column. It’s especially useful when working with live data or shared spreadsheets where preserving the source is important.

Steps:

➤ In a blank column (say, cell B2), enter:

➤ Press Enter for output.
➤ Drag the AutoFill handle down for all rows.

This will convert 123-45-6789 to 123456789 and so on.

If you’re looking for a quick, intuitive way to remove dashes without writing formulas, Excel’s Flash Fill is a powerful tool that can recognize patterns and apply them across a column. It’s perfect for users who prefer a visual, example-based approach over functions or code.

With Flash Fill, you only need to type the first cleaned SSN manually. Excel will detect the pattern of removed dashes and automatically suggest the rest of the cleaned list for you. This method works best on smaller datasets or when you only need to clean up a column once.

Steps:

➤ In cell B2, type the SSN without dashes manually such as 123456789.
➤ In B3, begin typing 987654321 and Excel should show a suggestion to auto-fill the column.
➤ Press Enter to accept the suggestion.
➤ If Flash Fill doesn’t appear, go to the Data tab >> Click Flash Fill (or press Ctrl + E).

Now you will find your dashes removed in your new column.

When handling large or frequently updated datasets, Power Query offers an excellent and reliable way to clean your data without altering the original source. It creates a repeatable process that removes dashes from SSNs automatically every time you refresh your data, saving you from manual cleanup or complex formulas. This method is perfect for recurring reports or data imports where consistency and accuracy are crucial.

Steps:

➤ Select any cell inside your dataset where SSNs are listed.
➤ Go to the Data tab on the Ribbon and click From Table/Range.
➤ If prompted, confirm that your table has headers and click OK to open the Power Query Editor.

➤ In the Power Query Editor, click to select the SSN column you want to clean.
➤ Navigate to the Transform tab and click Replace Values.

➤ In the dialog box, type a hyphen (-) in the Value to Find field and leave the Replace With field empty, then click OK.

➤ After verifying that the dashes are removed in the preview, click Close & Load to load the cleaned data back into Excel.

Your SSNs will now be free of dashes in a new table, while the original dataset remains unchanged. For updated datasets, just refresh the query to apply the cleaning automatically.

When you’re working with large datasets or need to perform repetitive cleaning tasks across multiple sheets or files, a simple VBA macro can save tons of time. This method is especially helpful for advanced users or Excel pros who prefer an automated, no-click solution that edits data in-place without requiring formulas, helper columns, or Flash Fill.

The macro below removes all dashes from the selected range, preserves leading zeros by formatting cells as text, and handles thousands of entries in just a few seconds.

Steps:

➤ Select your range A2:A11 that contains SSNs.

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Insert a new module from Insert tab >> Module.
➤ Paste this code:

Sub DeleteDashes()
    Dim rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", "Select SSN Range", WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For Each rng In WorkRng
        rng.NumberFormat = "@"
        rng.Value = VBA.Replace(rng.Value, "-", "")
    Next
    Application.ScreenUpdating = True
End Sub

➤ Press  F5  key to run the macro and click OK on the pop-up after confirming your range.

This macro will clean dashes directly in the selected cells and preserve formatting.

Frequently Asked Questions

Will Excel remove leading zeros when I delete dashes?

Yes, Excel can strip away leading zeros if the cell is formatted as a number. To preserve the full 9-digit SSN, always format the column as Text before applying any dash-removal method.

What if I want to keep some dashes but remove others?

If you need to keep certain dashes (like only after the third digit), use a combination of LEFT, MID, and RIGHT functions instead of removing all dashes with SUBSTITUTE. This gives you more control.

Is Flash Fill better than formulas?

Flash Fill works well for quick, one-time cleanup jobs on small datasets. However, for dynamic data that may change or update frequently, formulas are more reliable and automatically adapt to new entries.

Can I undo a macro after running it?

No. Macros make permanent changes and bypass Excel’s undo history. Always make a copy of your original file or create a backup before running any VBA code to avoid irreversible edits.

How do I automate this for recurring reports?

Power Query is ideal for repeatable data cleanup tasks like this. Alternatively, you can save your VBA macro to the Personal Macro Workbook to reuse it automatically across different Excel files and sessions.

Wrapping Up

In this tutorial, we explored several practical ways to remove dashes from SSNs in Excel starting from simple formulas and Flash Fill to more advanced tools like Power Query and VBA. Whether you’re cleaning up a one-time import or preparing recurring reports, there’s a method here that fits your workflow. Feel free to download the practice file and share your feedback.