Working with a social security number (let's say: 1234-56-
7890), I need to preserve the whole number in the cell,
but display only the last 4 digits (which would look like:
XXXX-XX-7890).

I know how to do this using concatenate function, but need
to know if it is possible to do using the formatting
programming. I tried:_("XXX-XX-"0_) and _("XXX-XX-"#_),
but i still get the whole number (all 10 digits instead of
the last 4) after the X's.

Thanks a lot!
Svetlana

You can't do it with formatting.

--
Regards,
Tom Ogilvy

"svetlana" wrote in message
...
Working with a social security number (let's say: 1234-56-
7890), I need to preserve the whole number in the cell,
but display only the last 4 digits (which would look like:
XXXX-XX-7890).

I know how to do this using concatenate function, but need
to know if it is possible to do using the formatting
programming. I tried:_("XXX-XX-"0_) and _("XXX-XX-"#_),
but i still get the whole number (all 10 digits instead of
the last 4) after the X's.

Thanks a lot!
Svetlana

Hi Svetlana,

There's no good way to do this, as Tom pointed out. But you could "hide"
the SSNs from most users by storing the SSNs in hidden names. The only way
a user could get to them is via VBA code. Here's an example:

Private mbDisableEvents As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim regExp As regExp

If Not mbDisableEvents Then
For Each rng In Target
If rng.Column = 2 Then
'/ store SSN and hide all except last 4 digits
'/ -- quick check of format first
Set regExp = New regExp
regExp.Pattern = "^\d{3}-\d{2}-\d{4}$"
If regExp.Test(rng.Value) Then
'/ check OK, store it
ThisWorkbook.Names.Add Name:="SSN_" & _
rng.Parent.Name & "_" & CStr(rng.Row) & _
"_" & CStr(rng.Column), _
RefersTo:="=" & rng.Value, Visible:=False
'/ reformat cell
mbDisableEvents = True
rng.Value = "xxx-xx-" & Right$(rng.Value, 4)
mbDisableEvents = False
End If
Set regExp = Nothing
End If
Next rng
End If
End Sub

This example assumes your SSNs are going to be entered into column B. If
not, you should change the If rng.Column=2 line to the appropriate column
number. This code would go "behind" the worksheet that the SSNs would be
entered into. To get there, just right-click the sheet tab and select View
Code. In order to check the format of the SSN before processing it, the
code uses the RegExp object, which requires a reference to the Microsoft
VBScript Regular Expressions x.x Library (pick the latest version available
to you via Tools | References in the VBE). If you don't care about checking
the format first, you can get rid of that code and the reference.

Now, whenever a user enters a validly-formatted SSN into the worksheet in
the appropriate column, it will be stored as a hidden name, and the cell
will display only the last 4 digits.

To get the SSN back, you need to use code:

Private Function msGetSSN(rrng As Range) As String
On Error Resume Next

If rrng.Cells.Count = 1 Then
msGetSSN = Mid$(ThisWorkbook.Names("SSN_" & _
rrng.Parent.Name & "_" & CStr(rrng.Row) & _
"_" & rrng.Column).RefersTo, 2)
End If
End Function

This code should go into a standard module, and you just pass in the range
for which you wish to get the SSN. If no SSN is associated with that range,
the function returns an empty string.

MsgBox msGetSSN(Sheets("Sheet1").Range("B2"))

I made the function Private so it can't be called externally, but you may
need to change it to Public depending on from where you're calling it. If
you want to use it as a worksheet function (UDF), you would have to make it
Public.

Make sure you lock down your VBA project with a password so nobody can view
the code (Tools | <projectname Properties from VBE).

Like I said, this isn't foolproof, as a savvy user could get the hidden
names just like the msGetSSN function does. But at least it will protect
the data from casual users.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


svetlana wrote:
Working with a social security number (let's say: 1234-56-
7890), I need to preserve the whole number in the cell,
but display only the last 4 digits (which would look like:
XXXX-XX-7890).

I know how to do this using concatenate function, but need
to know if it is possible to do using the formatting
programming. I tried:_("XXX-XX-"0_) and _("XXX-XX-"#_),
but i still get the whole number (all 10 digits instead of
the last 4) after the X's.

Thanks a lot!
Svetlana

Hi Svetlana,

This custom format may help:

In Format Cell|s|Number|Custom, type:
1. Six 0s (ie 000000), followed by
2. Alt-010, followed by
3. XXXX-XX-, followed by
4. Four 0s (ie 0000)

Now format the cell to wrap text with the vertical
alignment set to 'Bottom'

Finally, increase the row height a small amount so as to
force the cell to display only the 'last' line - on my
system, increasing from 13.2 to 14 was enough to acheive
this. You may need to experiment a bit, but you should end
up with the required XXXX-XX-7890 displayed. Note that
you'll still be able to see the original, unformatted
number (1234567890) in the formula bar.

Cheers


-----Original Message-----
Working with a social security number (let's say: 1234-56-
7890), I need to preserve the whole number in the cell,
but display only the last 4 digits (which would look
like:
XXXX-XX-7890).

I know how to do this using concatenate function, but
need
to know if it is possible to do using the formatting
programming. I tried:_("XXX-XX-"0_) and _("XXX-XX-"#_),
but i still get the whole number (all 10 digits instead
of
the last 4) after the X's.

Thanks a lot!
Svetlana

.