Today, you’ll learn how to remove dashes from SSN in Excel using proven and intuitive methods!

Think of managing Social Security Numbers (SSNs) in Excel like preparing a detailed document for an important meeting. The information is all there but in cluttered formatting—like unnecessary dashes.

Removing these dashes might seem tricky at first, especially if you’re dealing with hundreds or even thousands of entries. That’s where this guide steps in. Following various approaches, I’ll break down the process into simple and actionable steps.

By the end of this tutorial, you’ll master a quick and efficient way to clean up SSN formatting in Excel. Let’s get started!

Using the Flash Fill Tool

The Flash Fill feature of Excel is an excellent no-code approach to train Excel to do things that you’d otherwise do manually. For example, you can manually remove the dashes from a few SSNs at the beginning of the column. Then, apply this command to mimic your steps automatically. It can however follow your commands only if it detects a pattern in your actions.

For instance, you’ve got a bunch of SSNs in column A. You’d like to remove the dashes from the default SSN format and place the remaining numerical value in column B.

So, go to B2 and manually type the SSN without the dashes.

Repeat the same step for a few other cells in the column.

Now, select the entire column starting from the cell B2 and click on the Flash Fill command button in the Data Tools block of the Data tab.

Excel will copy your editing pattern and delete the dashes in less than a second.

A big drawback of this method is you won’t find it in Excel editions before Excel 2016.

Using the Find and Replace Tool

If you don’t see the Flash Fill tool in your Excel desktop app, you can use this alternative approach.

Go to the source worksheet and press Ctrl + F to bring up the Find and Replace dialog box.

In the Find what field, type a dash symbol.

Now, go to the Replace tab and ensure that the Replace with field is empty.

Hit the Replace All button to delete all dashes of the SSNs.

If you wish to perform this action in a selective cell range, highlight the source cell range. Repeat the steps mentioned above.

Using the SUBSTITUTE Function

The SUBSTITUTE function in Excel is great when you need a quick, automatic way to remove dashes from SSNs. Instead of doing it manually, this formula lets you replace dashes "-" with nothing "" and updates instantly if the SSNs in your data change. It’s super handy for big datasets where keeping everything up-to-date is a priority.

Go to the worksheet where you’ve got some SSN entries.

Suppose, your SSNs are in column A, starting from the cell A2.

You want to get the newly formatted SSN in column B starting from cell B2.

In B2, enter the following formula:

=SUBSTITUTE(A2, "-", "")

Change the cell address reference in this formula according to your own dataset.

Hit Enter to calculate the cell.

Now, click on the fill handle of B2 and drag it down until it reaches the last cell in column B that corresponds to an existing value in column A.

Excel will copy and paste the formula into the rest of the cells and simultaneously show SSNs formatted without the dashes.

You can use this function in all Excel versions since Excel 20076.

Using the Power Query Editor

Power Query is ideal for removing dashes from Social Security Numbers (SSNs) when working with large datasets or when you need to automate the process for repeated use. It’s specifically useful when dealing with SSNs embedded in a complex data structure, such as multiple columns or merged datasets. The advantage of using Power Query in such cases is that you can apply the transformation once, and it will consistently clean the data whenever it is refreshed or imported.

You can import the source dataset into the Power Query Editor in two ways. For example, you can connect an external data source or import from an existing workbook in the local storage. Let me walk you through both of the processes separately.

Go to the Excel Data tab. Click on the Get Data command.

You can now hover over the preferred primary data source, like From Database.

When you see the overflow menu on the left side, choose the secondary data source, like From Oracle Database.

Follow on-screen instructions to import the data to Power Query.

Contrarily, when you’d like to export a dataset from the active worksheet, select the source data.

Click on the From Table/Range command in the Data tab.

Select OK in the Create Table dialog box.

You should see your data in the Power Query Editor.

Now, right-click on the SSN column in Power Query and select the Replace Values option.

The Replace Values wizard will show up. There, type in the dash character in the Value To Find field.

Keep the Replace With field empty.

Click the OK button to delete all dashes from the SSN dataset.

Go to the File tab and select the Close & Load To option.

You’ll now see the Import Data dialog box. Select the Existing worksheet option and click on the cell in the active worksheet to import reformatted SSNs.

Select OK on the Import Data dialog box to finalize the Power Query to Excel worksheet data transfer process.

Excel will import the transformed dataset into the active worksheet.

Congratulations! You’ve successfully removed dashes from SSNs using the data transformation process in Power Query.

Using Power Automate

If you wish to delete dashes from SSNs in Excel workbooks in bulk, you can use Power Automate. It allows you to create an automated flow and remotely trigger it so that Microsoft 365 performs the work in the background while you can focus on important stuff.

If you’re using Excel for the web along with a Microsoft 365 subscription, follow these steps:

Setting Up an Automated Flow

Visit the Power Automate Home page using your Microsoft 365 account.

Click on the Create button in the left-side navigation panel and select the Automated cloud flow card on the right.

The Build an automated cloud flow wizard will show up.

Enter a name in the Flow name field that you can remember in the future.

Use the Choose your flow’s trigger field to find and select the OneDrive for Business When a file is created option.

Hit the Create button at the bottom of the wizard.

You should now see the Power Automate canvas with the first action When a file is created.

Click on that and make the following adjustments:

• Folder: Use the field to choose the OneDrive location where you’ll keep the workbooks with SSN datasets.

Now, click on the plus icon below the first action to add the second action. Click on the Add an action button.

Use the Add an action sidebar to lookup and add the Run script action.

You’ll now see the Run script Parameters sidebar. Use that to select appropriate data for the following fields:

• Location: Set to OneDrive for Business.
• Document Library: Choose OneDrive.
• File: Type / and select Insert dynamic content. Then, select the File identifier code from the overflow menu.
• Script: Set the Office Script you want to run. I’ve shared later below how to set that up in your Excel web app along with the exact script to be used.

Your flow is ready. Click the Save button in the top right corner.

Running a Flow

Now, click on the Test button near the Save button.

Select the Manual option in the right-side navigation panel and click on Test.

Go to the OneDrive folder you selected for this automated flow and create an Excel workbook containing SSNs.

You should see the Your flow ran successfully message when it processes the newly created workbook.

Find above an example of the real workbook that I transformed using this Power Automate flow.

Creating the Script

Open a new Excel workbook in Excel on the web and go to the Automate tab.

Click on the New Script command.

Copy and paste the following script inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    let sheet = workbook.getActiveWorksheet();

    // Get the used range
    let usedRange = sheet.getUsedRange();

    // Get all values in the used range
    let values = usedRange.getValues();

    // Iterate over each cell in the used range
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            if (typeof values[i][j] === 'string') {
                // Remove dashes from the string value
                values[i][j] = values[i][j].replace(/-/g, '');
            }
        }
    }

    // Set the updated values back to the used range
    usedRange.setValues(values);
}

Click Save script.

Find the More options icon to the right of the Save script button, click on that, and select the Rename option.

Enter a name that you can remember, like Remove Dashes.

Conclusions

So these are some of the proven ways to remove dashes from SSNs to reduce clutter or to adjust formatting as your organization prefers.

Try the method that suits your work and the level of Excel expertise.

Did you learn a new Excel skill today from this guide? You can comment below to let me know.