Microsoft Excel Tutorial: Hiding part of Social Security Number
Download this workbook from: Excel Display Only Last 4 Of Social Security Number - Episode 2618 Sample Files - MrExcel Publishing
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
Download this workbook from: Excel Display Only Last 4 Of Social Security Number - Episode 2618 Sample Files - MrExcel Publishing
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
Transcript of the video:
I need your help today.
I have two solutions to this one, but I don't like either solution.
Boy, there has to be something better. Episode 2618, display only the last four digits of the Social Security number, but keep the entire number available just in case.
This question came in and it resonates with me.
I used to take my father to the Veterans Hospital.
Where you are known as your last name and the last four of the Social Security number.
For those of you not in the United States, we all have a Social Security number here.
Three digits, two digits, four digits.
And the VA figured out that given your last name and the last four digits.
It's enough pretty much to make sure that it's you.
All right?
So the person here has a report that pulls data into Excel.
They get the whole Social Security number.
But they really only use the last four.
Unless it's a weird situation where we have to look at the entire SSN.
Maybe 1 out of 500 times.
So the question is, can we format the column to show only the last four digits of that number.
But have the full number stored there in case we need it?
All right.
Also, these Social Security numbers are all fake.
They were generated with RANDBETWEEN.
Don't go try and steal any of these people's identities.
It was just random numbers.
Although, I changed this one to have a zero as the leading digit.
Because I needed to test that in one of my solutions.
And I changed these two people to have the same last four.
Because I needed to test that in my solutions.
It would be amazing if in the custom number formats there was a way to hide digits.
We can do something like 1, 2, 3, 4.
But if there's more digits than that, they're always going to show us those digits.
It would almost have to be a special character.
Just to say if we put a capital X in.
That we understand there's a digit there and we want to obscure that digit.
Now, I don't know of any way to do this.
Of course we have a Special here.
Where we can format that number as a Social Security number and put the dashes in.
And I'm not entirely sure if the folks who are getting this data are getting it as text.
I'm expecting it to be as text.
Or as a number.
This solution assumes that it's as text.
And I even tried this crazy thing like, all right, so it's text.
What if we just ask for the last four digits of the text with "@@@@"?
But that actually puts the Social Security number in four times.
All right, so if you're a genius on custom number formatting.
And you know a solution to this.
Please, by all means, down in the YouTube comments, let me know.
But right now I'm assuming that there's no way that we can mask these characters to display only the last four digits.
So then I have two solutions, one involving Power Query and one involving VBA.
My idea is that we're going to select the column that has the Social Security numbers.
And run this macro called obscure SSN.
Let's go take a look at that code, right?
So hide SSN.
For each cell in the Selection.
We're going to save the original value.
And then we're going to grab the right four characters of that value.
And then check this out, I'm going to build a formula saying equal the new value.
So =1234.
And then an ampersand and this beautiful trick, the N function.
I bet you've never used the N function because it's not very useful.
The N function, if it's a number, will return the number.
But if it's any text, will return zero, right?
So what I'm doing is I'm putting the last four digits in.
And then adding the N function, which is going to add zero.
But in the formula bar, we'll be able to see the original Social Security number.
And then only if the length of the original cell was greater than or equal to nine.
Then put the formula in.
And in case one of the last four digits has a leading zero.
Make sure that we show all of the zeros like that.
All right, so let's take a look at this.
I'm going to select these cells and then obscure the SSN.
What we see here in each cell is the last four digits, but if we actually select one, like here, Ike.
We need to go check Ike's Social Security number.
Then we can look up in the formula bar and see that the original SSN was this, all right?
And then I figured there would have to be the reverse, where we had a way to take it backwards.
So we'll take a look at this second macro.
By the way, I'll put this workbook as a download down in the YouTube description.
If you actually want to try this, you're just going to come in here to Module 1.
Copy all of these and hopefully put it in your Personal Macro Workbook.
That way, it would be available all the time.
*************New Content ************ And then, the macro to reveal the Social Security Number.
Loops again through each cell in the selection.
Grabs the original formula which is going to look something like this.
If the right two characters are quote and right parentheses.
Oh, and I understand how confusing this looks.
That is the quote to say, Oh, we are starting some quoted text.
These two quotes right here are just to put in a single quote.
Any time you are inside quoted text.
You have to put two quotes to get a single quote.
And then close Paren.
So it takes those two characters off the right-hand side.
We strip off the quote and the parentheses.
And then use INSTR to look for this N, Open Parentheses, Quote.
And again, this is really confusing.
It is Quote Quote to put in a single Quote.
And that’s the end of the quoted text.
And that parentheses is the end of the INSTR function.
If the location of N is greater than zero.
Then we know that we have a social security number.
We are going to start from the MID of the original formula.
The location of N plus three.
Which is going to get us just the social security.
Write that back out to the cell.
Change the Number Format back to General.
To get rid of this number format here.
Close the IF statement.
Close the IF Statement Close the Next Cell loop.
So then here, we can select these and reveal SSN and they all come back, all right?
So that was my first solution.
It requires VBA.
That means you're on Windows or a Mac.
It certainly isn't going to work in Excel Online.
Then solution number two.
This is one where we're actually going to use the data types that were created back in 2018.
So I started out with name, the whole Social Security number, and some more data.
And we run that through Power Query.
All right, so just talking about the Aplied Steps here.
We start with the original data like this.
Power Query automatically changes the type, so they're declaring this column as text.
That's great.
And then I take this Social Security number and I duplicate it.
That's under Add Column, Duplicate Column.
So you see that we end up with the Social Security number and a copy of the Social Security number.
Now, in this data, I'm going to Split by Position to get the last four.
So we end up with everything before and then the last four.
They changed the type and I edited this to change this back to text.
I need this part to stay text.
That way, the 0123 doesn't change to 123.
And then we're going to remove a couple of columns.
The first five digits.
Rename the columns.
So this is going to be called last four.
And then reorder the columns so that way last four appears before fake SSN, all right?
Now check this out.
I'm going to select this column and this column.
Where is it?
Is it back here on the Transform tab?
I'm going to create a data type, all right?
When we create the data type, we're going to say that it's the last four that are going to be displayed.
And the whole SSN is going to be one of the fields in the Data Type.
Here's the dialog box where I would've done that.
So the data type name is Social Security.
Display column is going to be last four.
Click okay.
And we'll see that at that step, now we only see Social Security.
So then Home, Close and Load.
And check this out.
Now we end up back at data that looks almost exactly like the original data.
Here was the original data.
And all we're seeing is last four.
But if we needed to, it would be possible to click on this, and then the whole Social Security number is in there.
All right?
So this requires no VBA.
It is Power Query, so you're, again, going to be on Windows or Mac or Excel Online.
I was happy to see that these two different items that had 6789 are able to have separate fields.
So 12345 there, and 54321 there.
I don't know why I was worried that the data type wouldn't be allowed to have the same key with different stored values.
But that all worked fairly well.
Okay, so those are two solutions.
And I just get this feeling that the people who asked this are going to say this is too kludgey.
And it's not going to work.
Does anyone have a better way to display just the last four digits from a number, but to keep the rest of the digits there?
Let me know down in the YouTube comments.
The folks at the VA are doing a great service for our veterans.
And I would just love to figure out a solution here for this.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
I have two solutions to this one, but I don't like either solution.
Boy, there has to be something better. Episode 2618, display only the last four digits of the Social Security number, but keep the entire number available just in case.
This question came in and it resonates with me.
I used to take my father to the Veterans Hospital.
Where you are known as your last name and the last four of the Social Security number.
For those of you not in the United States, we all have a Social Security number here.
Three digits, two digits, four digits.
And the VA figured out that given your last name and the last four digits.
It's enough pretty much to make sure that it's you.
All right?
So the person here has a report that pulls data into Excel.
They get the whole Social Security number.
But they really only use the last four.
Unless it's a weird situation where we have to look at the entire SSN.
Maybe 1 out of 500 times.
So the question is, can we format the column to show only the last four digits of that number.
But have the full number stored there in case we need it?
All right.
Also, these Social Security numbers are all fake.
They were generated with RANDBETWEEN.
Don't go try and steal any of these people's identities.
It was just random numbers.
Although, I changed this one to have a zero as the leading digit.
Because I needed to test that in one of my solutions.
And I changed these two people to have the same last four.
Because I needed to test that in my solutions.
It would be amazing if in the custom number formats there was a way to hide digits.
We can do something like 1, 2, 3, 4.
But if there's more digits than that, they're always going to show us those digits.
It would almost have to be a special character.
Just to say if we put a capital X in.
That we understand there's a digit there and we want to obscure that digit.
Now, I don't know of any way to do this.
Of course we have a Special here.
Where we can format that number as a Social Security number and put the dashes in.
And I'm not entirely sure if the folks who are getting this data are getting it as text.
I'm expecting it to be as text.
Or as a number.
This solution assumes that it's as text.
And I even tried this crazy thing like, all right, so it's text.
What if we just ask for the last four digits of the text with "@@@@"?
But that actually puts the Social Security number in four times.
All right, so if you're a genius on custom number formatting.
And you know a solution to this.
Please, by all means, down in the YouTube comments, let me know.
But right now I'm assuming that there's no way that we can mask these characters to display only the last four digits.
So then I have two solutions, one involving Power Query and one involving VBA.
My idea is that we're going to select the column that has the Social Security numbers.
And run this macro called obscure SSN.
Let's go take a look at that code, right?
So hide SSN.
For each cell in the Selection.
We're going to save the original value.
And then we're going to grab the right four characters of that value.
And then check this out, I'm going to build a formula saying equal the new value.
So =1234.
And then an ampersand and this beautiful trick, the N function.
I bet you've never used the N function because it's not very useful.
The N function, if it's a number, will return the number.
But if it's any text, will return zero, right?
So what I'm doing is I'm putting the last four digits in.
And then adding the N function, which is going to add zero.
But in the formula bar, we'll be able to see the original Social Security number.
And then only if the length of the original cell was greater than or equal to nine.
Then put the formula in.
And in case one of the last four digits has a leading zero.
Make sure that we show all of the zeros like that.
All right, so let's take a look at this.
I'm going to select these cells and then obscure the SSN.
What we see here in each cell is the last four digits, but if we actually select one, like here, Ike.
We need to go check Ike's Social Security number.
Then we can look up in the formula bar and see that the original SSN was this, all right?
And then I figured there would have to be the reverse, where we had a way to take it backwards.
So we'll take a look at this second macro.
By the way, I'll put this workbook as a download down in the YouTube description.
If you actually want to try this, you're just going to come in here to Module 1.
Copy all of these and hopefully put it in your Personal Macro Workbook.
That way, it would be available all the time.
*************New Content ************ And then, the macro to reveal the Social Security Number.
Loops again through each cell in the selection.
Grabs the original formula which is going to look something like this.
If the right two characters are quote and right parentheses.
Oh, and I understand how confusing this looks.
That is the quote to say, Oh, we are starting some quoted text.
These two quotes right here are just to put in a single quote.
Any time you are inside quoted text.
You have to put two quotes to get a single quote.
And then close Paren.
So it takes those two characters off the right-hand side.
We strip off the quote and the parentheses.
And then use INSTR to look for this N, Open Parentheses, Quote.
And again, this is really confusing.
It is Quote Quote to put in a single Quote.
And that’s the end of the quoted text.
And that parentheses is the end of the INSTR function.
If the location of N is greater than zero.
Then we know that we have a social security number.
We are going to start from the MID of the original formula.
The location of N plus three.
Which is going to get us just the social security.
Write that back out to the cell.
Change the Number Format back to General.
To get rid of this number format here.
Close the IF statement.
Close the IF Statement Close the Next Cell loop.
So then here, we can select these and reveal SSN and they all come back, all right?
So that was my first solution.
It requires VBA.
That means you're on Windows or a Mac.
It certainly isn't going to work in Excel Online.
Then solution number two.
This is one where we're actually going to use the data types that were created back in 2018.
So I started out with name, the whole Social Security number, and some more data.
And we run that through Power Query.
All right, so just talking about the Aplied Steps here.
We start with the original data like this.
Power Query automatically changes the type, so they're declaring this column as text.
That's great.
And then I take this Social Security number and I duplicate it.
That's under Add Column, Duplicate Column.
So you see that we end up with the Social Security number and a copy of the Social Security number.
Now, in this data, I'm going to Split by Position to get the last four.
So we end up with everything before and then the last four.
They changed the type and I edited this to change this back to text.
I need this part to stay text.
That way, the 0123 doesn't change to 123.
And then we're going to remove a couple of columns.
The first five digits.
Rename the columns.
So this is going to be called last four.
And then reorder the columns so that way last four appears before fake SSN, all right?
Now check this out.
I'm going to select this column and this column.
Where is it?
Is it back here on the Transform tab?
I'm going to create a data type, all right?
When we create the data type, we're going to say that it's the last four that are going to be displayed.
And the whole SSN is going to be one of the fields in the Data Type.
Here's the dialog box where I would've done that.
So the data type name is Social Security.
Display column is going to be last four.
Click okay.
And we'll see that at that step, now we only see Social Security.
So then Home, Close and Load.
And check this out.
Now we end up back at data that looks almost exactly like the original data.
Here was the original data.
And all we're seeing is last four.
But if we needed to, it would be possible to click on this, and then the whole Social Security number is in there.
All right?
So this requires no VBA.
It is Power Query, so you're, again, going to be on Windows or Mac or Excel Online.
I was happy to see that these two different items that had 6789 are able to have separate fields.
So 12345 there, and 54321 there.
I don't know why I was worried that the data type wouldn't be allowed to have the same key with different stored values.
But that all worked fairly well.
Okay, so those are two solutions.
And I just get this feeling that the people who asked this are going to say this is too kludgey.
And it's not going to work.
Does anyone have a better way to display just the last four digits from a number, but to keep the rest of the digits there?
Let me know down in the YouTube comments.
The folks at the VA are doing a great service for our veterans.
And I would just love to figure out a solution here for this.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
