• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Converting 9 number social security to last 4.

Frncis

Member
I have been tasked by my employer to convert all Social Security entries to the last 4 only. At present there is a small number, but as time goes on it will be very time consuming to do this manually. I have used the custom formatting to convert the first 4 of the Social Security to X. I.e. XX-XX-1234. Unfortunately the first 5 numbers are still visible if you click on the cell.
I am trying to have the system convert all entries to only the last 4, and if the cell is clicked on all the user sees is Xs and the last 4, or just the last 4. I am also trying to not require the user to delete first 5 numbers.
Is this possible?
 

GraH - Guido

Well-Known Member
Hi, what is the point? If you are entering all the digits in the cell, even "masked" with a format they remain in clear in the formula bar.
I believe you'd need a macro for this, that changes the content of the cell by a string with X's only keeping 4 last digits.
 

Chihiro

Excel Ninja
Or just use formula and the copy paste over.

But in general, it's poor practice to keep sensitive info such as SS number, or CC number in Excel tracking sheet. This sort of data compliance should be done on proper database. Using Key rotation & management. So that it does not exist in unencrypted form, where it can easily be transferred via some medium.

Using Excel to store these kind of info will break several rules around Personally identifiable information in many jurisdictions (ex. GDPR).
 

charlesdh

Member
I agree with Chihiro statement. But, if your determined to use a code try this.
It's based on the data is in column "A". You will need to change the column if in different column. Be sure you test on a copy before you use it with your actual data. Make a back up copy to make sure you do not lose the data.

Code:
Sub Replace_SSN()
Dim Lrow As Long
Dim SSN As String
Dim Cell As Range

LastRow = Cells(Rows.Count, 1).End(xlUp).Row'' if data is not in column "A"
''' Change "1" to the column number that your data is in..
For Each Cell In Range("A2:A" & LastRow) ''' Change this to the column that you use.
    SSN = Cell.Value
    Cell.Value = "XXX-XX" & Right(SSN, 5)
Next Cell
End Sub
Charles
 

Frncis

Member
I totally agree with every thing that has been said. I was just following direction. I did find a surprising simple solution that is a 2 step process.
  1. Formatted the cell range as a custom format. XXX-XX-
  2. Under data validation this formula =AND(ISNUMBER(--D3),LEN(D3)<=6,ISERROR(FIND("0",D3)),ISERROR(FIND("7",D3)),ISERROR(FIND("8",D3)),ISERROR(FIND("9",D3)))
You are allowed to only enter the last 4 of the Social Security. Is the input message.
The error Alert style used was STOP. The user received the following message. Enter only the last 4!!
When the cell is viewed the only the last 4 is visible.

I want to thank you for your assistance, expression of concerns, & statement of best practices.
 
Last edited:

Peter Bartholomew

Well-Known Member
Frncis

I am impressed! Did you find the formula or did you write it yourself?
Here are a few changes that you might wish to consider.

=AND(ISNUMBER(D3),LEN(D3)=4,ISERROR(FIND({0,7,8,9},D3)))

The numeric test has not been coerced to [0,1] because it is a parameter of AND.
The string length is required to be 4, rather than less than or equal to 6
The prohibited digits are left as digits rather than characters, i.e. no "".
Rather than each digit appearing within its own function I have used the array constant {0,7,8,9}.

There is a catch. Validation formula will not accept Range intersection operations or array constants. The solution is easy. Use name manager to define a named formula 'Valid?' that refers to above formula.


The custom validation formula is then

= Valid?
It may puzzle a future developer but at least it 'does what it says on the tin'.
 

Frncis

Member
Frncis

I am impressed! Did you find the formula or did you write it yourself?
Here are a few changes that you might wish to consider.

=AND(ISNUMBER(D3),LEN(D3)=4,ISERROR(FIND({0,7,8,9},D3)))

The numeric test has not been coerced to [0,1] because it is a parameter of AND.
The string length is required to be 4, rather than less than or equal to 6
The prohibited digits are left as digits rather than characters, i.e. no "".
Rather than each digit appearing within its own function I have used the array constant {0,7,8,9}.

There is a catch. Validation formula will not accept Range intersection operations or array constants. The solution is easy. Use name manager to define a named formula 'Valid?' that refers to above formula.

The custom validation formula is then
= Valid?
It may puzzle a future developer but at least it 'does what it says on the tin'.
A little of both.
 

Frncis

Member
I found that the the formula had some limitations, so I reworked the process, & removed the formula. I modified code that I found, & now there is another problem. The code deletes the first 5 numbers & leaves the last 4. When a previously filled cell is accidentally clicked, the data changes.
I created a pop up message to alert the user, to not put the curser in a filled box. This not an ideal solution, but a OK temporary one.
here is the code that is coded on the worksheet:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Thiscell As Range
Static Lastcell As Range

If Target.Cells.Count > 4 Then Exit Sub
Set Thiscell = Target
Set Lastcell = Thiscell
If Lastcell Is Nothing Then Exit Sub
Lastcell.Value = VBA.Right(Lastcell.Value, 4)
Any idea why this is happening, or a solution?
 

Peter Bartholomew

Well-Known Member
What are you intending the line
Code:
If Target.Cells.Count > 4 Then Exit Sub
should achieve? The code seems to test the selection range and checks that it comprises of no more than 4 cells. Were you intending to check the length of the string contained in a single cell?
 

Frncis

Member
What are you intending the line
Code:
If Target.Cells.Count > 4 Then Exit Sub
should achieve? The code seems to test the selection range and checks that it comprises of no more than 4 cells. Were you intending to check the length of the string contained in a single cell?
Yes. Currently it does delete the first 5, & that is what I am trying to accomplish.
 

Peter Bartholomew

Well-Known Member
I would deal with the issue at the point at which the SSN is typed, copied or written from a program into the worksheet.

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SSNcell As Range
    'Test whether content should be an abbreviated SSN
    'This restricts the area of application of the event handler
    If Not Intersect(Target, Range("SSN")) Is Nothing Then
        'Make sure the program does not trigger a further event
        Application.EnableEvents = False
        'Loop over intersection
        For Each SSNcell In Intersect(Target, Range("SSN"))
            SSNcell.Value = VBA.Right(SSNcell.Value, 4)
        Next
        'Reset
        Application.EnableEvents = True
    End If
End Sub
The US Special number format will insert the apparent zeros and hyphens.
 

Frncis

Member
I would deal with the issue at the point at which the SSN is typed, copied or written from a program into the worksheet.

Code:
Option Explicit[/COLOR][/FONT][/LEFT][/COLOR][/FONT][/LEFT]
[FONT=Verdana][COLOR=rgb(20, 20, 20)]
[LEFT][FONT=Verdana][COLOR=rgb(20, 20, 20)]
[LEFT]Private Sub Worksheet_Change(ByVal Target As Range)
Dim SSNcell As Range
    'Test whether content should be an abbreviated SSN
    'This restricts the area of application of the event handler
    If Not Intersect(Target, Range("SSN")) Is Nothing Then
        'Make sure the program does not trigger a further event
        Application.EnableEvents = False
        'Loop over intersection
        For Each SSNcell In Intersect(Target, Range("SSN"))
            SSNcell.Value = VBA.Right(SSNcell.Value, 4)
        Next
        'Reset
        Application.EnableEvents = True
    End If
End Sub


The US Special number format will insert the apparent zeros and hyphens.
I know that (Target, Range("SSN")) is replaced with (Target, Range("D3:D329")) along with Intersect(Target, Range("SSN")). What I don't understand is Dim SSNcell As Range & (SSNcell.Value, 4), replaced with the same range format, ("D3:D329")) or another format?
 

Peter Bartholomew

Well-Known Member
"SSN" is a defined name I have given to the list of Social Security Numbers. The advantage of using a name is that I need neither know nor care where the list is on the sheet, you can change its location as much as you like and the code still finds the data. The intersection then narrows the processing down to looking at those SSNs that have just been changed.

The Dim statement simply declares that the variable 'SSNcell' is a Range; in fact I use it to represent each changed cell in turn. A cell has many properties (color, protection, row & column numbers, etc, etc. … ). The main property I am interested in, though, is its value (the 4 or 9 digit SSN). I cut the content down to the last 4 digits and write it back to the cell.

Usually, only one cell will have been changed so the routine will exit. If, however, many values have been pasted in to the SSN range, the loop will run over each cell in turn, reducing it to 4 digits.

I hope this helps. We are somewhat migrating into VBA and macros.
 

Frncis

Member
"SSN" is a defined name I have given to the list of Social Security Numbers. The advantage of using a name is that I need neither know nor care where the list is on the sheet, you can change its location as much as you like and the code still finds the data. The intersection then narrows the processing down to looking at those SSNs that have just been changed.

The Dim statement simply declares that the variable 'SSNcell' is a Range; in fact I use it to represent each changed cell in turn. A cell has many properties (color, protection, row & column numbers, etc, etc. … ). The main property I am interested in, though, is its value (the 4 or 9 digit SSN). I cut the content down to the last 4 digits and write it back to the cell.

Usually, only one cell will have been changed so the routine will exit. If, however, many values have been pasted in to the SSN range, the loop will run over each cell in turn, reducing it to 4 digits.

I hope this helps. We are somewhat migrating into VBA and macros.
I am starting to understand what you are saying. I posted the code you so kindly provided on the worksheet, & received the following error message: Run-time error '1004': Method 'Range of object'_worksheet' failed. The debug shows this line: If Not Intersect(Target, Range("SSN")) Is Nothing Then
 

Peter Bartholomew

Well-Known Member
The most likely cause is that the Named Range "SSN" does not exist.
Have you selected the range containing the social security numbers and typed "SSN" into the name box (to the left of the formula box) and then pressed the Enter key?

You could also go into Name Manager on the Formulas Ribbon tab to check that the Name is present and correct.

The other thing to be careful of when using a worksheet event is that the code is in the appropriate Sheet module under 'Microsoft Excel Objects' and not in a general module. If you hadn't done that correctly you wouldn't have got the event handler to run, though.
 

Frncis

Member
You were correct about the name manager, that was the problem. Once Once I added SSN to the Name manager , it worked.
The one think I noticed was that if the last 4, had a leading "0" or multiples of "0", "0" was visible, but when you clicked on the cell it "0" was dropped any suggestions?
 

Peter Bartholomew

Well-Known Member
It is an intrinsic property of a number that leading zeros are not significant. Formatting can change the presentation of the number in the cell to show a leading '0' or even '000-00-' but underneath the hood it remains a 1-4 digit number.

If you really want to see the number in the formula bar then you could use an apostrophe to prevent the 4-character text output by VBA from being converted to a number. If you wish to see placeholders of the form "000-00-" or "XXX-XX-" then insert the one you like instead of the apostrophe.

Code:
SSNcell.Value = "'" & VBA.Right(SSNcell.Value, 4)
 

Frncis

Member
It is an intrinsic property of a number that leading zeros are not significant. Formatting can change the presentation of the number in the cell to show a leading '0' or even '000-00-' but underneath the hood it remains a 1-4 digit number.

If you really want to see the number in the formula bar then you could use an apostrophe to prevent the 4-character text output by VBA from being converted to a number. If you wish to see placeholders of the form "000-00-" or "XXX-XX-" then insert the one you like instead of the apostrophe.

Code:
SSNcell.Value = "'" & VBA.Right(SSNcell.Value, 4)
Thanks for the advise. I think, the current format will work fine. Again thanks for all your assistance.
 
Top