Hi Casper,
This is similar to your method but uses VBA instead. It is set up so
that when a number is entered into column B its full value is placed
into the same row of column A, which should be hidden, and only its
last four characters are shown in the column B cell into which it was
originally entered. Since the code uses a loop it can deal with the
pasting of multiple SSNs into column B. If you are not interested in
having the hidden column A with the complete SSNs then delete...
Cells(rngCell.Row, Columns("B").Column - 1) _
..Value = rngCell.Value
from the code.
The code will need to be edited depending on the column into which you
are entering the SSNs, which is also the column that will show only the
last four characters, eg if that column is G then change the two "B"s
in the code to "G". Also, that column should be formatted "Text",
otherwise when the fourth last digit is a 0 it won't show in the last
four digits.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = Columns("B").Column Then
If Target.Columns.Count = 1 Then
Application.EnableEvents = False
On Error Resume Next
Dim rngCell As Range
For Each rngCell In Target
Cells(rngCell.Row, Columns("B").Column - 1) _
.Value = rngCell.Value
rngCell.Value = Right(rngCell.Value, 4)
Next rngCell
End If
End If
Application.EnableEvents = True
End Sub
To get the code in place...
1. Copy it
2. Right click the worksheet's sheet tab, then select "View Code"
from the popup menu. This takes you to the VBA Editor.
3. Paste the code into the code module that appears.
4. Either press Alt + F11 or go File|"Close and Return to Microsoft
Excel" to get back to Excel's normal interface.
5. The code will only run if the Security level is no higher than
Medium and "Enable Macros" is selected when the "Security
Warning" dialog appears on opening. (Tools|Macro|Security...select
Medium|OK|Close|Open|Select "Enable Macros" on the "Security
Warning" dialog)
Ken Johnson
