Social Security Numbers (SSNs) are important personal identifiers used in the United States. They typically consist of nine digits and are often presented without dashes.
However, for better readability and organization, it’s best to have dashes, but this can be tricky depending on how many SSNs you have in your data.
Fortunately, it’s a straightforward process.
To add dashes to SSN in Excel:
Select the cells with SSNs.
Go to the “Home” tab in the Excel ribbon.
Find the “Number” group.
Select “Special.”
In the “Format Cells” dialog, choose the “Social Security Number” category.
Click “OK.”
But wait, there’s more! You see, sometimes your dataset can have thousands, or hundreds of thousands, if not millions of SSNs, and then you could need different methods.
Luckily we have 6 great methods to help you out.
Read on.
6 Methods to Add Dashes to SSN in Excel
In this section, we will go over 6 methods to add dashes to SSN in Excel.
The 6 methods are:
Using the TEXT Function
Using the CONCATENATE Function
Using Flash Fill
Using Special Format
Using a Custom VBA Function
Using Power Query
Method 1: How to Add Dashes to SSN Using The TEXT Function
To add dashes to SSNs using the TEXT function in Excel, you’ll transform the numeric SSN into the standard SSN format (XXX-XX-XXXX).
Suppose we have the following dataset:
To add dashes to SSN, enter the following formula in a new blank cell:
=TEXT(A2, "000-00-0000")This formula uses the TEXT function to format the number in A2 as text with the pattern 000-00-0000, where each 0 represents a digit from the SSN.
Press Enter. Excel will display the SSN with dashes, following the XXX-XX-XXXX format.
Method 2: How to Add Dashes to SSN Using The CONCATENATE Function
To add dashes to SSNs using the CONCATENATE function in Excel, you can construct a text string that includes the SSN parts separated by dashes.
Enter the following formula in a new cell:
=CONCATENATE(LEFT(A2,3), "-", MID(A2,4,2), "-", RIGHT(A2,4))LEFT(A2,3) gets the first three characters (or digits) from the SSN. “-“ adds the first dash.
MID(A2,4,2) extracts the two characters starting from the fourth position, giving you the middle segment of the SSN. Another “-” adds the second dash.
RIGHT(A2,4) grabs the last four characters of the SSN.
Press Enter after typing the formula. Excel will now display the SSN in cell B2 formatted with dashes.
Copy the formula down to other cells by dragging the fill handle downward to cover the range you need.
Method 3: How to Add Dashes to SSN Using Flash Fill
With Flash Fill, you can easily format data, including adding dashes to SSNs. Flash Fill recognizes patterns in your data entry and automatically fills in the rest of the data for you based on that pattern.
In a new cell, manually enter the SSN with dashes.
For example, if the SSN in A2 is 123456789, you would type 123-45-6789 into B2.
After typing the formatted SSN, select cell B3 or the next cell where you want the pattern to be replicated. Go to the Data tab on the Ribbon, and click on Flash Fill, or simply press Ctrl + E.
Excel will quickly add dashes down the rest of the column, applying the same pattern of dashes to the SSNs.
Method 4: How to Add Dashes to SSN Using Special Format
You can also add dashes to SSN numbers using the special format option in the Number category on the ribbon in Excel.
To do this, select the cells that contain SSNs. Navigate to the Home tab on the left side of your screen and click on the dropdown arrow in the Number tab.
A format cells dialog box will open up. In the category, select Special, and then click Social Security Number.
Click OK to apply the changes to the selected cells.
Excel will automatically format the existing SSNs by adding dashes to it.
Method 5: How to Add Dashes to SSN Using a Custom VBA Function
Adding dashes to SNNs using a custom VBA function in Excel allows for more flexibility and automation within your spreadsheets.
This method involves writing a small piece of code to create a function that can be used directly in Excel, similar to built-in functions.
Press Alt + F11 on your keyboard to open the VBA Editor in Excel.
In the VBA Editor, right-click on any of the objects in the Project pane on the left-hand side. Go to Insert > Module to add a new module to your project.
This is where you’ll write your custom function.
In the newly created module, enter the following VBA code:
Function FormatSSN(ssn As String) As String
' This function adds dashes to a SSN
' Input: "123456789"
' Output: "123-45-6789"
If Len(ssn) = 9 Then
FormatSSN = Left(ssn, 3) & "-" & Mid(ssn, 4, 2) & "-" & Right(ssn, 4)
Else
FormatSSN = "Invalid SSN"
End If
End FunctionThis function checks if the input string has 9 characters. If so, it formats the SSN by adding dashes. If not, it returns “Invalid SSN”.
In a cell, use the newly created FormatSSN function just like any other Excel function.
Press Enter, and the cell should now display the SSN with dashes. Using Fill Handle, drag the formula down to apply it to the rest of the column as well.
Method 6: How to Add Dashes to SSN Using Power Query
Adding dashes to SSNs using Power Query in Excel is a powerful method for cleaning and transforming your data without altering the original dataset.
Start by highlighting the range of cells containing the SSNs. Go to the Data tab on the Ribbon, and click From Table/Range in the Get & Transform Data group.
If your data isn’t already formatted as a table, Excel will prompt you to create a table.
In the Power Query Editor, go to the Add Column tab. Click on Custom Column.
In the custom column formula, you can use a formula like the following:
Text.Start([SSN], 3) & "-" & Text.Middle([SSN], 3, 2) & "-" & Text.End([SSN], 4)Give your new column a name, such as “Formatted SSN”. After entering the formula and naming the column, click OK.
After you click OK, Power Query will add a new column that has SSNs with dashes.
Check out our latest clip below to upgrade your data skills.
Final Thoughts
Formatting SSNs with dashes in Excel can help maintain consistency and readability in your data.
Furthermore, learning how to add dashes to SSNs in Excel is just one step in the journey to mastering data management. With this knowledge, you’ll be better equipped to handle and analyze personal information securely and professionally.
Thanks for reading and best of luck with your calculations.
Frequently Asked Questions
In this section, you will find some frequently asked questions you may have when adding dashes to SSNs in Excel.
How do I convert a Social Security Number to text in Excel?
To convert a Social Security Number (SSN) to text in Excel, you can use the TEXT function.
For example, if the SSN is in cell A1, you can use the formula =TEXT(A1, “000-00-0000”). This will convert the SSN to text with the format 000-00-0000.
What is the formula for displaying dashes in an SSN?
The formula to display dashes in an SSN is =TEXT(SSN_CELL, “000-00-0000”).
Replace SSN_CELL with the reference to the cell containing the SSN you want to format.
How to add dashes to SSN using the replace function?
To add dashes to SSNs using the REPLACE function in Excel, you can replace specific positions in the SSN string with dashes.
For an SSN in cell A1, the formula =REPLACE(REPLACE(A1,6,0,”-“),4,0,”-“) inserts a dash after the third and fifth digits.
This method assumes the SSN is stored as text. If the SSN is numeric, you might first need to convert it to text using the TEXT function, e.g., =TEXT(A1,”000000000″), to ensure proper formatting.
How do I display an SSN with dashes in Excel 2016?
To display an SSN with dashes in Excel 2016, you can use the TEXT function or apply a custom number format.
For the TEXT function, use the formula =TEXT(A1, “000-00-0000”) where A1 is the cell containing the SSN. To apply a custom number format, right-click on the cell, choose Format Cells, select Custom, and enter the format “000-00-0000”.
How do I format a Social Security Number in Excel 2010?
To format a Social Security Number in Microsoft Excel 2010, you can use the TEXT function or apply a custom number format.
The TEXT function has the syntax =TEXT(value, format_text), where you replace value with the cell reference containing the SSN and format_text with the desired format.
For example, =TEXT(A1, “000-00-0000”). To apply a custom number format, right-click on the cell, choose Format Cells, select Custom, and enter the format “000-00-0000”.
