Finding Text String Within A String With A Formula

[Sandi]

I need to find a text string within a string. Because I am not
familiar with programming, I prefer to do this with a formula. This
is what I need to do - extract text that meets this criteria:

SSN-XXXXXXXXX
SSN-XXX-XX-XXXX
SSN - XXXXXXXXX
SSN - XXX-XX-XXXX

EXAMPLES OF STRINGS TO BE SEARCHED:

ROBERT PALMER 123 ELM ST WINNBORO 555-1212 SSN-123456789 PR 34290
#<

14932 ACACIA GARDEN HEIGHTS 979/830-9086 COST ASSOC SSN -
123-45-6789


Thanks!


Sandi

 

[Tom Ogilvy]

=MID(SUBSTITUTE(SUBSTITUTE(B8,"
",""),"-",""),FIND("SSN",SUBSTITUTE(SUBSTITUTE(B8," ",""),"-",""))+3,9)

 

[Earl Kiosterud]

Sandi,

Here's a user-defined function that will do it. Paste it into a general
module from here. Watch for line breaks

Function SSN(Indata As Variant) As String
Dim i
Dim Character As String
Dim CharCount As Integer
i = InStr(1, UCase(Indata), "SSN") ' look for "SSN"
If i = 0 Then ' didn't find "SSN"
Exit Function
End If
i = i + 4 ' move past "SSN"
Do
Character = Mid(Indata, i, 1)
If IsNumeric(Character) Then ' is it a number?
SSN = SSN & Character ' add it
CharCount = CharCount + 1
Else ' did we find other than - or space?
If Not Character = " " And Not Character = "-" Then
SSN = "Invalid SSN"
Exit Function
End If
End If
i = i + 1
If i = Len(Indata) Then ' don't run off the end of Indata
SSN = "Invalid SSN"
Exit Function
End If
Loop Until CharCount = 9
' add the dashes
SSN = Left(SSN, 3) & "-" & Mid(SSN, 4, 2) & "-" & Right(SSN, 4)
End Function

Now in your cell:

=SSN(A2)

You should see the social security number, in the format XXX-XX-XXXX.

 

[Sandi]

Thanks for answering my posting. I think your solution of the
user-defined function may work for me - but I am having some trouble
with the module part. This is the first time I have tried this - and
this is what I did - opened my spreadsheet. Then selected Tools,
Macro, Visual Basic Editor. Then selected Insert, Module. I copied
your function (beginning with "Function SSN(Indata As Variant) As
String" through "End Function"). Then I pressed F5, typed a macro
name and clicked the Create button - and nothing happened. What am I
doing wrong?

 

[Sandi]

Thanks for answering my posting. I think your solution of the
user-defined function may work for me - but I am having some trouble
with the module part. This is the first time I have tried this - and
this is what I did - opened my spreadsheet. Then selected Tools,
Macro, Visual Basic Editor. Then selected Insert, Module. I copied
your function (beginning with "Function SSN(Indata As Variant) As
String" through "End Function"). Then I pressed F5, typed a macro
name and clicked the Create button - and nothing happened. What am I
doing wrong?

 

[Earl Kiosterud]

Sandi,

You're close. Since this is written as a function, you don't call it with
an event (like clicking a button). For that it would be a Sub instead of a
Function. You call it from a cell, and it runs whenever it's argument
changes. In a cell, put:
=SSN(A2)
A2 is the cell containing the record that has the SSN in one of several
formats, as you mentioned in your original post. Change the A2 reference as
necessary to point to your cell. Now any time you change cell A2, the
function will run, and will politely return the SSN. Or will
not-so-politely give you a hideous error message if there's something wrong!