Last 4 digits of SSN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to have the user input their whole social security number and
have Excel only display the last 4 digits of their number? Can I do this as
a cell format or will this have to be done with a script. I tried the data
validation with text length equal to 4, but if the last 4 digits start with a
zero (0123), it gives an error because it doesn't recognize the zero as a
digit. I'm guessing there has to be a script attached to that cell that will
grab the last 4 digits. Any ideas would be great. Thanks in advance.
 
Lets use A1 as the cell with the SSN, and B1 for the last four digits. I am
assuming that you are not doing any type of calculation with this, but just
need the last four.

Format the SSN cell(s) (A1) as SPECIAL > SSN.
In B1, type =RIGHT(A1,4)

I did not format B1. It defaults to GENERAL and I tried it with a zero as
the first digit of the last four numbers and it worked.

Hope this works,
Les
 
Thanks for your suggestion. I'm sorry, but I did not explain the situation
properly. I don't want to grab the last 4 digits from another cell, but have
the user put their social security number into cell A1 and when they press
Enter, it only displays the last 4 digits that they entered. I hope this is
a better explanation. Thanks.
 
If you want to keep the whole SSN number, but only display the last 4 digits for
security purposes, then don't do it.

Excel's security isn't made to protect things like this.

But you could have a macro that actually only keeps those final 4 digits--but
why bother--just have the user type in the last 4 digits.
 
don't know exactly what you want to do, only have part of the entry visible?

maybe store the ssn in a hidden worksheet and use the formula to show the last 4
in A1.

don't know what you're going to do with the data after it's entered, though.
 
Thanks Dave. I don't want to keep the whole number but display the last 4.
I just want to keep the last 4 digits.

Could you maybe give me an example of that macro, because as you stated,
just have them enter the last 4 digits, management doesn't trust the people
to only input the last 4 digits. They want me to make absolutely sure that
the last 4 digits will be displayed no matter what the user puts in the cell.
I completely agree with your statement, but unfortunately don't have the
final say. Thanks again.
 
This works for me
Phillip UK London

Put this code in the sheet module
Right click sheet tab and select view code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Thiscell As Range
Static Lastcell As Range

If Target.Cells.Count > 1 Then Exit Sub
Set Thiscell = Target
Set Lastcell = Thiscell
If Lastcell Is Nothing Then Exit Sub
Lastcell.Value = VBA.Right(Lastcell.Value, 4)

End Sub
 
Matt,

You can use then the _Change event. Put this code in the sheet module where
the user will input the data:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Target.Value = Right$(Target.Value, 4)
Application.EnableEvents = True
End If
End Sub


and change the $A$1 reference to the cell that they will enter the data in.

Regards,

Juan Pablo González
 
Phillip, thanks. That looks like exactly what I need. One question though?
Is it possible to make it only apply to a change in one particular cell (A1)
instead of any cell on the spreadsheet? Thanks alot.
 
Is it possible to make it only apply to a change in one particular cell (A1)
instead of any cell on the spreadsheet? Thanks alot.
Click to expand...

If Target.Address = "$A$1" Then
'do something
End If

Hth,
Merjet
 
Look at Juan Pablo's suggestion.
Phillip, thanks. That looks like exactly what I need. One question though?
Is it possible to make it only apply to a change in one particular cell (A1)
instead of any cell on the spreadsheet? Thanks alot.
Click to expand...
 
Back
Top