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

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.

On 20 Aug 2004 10:46:56 -0700, (Viv) wrote:

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.

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.

altF11 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(S UBSTITUTE(A1, "-",
""), "SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""), FIND("$$$",
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(A1, "-", ""),
"SSN", "$$$"), "SS#", "$$$"), "SS ", "$$$"), " ", ""))+3, 9)

In article ,
(Viv) wrote:

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.

Hi!

Take your pick: one formula, one UDF:

http://tinyurl.com/6mjoh

Biff

-----Original Message-----
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.
.