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.

 

img1

 

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

 

img2

 

  • 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

 

img3

 

  • We will run the macro & we get the SSN in the required format; refer the snapshot below:

 

img4

 

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

 
 



2 thoughts on “VBA – Format Numbers as Social Security Numbers

    • 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 *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>