How to extract a generic string (pattern) of character & digits in VBA?

Question

I'm trying to build a function on which I'm blocked since a long time now.

I would like, from a string in a cell, extract a sub-string like "ABC123" or "AB1234". There are some conditions :

It must be like "ABC123" or "AB1234" so 3 char and 3 Numeric or 2 char and 4 Numeric. I.e. "CDE789" is therefore also one of the pattern.
The String in which we are looking has no particular size. It may contain, or not, the pattern.
If the search term should have a space after or before (then only that pattern). Not that it doesn't apply if we are at the beginning of the string or at the end.
The function should return that particular set of string (i.e. in a column besides that already exist).

I tried the like function, arrays or even dictionary but I can't find a solution. For instance I tried to loop trough the LEN of the string, subdivide in 6 variables and check with an IF if the first 2 where char and the last 4 where digits. It's quite heavy and not applicable for a large set of data.

Also please apologies for my broken English, do not hesitate to correct me, I'm also her to learn this :)

Thank you !

RegEX ? I'm not familiar with that but it's an excellent idea ! Thank you ! — Hive, Jun 20 '17 at 08:04
there is `like "[A-Z]"` way of doing things, but I think you'll get lost in the combinations that way, where regex, will look a lot cleaner for you. — Nathan_Sav, Jun 20 '17 at 08:05

score 0 · Answer 1 · answered Jun 20 '17 at 08:39

0

VBA: Count amount of letters and numbers with function

Function AlphaNumeric(pInput As String) As String
'Updateby20140303
Dim xRegex As Object
Dim xMc As Object
Dim xM As Object
Dim xOut As String
Set xRegex = CreateObject("vbscript.regexp")
xRegex.Global = True
xRegex.ignorecase = True
xRegex.Pattern = "[^\w]"
AlphaNumeric = ""
If Not xRegex.test(pInput) Then
    xRegex.Pattern = "(\d+|[a-z]+)"
    Set xMc = xRegex.Execute(pInput)
    For Each xM In xMc
        xOut = xOut & (xM.Length & IIf(IsNumeric(xM), "N", "L"))
    Next
    AlphaNumeric = xOut
End If
End Function

answered Jun 20 '17 at 08:39

JGK

138
3
15

If I try it in a cell, it only gives me blank cells. – Hive Jun 20 '17 at 08:54
You should place this code in Module. and type like below in A3 column and place your value A2 column =AlphaNumeric(A2) – JGK Jun 21 '17 at 11:35

Hive · Answer 2 · 2017-06-20T09:36:19.420

EDIT : found !

Thanks to your answers, I achieved this :

Function GetID(MyRange As Variant)

    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As Object

    strPattern = "([a-zA-Z]{3})([0-9]{3})"

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If strPattern <> "" Then
        strInput = MyRange.Value
        strReplace = strPattern

        If regEx.test(strInput) Then
            Set strOutput = regEx.Execute(MyRange.Value)
            GetID = regEx.Execute(strInput)(0)
        Else
            GetID = "Not matched"
        End If
    End If

End Function

It works. However there is one case where it doesn't : - If I have more character than my pattern. For instance ABC123456 will return ABC123. It should not be taken into account, only "ABC123". Even AABC123 is taken into account while it shouldn't.

EDIT : it works by adding \b at beginning and at the end :

"\b([a-zA-Z]{3})([0-9]{3})\b"

Thank you very much !

score -1 · Answer 3 · answered Jun 20 '17 at 08:08

-1

You should use regular expressions.

Here you have an example that returns the match, as you want: Returning a regex match in VBA (excel)

answered Jun 20 '17 at 08:08

Fran Reneses

29
4

How to extract a generic string (pattern) of character & digits in VBA?

3 Answers3