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

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel offers multiple ways to add dashes to the social security numbers (SSNs). You can use formulas or 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.


Download Practice Workbook

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


6 Methods to Add Dashes to SSN in Excel

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

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

For that,

❶ Select cell D5 first.

❷ Then insert the following formula:

=TEXT(B5,"???-??-????")

Where,

  • B5 refers to an SSN without any dashes.

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

Use the TEXT Function to Add Dashes to SSN in Excel

❹ 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.

Use Fill Handle to Add Dashes to SSN in Excel

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

Read More: How to Write Phone Number in Excel (Every Possible Way)


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.

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

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.

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

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

Use Fill Handle to Add Dashes to SSN in Excel

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

Read More: How to Format Phone Number with Extension in Excel (3 Easy Ways)


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.

=REPLACE(REPLACE(B5, 4, 0, "-"), 7, 0, "-")

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.

Use the REPLACE Function to Add Dashes to SSN in Excel

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

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:

Read More: How to Format Phone Number with Dashes in Excel (2 Ways)


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.

Add Dashes to SSN with Special Number Formatting in Excel

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

see all the selected SSNs are fragmented with dashes

Read More: Excel Formula to Change Phone Number Format (5 Examples)


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.

000-00-0000

❻ Finally hit the OK button.

Apply Custom Number Formatting to Add Dahses to SSN in Excel

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

Read More: [Solved!]: Excel Phone Number Format Not Working (4 Solutions)


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.

Use Flash Fill to Add Dashes to SSN in Excel

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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo