Excel VBA Regex Function That Returns Multiple Matches Into A Single Cell

Question

I need help getting my Excel function to work. The goal is to run an in-cell function that extracts all pattern matches of a regex function from the input of another cell into one cell, not an array of cells.

I have tried this using an array which returns two matches in the function dialogue box preview but only outputs the first match in the cell. I have also tried using a collection but had no luck with that.

Here is my current code and a sample of text that would be used as the function's string input:

Function RegexMatches(strInput As String) As Variant

Dim rMatch As Match
Dim arrayMatches
Dim i As Long

arrayMatches = Array()

With New RegExp
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(Code)[\s][\d]{2,4}"
        For Each rMatch In .Execute(strInput)
        ReDim Preserve arrayMatches(i)
        arrayMatches(i) = rMatch.Value
        i = i + 1
    Next
End With

    RegexMatches = arrayMatches
End Function

Sample strInput from an Excel cell:

Code 123 some random text
goes here and continues to the next line
Code 4567 followed by more text
including new lines not just wrapped text

The desired output from this function would be both (2) matched values from the regex function into a single cell (e.g. "Code 123 Code 4567").

Any help is greatly appreciated!

Concat the match values, and then return this trimmed string. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jul 7, 2017 at 21:01
@WiktorStribiżew That is what I was thinking conceptually but I don't know how to do it inside the function using VBA code. — BoogieMan2718
– BoogieMan2718, Commented Jul 7, 2017 at 21:08
@Mat'sMug Thanks for the insight, I missed the last three lines of the copy/paste. I'll correct it now. — BoogieMan2718
– BoogieMan2718, Commented Jul 7, 2017 at 21:12

Mathieu Guindon · Accepted Answer · 2017-07-07 21:17:08Z

6

Looks like you missed off the end of your function (as per Mat's Mug's comment)? Try this (which is as per Wiktor's comment).

Edit: amended in light of Mat's Mug's suggestion.

Function RegexMatches(strInput As String) As String

Dim rMatch As Object
Dim s As String
Dim arrayMatches()
Dim i As Long

With New RegExp
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(Code)[\s][\d]{2,4}"
    If .test(strInput) Then
        For Each rMatch In .Execute(strInput)
            ReDim Preserve arrayMatches(i)
            arrayMatches(i) = rMatch.Value
            i = i + 1
            's = s & " " & rMatch
        Next
    End If
End With

RegexMatches = Join(arrayMatches, " ")

End Function

edited Jul 7, 2017 at 21:17

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Jul 7, 2017 at 21:02

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

15 Comments

Mathieu Guindon Over a year ago

I'd make the function return an array of matches as the name implies (Variant was fine then), and use Join at the call site to join the values with a vbNewLine to get the string to dump into the cell.

SJR Over a year ago

@Mat'sMug - thanks, have amended. I agree it is a more elegant approach.

Mathieu Guindon Over a year ago

Except you're doing the Join in the function now (returns a String); RegexMatches = arrayMatches, and let the caller deal with the string representation ;-)

Mathieu Guindon Over a year ago

Oh duh, just realized the function is actually used as a UDF!

Mathieu Guindon Over a year ago

Nothing - the fact that the function was called by a cell had eluded me; joining before returning, and returning a String, is exactly what the OP needs =) if it was just an ordinary function, it being responsible for both the matching and the string representation would be violating SRP IMO.

|

Collectives™ on Stack Overflow

Excel VBA Regex Function That Returns Multiple Matches Into A Single Cell

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related