Thread: Searching for a String with Pattern

I posted this thread in the Excel area, then I realized that I can do this much easier in Access.

I need help creating a VB function.

I have a column with text (not exceeding 255 chars). Within the text, there is a specific 8 character string I'm interested in pulling (2 letters, 5 numbers, 1 letter) <= this is always the pattern (i.e. AB12345C).

The function needs to search through the text and when it finds the first instance return the string. If the function does not find any string, then it should return nothing.

Any help is greatly appreciated

This is a sample of the data I have in the column of my table

Notes
12342 John Doe AB12345C EN ME
897342 Jane Doe DE54321F EN ME
8923 Peter Doe GH23456I EN ME
40293074 Jamie Doe JK34567L EN ME
2093 Frank Doe MN45678O EN ME 298730 Alex Doe PQ98765R EN ME

This is the VB function I want to modify to include the specific pattern [A-Z]{2}[0-9]{5}[A-Z]{1}

I know I have to change the following argument to accomodate my string, but I don't know how:

IsNumeric(varString(intCounter))

Public Function fExtractECodes(strString As String)

Dim varString As Variant
Dim intCounter As Integer
Dim strBuild As String
Dim intNumOfMatches As Integer

    varString = Split(strString, " ")

For intCounter = LBound(varString) To UBound(varString)

    If Len(varString(intCounter)) = 8 And IsNumeric(varString(intCounter)) Then
    intNumOfMatches = intNumOfMatches + 1
    strBuild = strBuild & " " & varString(intCounter)

End If

Next

    If intNumOfMatches = 1 Then
    fExtractECodes = Trim(strBuild)

Else

    fExtractECodes = Null

End If

End Function

If you are looking to do this in Access, then why not just create a query and in the criteria put Like *AB12345C* and then Access will bring back all records having this string in the field you identify. You don't need any VBA to do that. If you need further machinations on the records identified, you can export back to Excel and slice and dice to your hearts content.

Alan

This is easily done in Excel with a UDF, if you decide to use it instead of Access

Option Explicit

Function FindString(rng As Range, Optional Delimiter As String = " ")
    Dim ctr As Integer
    Dim arrSplit As Variant

    FindString = ""
    arrSplit = Split(WorksheetFunction.Trim(rng), Delimiter)
    For ctr = LBound(arrSplit) To UBound(arrSplit)
        If Len(arrSplit(ctr)) = 8 Then
            If IsNumeric(Mid(arrSplit(ctr), 3, 5)) And Not IsNumeric(arrSplit(ctr)) Then
                FindString = arrSplit(ctr)
                Exit For
            End If
        End If
    Next
End Function

Enter in Excel

=FindString(A2)

to use " " as your delimiter (default)

=FindString(A2,";")

as an example ";" (semi-colon)

Drag/Fill down as required

Hope this helps

Thx for the reply alansidman. However, the list I have to work with have thousands of records with different variations, but the pattern is always the same 2 alpha; 5 numbers; 1 alpha.

Marcol, you just gave me some ideas... and thx also for your reply.

I'll post back if I'm able to make this VB code work.

FWIW, see also the Excel thread and UDF: http://www.excelforum.com/2419378-post5.html

Thx DonkeyOte for your help and insight. You can mark this post 'Solved'

I ended up modifying Marcol's code in Access and it worked like a charm. For those of you interested, this is the end result:

Function FindString(rng As Variant)

    Dim ctr As Integer
    Dim arrSplit As Variant

    FindString = ""
    arrSplit = Split(rng, " ")
    
    For ctr = LBound(arrSplit) To UBound(arrSplit)
        If Len(arrSplit(ctr)) = 8 Then
            If IsNumeric(Mid(arrSplit(ctr), 3, 5)) And Not IsNumeric(arrSplit(ctr)) Then
                FindString = arrSplit(ctr)
                Exit For
            End If
        End If
    Next
End Function

I'm glad you have resolved but obviously you're not being rigorous regards your checks using this approach - ie @@12345! would pass as valid.

If you wanted to avoid RegExp and still use a robust test then you can use Like [edit: have not tested re: Access mind you!], eg:

Function FindString(rng As Variant)
    Dim ctr As Integer
    Dim arrSplit As Variant
    FindString = ""
    arrSplit = Split(rng)
    For ctr = LBound(arrSplit) To UBound(arrSplit) Step 1
        If arrSplit(ctr) Like "[A-Z][A-Z]#####[A-Z]" Then
            FindString = arrSplit(ctr)
            Exit For
        End If
    Next ctr
End Function

not use of Exit For means you will only ever return first match should multiple exist - I presume this remains the intention.