Using VBA to parse and split a string with wildcards?

Question

I've got a sheet that contains item numbers of alphanumeric characters, and a bunch of other information in the row. Sometimes, similar items are combined into one row, and the difference on the item number will be shown with (X/Y) to choose which character to use at that point in the item number (not just X or Y, can be any alphanumeric character). In other words, these entries will look like this:

AB(X/Y)CD123

What I need is a way to separate that into the two item numbers ABXCD123 and ABYCD123. After that I'll have to create a row below the current one and copy the current row into it, with the changed item number, but that part is easy. I've tried using InStr to get the (X/Y) flagged, but I don't know how to pull out the X and Y characters to make new strings with them. I also don't know if a wildcard will work with InStr, and I'm not too familiar with RegEx.

Any ideas?

Answer 1

Here is s little introduction to regex¹ in a UDF².

Function partNums(str As String, _
                  Optional num As Integer = 1)
    Dim tmp As String
    Static rgx As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    partNums = vbNullString

    With rgx
        .Global = True
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "\([A-Z]{1}/[A-Z]{1}\)"
        If .Test(str) Then
            tmp = .Execute(str)(0)
            Select Case num
                Case 2
                    tmp = Mid(tmp, 4, 1)
                Case Else
                    tmp = Mid(tmp, 2, 1)
            End Select
            partNums = .Replace(str, tmp)
        End If
    End With
End Function

In B2:B3 as,

=partNums(A2)
=partNums(A3,2)

            

Here is a largely duplicated UDF that handles from 1 to 3 characters.

Function partNums(str As String, _
                  Optional num As Integer = 1)
    Dim tmp As String
    Static rgx As Object

    'with rgx as static, it only has to be created once; beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    End If
    partNums = vbNullString

    With rgx
        .Global = True
        .IgnoreCase = True
        .MultiLine = False
        .Pattern = "\([A-Z]{1,3}/[A-Z]{1,3}\)"
        If .Test(str) Then
            tmp = .Execute(str)(0)
            tmp = Split(Replace(Replace(tmp, Chr(40), vbNullString), Chr(41), vbNullString), Chr(47))(num - 1)
            partNums = .Replace(str, tmp)
        End If
    End With
End Function

            

---

¹ _{regex questions can usually be answered by the solutions in How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops.}

² _{A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).}