How To Add Dashes To Ssn In Excel
Microsoft Excel offers multiple means to add dashes to the social security numbers (SSNs). You tin apply formulas or Format Cells dialog box to add dashes to an SSN. In this commodity, you volition learn 6 methods to add together dashes to an SSN in Excel with ease.
Download Practice Workbook
You tin can download the Excel file from the following link and practice along with it.
half-dozen Methods to Add Dashes to SSN in Excel
1. Use the TEXT Function to Add together 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:
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(+)-similar icon called Fill Handle will appear.
❺ Drag downwardly the Fill up Handle icon until cell D14.
Subsequently that, you will have all the social security numbers with dashes.
Read More: How to Write Phone Number in Excel (Every Possible Style)
ii. Combine the LEFT, MID, & RIGHT Functions to Add Dashes to SSN in Excel
Yous tin can combine the LEFT, MID, and Right functions to create a formula to add together dashes to an SSN in Excel.
To practice that,
❶ First, select cell D5.
❷ At present copy and paste the following formula into cell D5.
=LEFT(B5,3)&"-"&MID(B5,4,2)&"-"&Correct(B5,iv)
In this formula:
- LEFT(B5,3) extracts 3 digits from the left side of an SSN.
- MID(B5,iv,ii) extracts ii digits starting from the 4th digit of an SSN.
- Correct(B5,4) extracts the last iv digits from the right side of an SSN.
- LEFT(B5,3)&"-"&MID(B5,iv,2)&"-"&Right(B5,4) inserts dashes (-) after the tertiary and 5th digit of an SSN
❸ Finally striking the ENTER cardinal.
❹ At present elevate the Fill up Handle icon from cell D5 to cell D14.
Finally, you will take all the SSNs with dashes equally in the picture below:
Read More: How to Format Phone Number with Extension in Excel (3 Easy Ways)
iii. Use the Supervene upon Role to Add Dashes to SSN in Excel
Incorporating the REPLACE office is another selection to add dashes to the social security numbers in Excel.
For that,
❶ Insert the following formula into cell D5.
=Supervene upon(REPLACE(B5, 4, 0, "-"), 7, 0, "-")
Hither,
- REPLACE(B5, four, 0, "-") introduces a dash (-) at the 4th position of an SSN number from jail cell B5.
- REPLACE(Replace(B5, iv, 0, "-"), 7, 0, "-") inserts another dash (-) at the seventh 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.
Later on that you lot will take all the social security numbers (SSN) with dashes as in the prototype beneath:
Read More than: How to Format Phone Number with Dashes in Excel (ii Means)
4. Add Dashes to SSN with Special Number Formatting in Excel
To add together 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.
❸ Become to the Number tab.
❹ Select Special from the Category list.
❺ Then choose Social Security Number from the Blazon department.
❻ Finally striking the OK button to apply changes.
Then y'all will see all the selected SSNs are fragmented with dashes like the following screenshot:
Read More than: Excel Formula to Alter Phone Number Format (5 Examples)
5. Utilize 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.
❷ At present press CTRL + one 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 meet the SSNs with dashes as yous set the number format.
Read More: [Solved!]: Excel Phone Number Format Non Working (four Solutions)
6. Use Flash Fill up to Add together Dashes to SSN in Excel
Wink Fill is an amazing feature embedded in Microsoft Excel 2019 and afterward versions.
You can apply this feature to add together dashes to all the SSNs in Excel.
For that,
❶ Create another cavalcade next to the cavalcade having SSNs without dashes.
❷ In the top jail cell of the new column insert dashes to an SSN manually.
❸ Then select the whole column.
❹ Afterward that go to Domicile > Editing > Fill > Wink Fill.
Subsequently clicking on the Flash Fill command, Excel volition get the design and impose dashes to all the SSNs equally in the post-obit screenshot.
Things to Remember
- Wink Fill is available in Excel 2019 and Microsoft Office 365.
- One pitfall regarding the usage of the Flash Fill up feature is that information technology doesn't support Automatic Updates.
Practice Department
You tin exercise all the methods in the post-obit do section.
Decision
To sum up, nosotros accept discussed 6 means 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 inquire any questions in the comment department below. We will endeavour to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Related Manufactures
- Format TextBox for Phone Number with Excel VBA (A Pace-past-Stride Guide)
- How to Format Phone Number with Country Lawmaking in Excel (5 Methods)
- How to Remove Parentheses from Telephone Numbers in Excel (5 Methods)
Source: https://www.exceldemy.com/add-dashes-to-ssn-in-excel/
0 Response to "How To Add Dashes To Ssn In Excel"
Post a Comment