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

Microsoft Excel Tutorial: Hiding part of Social Security Number

Welcome to Episode 2618 of MrExcel's netcast, where we tackle the question of how to display only the last four digits of a Social Security number in Excel. This is a common issue for those of us in the United States, where we all have a Social Security number with three digits, two digits, and four digits. The VA has figured out that the last four digits, combined with your last name, are enough to verify your identity. But what if we only need to see the last four digits in our Excel report, while keeping the full number available just in case? In this episode, we explore two solutions to this problem.

The first solution involves using VBA to create a macro that will obscure the Social Security numbers in the selected column. This macro will replace the original number with a formula that displays only the last four digits, while still retaining the full number in the formula bar. This solution requires VBA and will only work on Windows or Mac versions of Excel. However, it does not require any additional data or changes to the original data set.

The second solution utilizes the data types feature introduced in Excel 2018. By creating a data type for the last four digits of the Social Security number, we can display only those digits in our report while still having access to the full number if needed. This solution does not require VBA and can be used on Windows, Mac, or Excel Online. However, it does require some additional steps in Power Query to split the original data and create the data type.

I understand that these solutions may not be ideal for everyone, and I am always open to hearing about better ways to solve this problem. If you have a different solution or any questions or comments, please leave them in the comments section below. And as always, if you enjoy these videos, please like, subscribe, and ring the bell to be notified of future episodes. Thank you for watching, and I'll see you next time for another netcast from MrExcel.

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

[MrXL]

I received a great workbook from Rico S. His macro would change the first 7 characters of the cell to white font and 1 point font.
Anyone looking in the grid or printing would see the last 4. The formula bar would show all.

[AccessAnalytic]

Hey Bill, how about =HYPERLINK("#"&B4, RIGHT(B4, 4) ) then hide column B. On Hover the full code will show (I think )

[chrism]

First thing I thought was Power Query. Thanks Bill, clever!

[kathrynwalker]

This not might seem very "techy" but it worked! I used the =CONCATENATE("***-**-", RIGHT(F7, 4)) to get the last four format, copied and pasted the column in word, which removed the formula and kept the numbers correct and repasted them into a new column in excel and was able to delete the other two. No having to worry about hiding the original column or anything like that.

[richardhay]

Just finally got Pyton for Excel today (8/29). Finally! Somthing to learn!!

[AccessAnalytic]

That PQ DataType solution is VERY cool 👏🏻👏🏻👏🏻👏🏻

[YvesAustin]

I think this is the solution you just mention from Rico S.
Selecting a cell, in the formula bar highlight the first 7 characters (assuming text separated by - ), and apply white font color. Unfortunately, Format Painter will not copy over to other cells; so it would need to be done for each cell.

In vba this code works:
set rng = ActiveSheet.range("B3:B22")
rng.Characters(Start:=1, Length:=7).Font.Color = vbWhite


I think beyond that, one would need to create an entire new font for "*" only and apply that font to the first 7 characters. Maybe that font already exists somewhere!

[Franceskineos]

What about an event driven macro the change the display format according if the cell is selected or not or may be on a mouse double klick, in VBA form some filed can be set as password mode displaying asterisk

[glennwood]

Add a column and use the formula =replace(A1, 1, 7, ""), the result is a cell with just the last 4 SSN numbers showing, the original column can be hidden to keep it out of view. You could also use IF(LEN(A1=7), REPLACE(A1, 1, 5, ""), REPLACE(A1, 1, 7, "")) this would fix the issue if the incoming text is not formatted as a SSN but as just 7 numbers without the "-", also if you had a combination of both numbers in the same column. Just my thoughts not using VBA code

[richardhay]

Place names and full number on separate sheet. Put the correspondinglist of names only in column A. In column B use an IF statement to populate the column. For the condition D1="" return for TRUE =TEXTAFTER with the second iinstace of delimiter (-), for FALSE the full SSN from the column on the second sheet. By default D1 is empty so the resut of TEXTAFTER (last 4) would show. If you type any character in D1 the full SSN values would be shown. No one else looking at the sheet would know D1 would reveal the full SSN and you would not need to remember what character would unlock the full SSN.

Alternatively in cell D1 use XLOOKUP with the lookup vaulue being the name, lookup array, the names on second sheet and return array the full SSN column. Include a provision to return blank if D1 is blank. The name could be typed, pasted or selected from dropdow. This XLOOKUP approach would reveal onlya single zt a time which might be what you want. SSN

[GM]

How about using MOD? Create a checkbox somewhere on the sheet that causes the MOD to be 10, 000 (last 4) if checked and 1 billion if not checked (entire SSN). If the full SSNs are text, then first use SUBSTITUTE to get rid of the hyphens. You could probably also create a macro with a keyboard shortcut to change the MOD from 10, 000 to 1 billion.

[DimEarly]

This is low-tech compared to the other solutions, but I guess that's what you're aiming for...

If the number is formatted as text, you can just right align it, and shrink the column until only the last 4 digits are visible (assuming there's some data in the column to the left, like in your example). Then click in the cell to see the full SSN in the formula bar if needed.

The only downside is this won't work if it's formatted as a number, and that distinction is pretty finicky for non-Excel users - but it looks like you're expecting it to be formatted as text.