Microsoft Excel Tutorial: Hiding part of Social Security Number

Download this workbook from: https://www.mrexcel.com/youtube/MeCptGsHsYY/

Someone from the Veterans Administration is getting data downloaded that includes the entire social security number (SSN). They only want to display the last four of the SSN. But, sometimes, they need to be able to go back and see the entire SSN.

I have two solutions today, but I bet you have something better.

First, it would be nice if Excel offered a custom number formatting code that said “There is a digit here, but we don’t want to display it”.

My first solution is a pair of VBA macros that embed the original SSN in the N() function in Excel.

The second solution runs the data through Power Query, creating a data type that displays the Last 4 of SSN, but offers a card with the full SSN.

Table of Contents
(0:00) Problem Statement Display last 4 of SSN
(1:08) Using RANDBETWEEN for SSN
(1:35) Excel Custom Number Format Idea to obscure a character
(2:06) Special number format for Social Security Number
(2:25) @@@@ Number Format repeats text four times!
(2:57) VBA Solution
(3:30) Excel N() function for including a comment in a cell
(4:30) Macro to bring back SSN
(5:13) Including quoted text in Excel VBA
(5:35) INSTR function in Excel VBA to find text
(6:15) Testing the Reveal Macro
(6:30) Data Types in Power Query
(9:30) Wrap-up