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.


image 48


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



    • 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.