Extract SSN's from a free form text field...

  • Thread starter Thread starter Viv
  • Start date Start date
V

Viv

I am trying to extract SSN's from a free form text field where the SSN
may appear in any portion of the field (beginning, end or middle of
the field).

The SSN will always be preceded by "SS", "SSN", or "SS#".
The SSN may be listed with or without dashes or spaces.
The SSN may be preceded by a dash or space or neither after "SS",
"SSN", or "SS#".


Examples:
Example #1 - SSN-123456789;
Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 SHADY COVE
Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC WRKING IN CA

These are a few of the ways the SSN could appear in the field.

If the solution requires programming, please provide exact directions
and expectations. I am not very good with programming. I prefer a
formula if possible.
 
I am trying to extract SSN's from a free form text field where the SSN
may appear in any portion of the field (beginning, end or middle of
the field).

The SSN will always be preceded by "SS", "SSN", or "SS#".
The SSN may be listed with or without dashes or spaces.
The SSN may be preceded by a dash or space or neither after "SS",
"SSN", or "SS#".


Examples:
Example #1 - SSN-123456789;
Example #2 - CE UNEMPLOYED SS# 987-65-4321 CBR 444-4444 SHADY COVE
Example #3 - SE RETIRED SS 876543219 SE RETIRED OTHER SVC WRKING IN CA

These are a few of the ways the SSN could appear in the field.

If the solution requires programming, please provide exact directions
and expectations. I am not very good with programming. I prefer a
formula if possible.
Click to expand...

It's easiest with VBA.

I took the approach that, if we remove the <space>'s and hyphens from your
string, a SSN could be uniquely identified by being the first consecutive
string of nine digits after an "SS". If that is not the case, post back with
some more data.

<alt><F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window, then, from
the main menu bar on top: Insert/Module and paste in the code below.

To use the function, in some cell enter the formula =SSN(cell_ref) where
cell_ref is the address of a string from which you want the SSN extracted.

e.g. =SSN(A1)

The answer should appear as a string of nine digits (or maybe fewer if there
are leading zero's.

Format the result as ssn. Format/Cells/Number/Special/Social Security Number

====================================
Option Explicit

Function SSN(rg As Range) As Long
Dim i As Long
Dim str As String
Dim temp As String

If rg.Count <> 1 Then Exit Function

str = Replace(rg.Text, "-", "")
str = Replace(str, " ", "")
str = Right(str, Len(str) - InStr(1, str, "SS"))

For i = 1 To Len(str) - 9
temp = Mid(str, i, 9)
If IsNumeric(temp) Then
SSN = temp
Exit Function
End If
Next i
End Function

=================================


--ron
 
one way:

this works with the examples you gave. (Note that I used "SS ", i.e,
with a space after the SS - in one of my substitutions. If that's not
always to be the case, delete the space, but make sure you have no words
prior to the SSN that have "SS" in them):

=MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-",
""), "SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""), FIND("$$$",
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""),
"SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""))+3, 9)
 
Back
Top