CODEWITHSUNDEEP
vbaexcel
Tom Ruiz
Tom Ruiz

Reputation: 307

Extract substring with criteria

I have several rows of information pulled from a report in Column C and D, its basically a description someone wants to do with an account they also of course use give you the account number what I want to do is extract that substring the criteria I'm using is that it must start with the letter A and should be as a minimum 17 characters long, Account numbers have a combination of letter and numbers but they all start with letter A i.e A8H66P66FHDSJ2YNTP some of this account numbers have up to 25 characters some have 19 some 17 so again I'm looking to extract a substring from a string that starts with letter A and its atleast 17 characters long

Upvotes: 1

Views: 85

Answers (2)

tigeravatar
tigeravatar

Reputation: 26660

Formula solution:

=IFERROR(TRIM(MID(SUBSTITUTE(C1," ",REPT(" ",LEN(C1))),LEN(C1)*(MATCH(TRUE,INDEX(ISNUMBER(SEARCH("A"&REPT("?",17),TRIM(MID(SUBSTITUTE(C1," ",REPT(" ",LEN(C1))),LEN(C1)*(ROW($1:$100)-1)+1,LEN(C1))))),),0)-1)+1,LEN(C1))),"No Account Number")

Upvotes: 0

omegastripes
omegastripes

Reputation: 12612

Try to use RegEx as shown in the below example:

Sub Test()

    Dim oCell, oMatch

    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = "\bA[A-Z0-9]{16,24}\b"
        For Each oCell In ThisWorkbook.Sheets("Sheet1").Range("C1:D1000")
            For Each oMatch In .Execute(oCell.Value)
                Debug.Print oMatch.Value
            Next
        Next
    End With

End Sub

Upvotes: 1

Related Questions