VBA - Format Numbers as Social Security Numbers

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 info@exceltip.com

Comments

Frank
November 29, 2015 at 1:35 pm

Thank you very much
I saw the VBA Code and i will never ubderstand how it works
Thank you anyway

Reply ↓

Admin
December 1, 2015 at 5:08 am

Hi Frank,

We have made it more easy for you and included comments for each single line code for you. Please refer to the below code..

Sub SSN_Format()

\'Formatting number in SSN Format\

Dim r As Range                                                  \'defining variable r of range data type\
Application.ScreenUpdating = False                              \'disabling the screen updates till the time, macro is running\
With Range("A1").CurrentRegion                                  \'defining region which start from cell A1\
    For Each r In .Cells                                        \'using for loop for looping over all the cells in the defined region\
        With r
            .Value = Application.Text(.Value, "000-00-0000")    \'defining the format to be used in all cells\
        End With
    Next r                                                      \'end of for loop\
End With
Application.ScreenUpdating = True                               \'enabling the screen updates at the end of macro\
End Sub

We hope it works and you understand the code that how it works. We focus more on our users' understanding than just blogging.

Happy Learning!
Site Admin

Reply ↓