Excel Display Only Last 4 Of Social Security Number - Episode 2618

Introduction

In this tutorial, we will learn how to display only the last four digits of Social Security numbers (SSNs) in Excel while keeping the full number accessible. This is particularly useful for protecting sensitive information while still allowing verification. We will explore two main methods: using VBA macros and utilizing Excel's data types feature through Power Query.

Step 1: Using VBA to Create a Macro

This method allows you to obscure the SSNs in a selected column while retaining the full number in the formula bar.

Steps to Create the VBA Macro

1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a New Module:
   • Right-click on any of the items in the Project Explorer.
   • Choose Insert, then Module.
3. Copy and Paste the Following Code:

   Sub HideSSN()
       Dim cell As Range
       For Each cell In Selection
           If IsNumeric(cell.Value) And Len(cell.Value) = 11 Then
               cell.Formula = "=N(" & cell.Value & ")"
           End If
       Next cell
   End Sub
   

4. Run the Macro:
   • Close the VBA editor.
   • Select the range of cells containing the SSNs you want to modify.
   • Press ALT + F8, select HideSSN, and click Run.

Important Notes

• This macro replaces the original SSN with a formula that displays only the last four digits.
• The full number remains visible in the formula bar, ensuring you can access it if needed.

Step 2: Using Power Query to Create a Data Type

This method utilizes Power Query to create a custom data type that shows only the last four digits while allowing access to the full SSN.

Steps to Split SSNs and Create Data Type

1. Load Data into Power Query:
   • Select your SSN data range.
   • Go to the Data tab and click From Table/Range.
2. Split the SSN Column:
   • Right-click on the SSN column header.
   • Choose Split Column, then By Number of Characters.
   • Enter 7 to split after the first three digits and two more.
3. Create a Custom Column for Last Four Digits:
   • Go to the Add Column tab and select Custom Column.
   • Use the following formula:

     Text.End([SSN], 4)
     

   • Name this column "Last Four Digits".
4. Close and Load the Data:
   • Click Close & Load in the top left corner to load the modified data back into Excel.
5. Using the Data Type:
   • You can now display only the "Last Four Digits" in your report while still having access to the full SSN.

Practical Tips

• Ensure your SSN data is formatted correctly (as text) before starting these processes.
• Always keep a backup of your original data to prevent accidental loss.

Conclusion

You have now learned two effective methods to display only the last four digits of Social Security numbers in Excel. The VBA macro method is great for quick obscuring, while the Power Query method provides a more structured approach with data types. Depending on your needs and comfort with Excel, you can choose the method that works best for you. If you have any questions or alternative solutions, feel free to share them in the comments!