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?

It's not clear on the scope of your data (how many rows/columns) nor where you want to put the results. — , May 06 '16 at 14:42
Like I said, that part is easy. It's a large sheet to parse through. Once it finds one of these problem item numbers, it should copy the current row into an array or something and insert it beneath the current row. The hard part for me is how to change the item numbers so that the (A/B) section changes to A on one row and B on the other. Once I have some string variables or something with those modified item numbers the rest is fine. — gualdhar, May 06 '16 at 14:55

score 3 · Accepted Answer · edited May 23 '17 at 11:52

3

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).}

edited May 23 '17 at 11:52

Community

1
1

answered May 06 '16 at 15:08

Super Cool. Also this is brand new information for me but totally trumps my approach – Doug Coats May 06 '16 at 15:13
Looks like this will work great. Thanks for the assist. Any other good resources for learning about RegEx besides the link you posted? – gualdhar May 06 '16 at 15:30
If it isn't covered [there](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835) then it probably doesn't exist. I will tell you that **'gimme a regex pattern'** style inquiries are generally frowned upon as an [SO](http://stackoverflow.com/tour) question without some evidience of original effort toward a solution. – May 06 '16 at 15:33
I wasn't specifically looking for a RegEx solution, just saying that I didn't know anything about it so I hadn't tried it yet. I was debating using two splits, one on ( and one on / then using a left or something until you posted your answer. This is much more elegant, and I appreciate it. – gualdhar May 06 '16 at 15:49
My answer that i decided wasnt worth posting after Jeeped's answer was basically hacking it apart then recombining. I didnt know regular expressions was a thing, and I have been having an grand ol time learning this stuff since he posted the links. I wish I had learned about this before O_o – Doug Coats May 06 '16 at 16:21
1

@DougCoats - Welcome to the [regex] rabbithole Alice. Have some tea before you're late. – May 06 '16 at 16:26
@Jeeped, are there things I should be aware of before I go too much farther? Or are these things Ill be forced to learn along the way? lol – Doug Coats May 06 '16 at 16:31
@DougCoats - My patterns are typically 'long hand' as I don't use [regex] enough to always remember the shorthand (e.g. \s for spaces) and tend to use 'literal' characters by escaping with the `\\`. I don't think it makes much of a difference unless you are trying to impress someone else with a cryptic looking regex pattern that works great but no one else understands. – May 06 '16 at 16:42
@Jeeped one more question, in the snippet there you used hard-coded values for Mid. It works fine for this, however lets say I need to do a new regex like \([A-Z]{1,3}/[A-Z]{1,3}\) so there are between 1 and 3 characters in each section. Is there a way to pull those sections out without hard-coding their location in the pattern string? – gualdhar May 06 '16 at 17:22
You need the existing regex object for the subsequent .replace operation so it would probably be easier with instr. – May 06 '16 at 17:24
@gualdhar - Please see addendum above. – May 06 '16 at 18:10

Using VBA to parse and split a string with wildcards?

1 Answers1