Replace first part of SS# with x's (XXXX-XX-9999)

J

Jenpher

I am trying to write a code that will take the full social securit
number 9999-99-9999 and turn it into the following: XXXX-XX-9999, th
first numbers can be deleted, with only the last four remaining. I kno
this is really simple for you pros out there, but I cannot do it!
Please help me!
 
J

Jake Marx

Hi Jenpher >,
I am trying to write a code that will take the full social security
number 9999-99-9999 and turn it into the following: XXXX-XX-9999, the
first numbers can be deleted, with only the last four remaining. I
know this is really simple for you pros out there, but I cannot do
it!! Please help me!!
Click to expand...

I'm assuming you mean xxx-xx-9999, as the standard SSN has 3 digits, a
hyphen, 2 digits, a hypen, then 4 digits.

Here's a function that will do that for you:

Public Function gsMaskSSN(rsOrigSSN As String) As String
If rsOrigSSN Like "###-##-####" Then
gsMaskSSN = "xxx-xx-" & Right$(rsOrigSSN, 4)
Else
Err.Raise 10001, "gsMaskSSN", "Invalid SSN"
End If
End Function

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Jenpher

I've looked a lot of places but I don't know where to paste it and the
apply the format afterwards. Any help is very, very appreciated. Than
you!
 
J

Jake Marx

Hi Jenpher >,
I've looked a lot of places but I don't know where to paste it and
then apply the format afterwards. Any help is very, very appreciated.
Thank you!!
Click to expand...

Most code goes in a Standard Module. To create one, open the VBE (Alt+F11
from Excel), then select Insert | Module. You can paste the code in there.

The code I gave you was set up as a function. This means that you pass in
one or more values, and the function returns a value to you. The code used
to pass in the SSN will vary depending on what it is you're trying to
accomplish. Where are the SSNs stored, and how do you want them outputted?
Or do you just want to take all SSNs in a worksheet and mask them using this
function? Do you need to retain the original values somewhere?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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

Gord Dibben

Jenpher

If "the first numbers can be deleted"......

In an adjacent column enter

=RIGHT(A1,4) and drag/copy down.

Assumes Column A has your SSN's

When done, you can copy the column and paste special>values>OK>Esc

Delete the original column.

Gord Dibben Excel MVP
 
Top