How to Add Dashes to SSN in Excel (6 Methods)

Microsoft Excel offers multiple ways to add dashes to social security numbers (SSNs). You can use formulas or the Format Cells dialog box to add dashes to an SSN. In this article, you will learn 6 methods to add dashes to an SSN in Excel with ease.

How to Add Dashes to SSN in Excel: 6 Simple Methods

1. Use the TEXT Function to Add Dashes to SSN in Excel

You can add dashes to the social security numbers (SSN) using the the TEXT function.

For that,

❶ Select cell D5 first.

❷ Then insert the following formula:

Where,

• B5 refers to an SSN without any dashes.

❸ After that hit the ENTER button to execute the formula.

❹ Take the mouse cursor at the right-bottom corner of cell D5.

A plus(+)-like icon called Fill Handle will appear.

❺ Drag down the Fill Handle icon until cell D14.

After that, you will have all the social security numbers with dashes.

2. Combine the LEFT, MID, & RIGHT Functions to Add Dashes to SSN in Excel

You can combine the LEFT, MID, and RIGHT functions to create a formula to add dashes to an SSN in Excel.

To do that,

❶ First, select cell D5.

❷ Now copy and paste the following formula into cell D5.

In this formula:

• LEFT(B5,3) extracts 3 digits from the left side of an SSN.
• MID(B5,4,2) extracts 2 digits starting from the 4th digit of an SSN.
• RIGHT(B5,4) extracts the last 4 digits from the right side of an SSN.
• LEFT(B5,3)&”-“&MID(B5,4,2)&”-“&RIGHT(B5,4) inserts dashes (-) after the 3rd and 5th digit of an SSN

❸ Finally hit the ENTER key.

❹ Now drag the Fill Handle icon from cell D5 to cell D14.

Finally, you will have all the SSNs with dashes as in the picture below:

3. Use the REPLACE Function to Add Dashes to SSN in Excel

Incorporating the REPLACE function is another option to add dashes to the social security numbers in Excel.

For that,

❶ Insert the following formula into cell D5.

Here,

• REPLACE(B5, 4, 0, “-“) introduces a dash (-) at the 4th position of an SSN number from cell B5.
• REPLACE(REPLACE(B5, 4, 0, “-“), 7, 0, “-“) inserts another dash (-) at the 7th position of an SSN number from the cell

❷ After that hit the ENTER button.

❸ Now drag the Fill Handle icon from cell D5 to D14.

After that you will have all the social security numbers (SSN) with dashes as in the image below:

4. Add Dashes to SSN with Special Number Formatting in Excel

To add dashes to SSN with special number formatting in Excel,

❶ Select all the SSN numbers first.

❷ After that press CTRL + 1 to avail the Format Cells dialog box.

❸ Go to the Number tab.

❹ Select Special from the Category list.

❺ Then choose Social Security Number from the Type section.

❻ Finally hit the OK button to apply changes.

Then you will see all the selected SSNs are fragmented with dashes like the following screenshot:

5. Apply Custom Number Formatting to Add Dahses to SSN in Excel

Another way to add dashes to an SSN is by applying a Custom number formatting from the Format Cells dialog box.

To do that,

❶ Select all the SSNs.

❷ Now press CTRL + 1 to get the Format Cells dialog box.

❸ Navigate to the Number tab.

❹ Choose Custom from the Category list.

❺ Into the Type box, insert the following formula.

❻ Finally hit the OK button.

After that, you will see the SSNs with dashes as you set the number format.

6. Use Flash Fill to Add Dashes to SSN in Excel

Flash Fill is an amazing feature embedded in Microsoft Excel 2019 and later versions.

You can use this feature to add dashes to all the SSNs in Excel.

For that,

❶ Create another column adjacent to the column having SSNs without dashes.

❷ In the top cell of the new column insert dashes to an SSN manually.

❸ Then select the whole column.

❹ After that go to Home > Editing > Fill > Flash Fill.

After clicking on the Flash Fill command, Excel will get the pattern and impose dashes to all the SSNs as in the following screenshot.

Things to Remember

• Flash Fill is available in Excel 2019 and Microsoft Office 365.
• One pitfall regarding the usage of the Flash Fill feature is that it doesn’t support Automatic Updates.

Practice Section

You can practice all the methods in the following practice section.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

Conclusion

To sum up, we have discussed 6 ways to add dashes to social security numbers (SSN) in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

Related Article

• How to Insert Dot between Numbers in Excel

<< Go Back to Excel TEXT Function | Excel Functions | Learn Excel