1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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.

Discussion in 'Ask an Excel Question' started by Frncis, Nov 26, 2018.

  1. Frncis

    Frncis Member

    Messages:
    65
    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?
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    905
    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.
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    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).
  4. charlesdh

    charlesdh Member

    Messages:
    68
    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 (vb):

    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
  5. Frncis

    Frncis Member

    Messages:
    65
    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: Nov 28, 2018
    charlesdh likes this.
  6. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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'.
    Thomas Kuriakose likes this.
  7. Frncis

    Frncis Member

    Messages:
    65
    A little of both.
  8. Frncis

    Frncis Member

    Messages:
    65
    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 (vb):
    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?
  9. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    What are you intending the line
    Code (vb):
    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?
  10. Frncis

    Frncis Member

    Messages:
    65
    Yes. Currently it does delete the first 5, & that is what I am trying to accomplish.
  11. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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 (vb):
    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 and charlesdh like this.
  12. Frncis

    Frncis Member

    Messages:
    65
    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?
  13. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    "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.
  14. Frncis

    Frncis Member

    Messages:
    65
    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
  15. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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.
  16. Frncis

    Frncis Member

    Messages:
    65
    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?
  17. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    584
    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 (vb):
    SSNcell.Value = "'" & VBA.Right(SSNcell.Value, 4)
  18. Frncis

    Frncis Member

    Messages:
    65
    Thanks for the advise. I think, the current format will work fine. Again thanks for all your assistance.

Share This Page