Hide characters in SSN#

[gmeeks]

Can anyone tell me how to enter a full SSN# but only display the last four digits. In other words enter 111-11-1111 but after you enter the number, it displays as xxx-xx-1111?

 

[Ekim]

Put the following in the relevant sheet module (e.g. if your input is in Sheet 1, then put the macro in that sheet module).

The macro is set for range A1:A20. Amend to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target(1), Range("A1:A20")) Is Nothing Then _
Target(1).Value = Right(Target, 4)
End Sub

HTH

Mike

 

[BuddieB]

I would suggest hiding the original column with the full SS#'s and in the adjacent column put the following formula:

="xxx-xx-"&right(C1,4)

assuming the hidden column is C and data begins in row 1

 

[Ekim]

Buddie is absolutely right. I misinterpreted the question – the display is to show “xxx-xx-1111”. In addition, it makes sense putting the original SSN in one column and formatting it in another.

Amended macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target(1), Range("A1:A20")) Is Nothing Then _
    Target(1).Offset(0, 1).Value = "xxx-xx-" & Right(Target, 4)
End Sub

Regards,

Mike

 

[Yogi Anand]

Or, in line with BuddieB's idea, if the full SS# is entered in C1, then in an adjacent cell enter ...

=--(RIGHT(C1,4))

Custom Number Format it as "xxx-xx-"0000

and I would hide the entry in column C and protect the worksheet, and/or hide column C all together.

 

[gmeeks]

I will do that. Thank you for everyone's help.