In the United States, a Social Security number (SSN) is a nine-digit number issued to U.S. citizens, permanent residents, and temporary (working) residents. The number is issued to an individual by the Social Security Administration, an independent agency of the United States government. Although its primary purpose is to track individuals for Social Security purposes, the Social Security number has become a de facto national identification number for taxation and other purposes.
If you receives employee lists where the Social Security numbers (SSN) are not in the same format i.e. some are text, numeric, and general or may have dashes. In order to remove dashes, you can simply use Find & Replace command or use shortcut key CTRL + H.
You can format a number as a Social Security number. For example, you can format a 9-digit number, 123456789 as 123-45-6789.
In this article, we will learn how to convert numbers in SSN format using VBA code.
Question: I have a list of social security number & I want to convert all of them into the given format: (000-00-0000), using the VBA code.
In order to convert numbers into SSN format, we need to follow the below steps to launch VB editor:
- Click on Developer tab
- From Code group select Visual Basic
- Copy the below code in the standard module
Sub SSN_Format() Dim r As Range Application.ScreenUpdating = False With Range("A1").CurrentRegion For Each r In .Cells With r .Value = Application.Text(.Value, "000-00-0000") End With Next r End With Application.ScreenUpdating = True End Sub
- We will run the macro & we get the SSN in the required format; refer the snapshot below:
Conclusion: In this way, we can use excel format feature to display social security numbers in proper formatting.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]