VBA Find a string that has range of value in it with Regular Expression and replace with each value in that range

Question

First of all, sorry for the long title. I just don't know how to put it succinctly. I am trying to do this in VBA as normal Excel will not cut it.

Basically, I have a column. Each cells may contain data in the format of something like

flat 10-14;Flat 18-19;unit 7-9;flat A-D;ABC;DEF;

What I need is to find the string that has "-" in it, and attempt to replace it with anything in between. so the above code will become

Flat 10, Flat 11; Flat 12, Flat 14;Flat 18, Flat 19;Unit 7, Unit 8, Unit 9;Flat A, Flat B, Flat C; ABC;DEF;

With the help of this article on RegExpression, I have managed to work out how to expand the bits of data with number, which I will post the code below. However, I don't know a good way to expand the data with the letter. i.e from Flat A-C to Flat A, Flat B, Flat C

My code is below, please feel free to give any pointers if you think it can be more efficient. I am very much an amateur at this. Thank you in advance.

Sub CallRegEx()
    Dim r As Match
    Dim mcolResults As MatchCollection
    Dim strInput As String, strPattern As String
    Dim test As String, StrOutput As String, prefix As String
    Dim startno As Long, endno As Long
    Dim myrange As Range

    strPattern = "(Flat|Unit) [0-9]+-+[0-9]+"

With Worksheets("Sheet1")
    lrow = .Cells(Rows.Count, 9).End(xlUp).Row
    For Each x In .Range("A2:A" & lrow)
        strInput = Range("A" & x.Row).Value
        Set mcolResults = RegEx(strInput, strPattern, True, , True)
        If Not mcolResults Is Nothing Then

        StrOutput = strInput

        For Each r In mcolResults
                    startno = Mid(r, (InStr(r, "-") - 2), 2)
                    endno = Mid(r, (InStr(r, "-") + 1))
                    prefix = Mid(r, 1, 4)
                    test = ""
                        For i = startno To endno - 1
                        test = test & prefix & " " & i & ","
                        Next i
                        test = test & prefix & " " & endno
                    'this is because I don't want the comma at the end of the last value
                    StrOutput = Replace(StrOutput, r, test)

            Debug.Print r ' remove in production
        Next r
        End If
    .Range("D" & x.Row).Value = StrOutput
    Next x

End With
End Sub

This function below is to support the Sub above

Function RegEx(strInput As String, strPattern As String, _
    Optional GlobalSearch As Boolean, Optional MultiLine As Boolean, _
    Optional IgnoreCase As Boolean) As MatchCollection

    Dim mcolResults As MatchCollection
    Dim objRegEx As New RegExp

    If strPattern <> vbNullString Then

        With objRegEx
            .Global = GlobalSearch
            .MultiLine = MultiLine
            .IgnoreCase = IgnoreCase
            .Pattern = strPattern
        End With

        If objRegEx.test(strInput) Then
            Set mcolResults = objRegEx.Execute(strInput)
            Set RegEx = mcolResults
        End If
    End If
End Function

Alex K. · Accepted Answer · 2018-11-27 18:07:45Z

2

Letters have character codes that are ordinal (A < B < C ...) & these can be accessed via asc()/chr$() - here is one way to do it:

inputStr = "flat 10-14;Flat 18-19;unit 7-9;flat A-D;ABC;DEF;flat 6;flat T"

Dim re As RegExp: Set re = New RegExp
    re.Pattern = "(flat|unit)\s+((\d+)-(\d+)|([A-Z])-([A-Z]))"
    re.Global = True
    re.IgnoreCase = True

Dim m As MatchCollection
Dim start As Variant, fin As Variant
Dim tokens() As String
Dim i As Long, j As Long
Dim isDigit As Boolean

tokens = Split(inputStr, ";")

For i = 0 To UBound(tokens) '// loop over tokens

    Set m = re.Execute(tokens(i))

    If (m.Count) Then
        With m.Item(0)
            start = .SubMatches(2) '// first match number/letter
            isDigit = Not IsEmpty(start) '// is letter or number?

            If (isDigit) Then '// number
                fin = .SubMatches(3)
            Else '// letter captured as char code
                start = Asc(.SubMatches(4))
                fin = Asc(.SubMatches(5))
            End If

            tokens(i) = ""

            '// loop over items
            For j = start To fin
                 tokens(i) = tokens(i) & .SubMatches(0) & " " & IIf(isDigit, j, Chr$(j)) & ";"
            Next
        End With
    ElseIf i <> UBound(tokens) Then tokens(i) = tokens(i) & ";"
    End If
Next

Debug.Print Join(tokens, "")

flat 10;flat 11;flat 12;flat 13;flat 14;Flat 18;Flat 19;unit 7;unit 8;unit 9;flat A;flat B;flat C;flat D;ABC;DEF;flat 6;flat T

answered Nov 27, 2018 at 18:07

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Minh Over a year ago

Thank you Alex, you understood what I meant. And thank you also for writing a new script which looks much better. However, for my purpose, I cannot use ; as a delimiter in the new expanded text, but this is certainly something I can work on.

Collectives™ on Stack Overflow

VBA Find a string that has range of value in it with Regular Expression and replace with each value in that range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related