Last 4 digits of SSN

[Matt]

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.

 

[WLMPilot]

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

 

[Matt]

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.

 

[Dave Peterson]

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.

 

[Gary Keramidas]

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.

 

[Matt]

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.

 

[Phillip]

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

 

[Juan Pablo González]

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

 

[Matt]

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.

 

[merjet]

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.

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

Hth,
Merjet

 

[Dave Peterson]

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.