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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ron Rosenfeld (ronrosenfeld_at_nospam.org)
Date: 08/20/04 

Date: Fri, 20 Aug 2004 14:33:53 -0400

On 20 Aug 2004 10:46:56 -0700, vivinewell@yahoo.com (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.

<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

Relevant Pages

  • Re: OpenForm vs. Form menu double click.
    ... was a string and the data type of the where variable matched the syntax... ... In my situatiation the SSN on the input is text as well as the SSN field in ... A command button to add an assignment ... Private Sub Form_Open ...
    (microsoft.public.access.formscoding)
  • VB to search Excell Sheets
    ... The employees will enter the last 4 numbers of the SSN (Readout) ... Each sheet in the book is named as the last 4 numbers of the SSN. ... Dim m_XLWorkbook As Excel.Workbook ... Dim FileNamePath As String ...
    (microsoft.public.vb.general.discussion)
  • Re: Indexing by multiple keys
    ... an SSN as a String than an int. ... I myself would model an SSN as a string. ... If OTOH the SSN/SIN was being typed in by a clerk I'd consider validation of some sort. ...
    (comp.lang.java.programmer)
  • Re: Indexing by multiple keys
    ... SSN as a String than an int. ... I myself would model an SSN as a string. ... If OTOH the SSN/SIN was being typed in by a clerk I'd consider validation of some sort. ...
    (comp.lang.java.programmer)
  • Re: Indexing by multiple keys
    ... Robert Klemme wrote: ... model an SSN as a String than an int. ... I myself would model an SSN as a string. ...
    (comp.lang.java.programmer)