Combine RegEx and fill an underminate number of cells

Question

I have a cell in Excel that holds a long string in cell A1:

"ABC12+BED58,YZ001"

I have the following regex to match some specific variables in my string

strPattern = "[A-Z]{1,3}[0-9]{2,4}"

Basically, I need to write a macro or a function (I would prefer a function actually) that will fill cell A2, A3, A4 like that:

ABC12
BED58
YZ001

The thing is, there is an undeterminate number of parameters in the string (so for example, it could go all the way through A200).

I'm thinking of a function get_n_variables(str, n) that would return the Nth unique match

Here is my progress so far but the function returns #VALUE!

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim matches As Object


    strPattern = "[A-Z]{1,3}[0-9]{2,4}"

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

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

        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            simpleCellRegex = matches(0).SubMatches(0)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Answer 1

You can actually still use an function if you use an array

• select B1:D1
• enter this formula =simpleCellRegex(A1) and press CTRL+SHIFT+ENTER

if you dont know how many matches enter in more cells than there may be matches

code

Function simpleCellRegex(StrIn As String) As Variant

    Dim regEx As Object
    Dim regMC As Object
    Dim X
    Dim strPattern As String
    Dim lngCnt As Long

    strPattern = "[A-Z]{1,3}[0-9]{2,4}"

    Set regEx = CreateObject("vbscript.regexp")

     With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
      If .Test(StrIn) Then
          Set regMC = .Execute(StrIn)
          ReDim X(0 To regMC.Count - 1) As String
            For lngCnt = 0 To UBound(X)
                X(lngCnt) = regMC(lngCnt)
            Next
          simpleCellRegex = X
      Else
          simpleCellRegex = "Not matched"
      End If
    End With
End Function

Answer 2

From MrExcel Forum:

You can not put a function in a cell to change other cells. Functions do not work this way.

Thus, it should be a sub, like this, e.g. (outputs the matches under the selected cell with our input string):

Sub simpleCellRegex()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim matches As MatchCollection
    Dim i As Long, cnt As Long


    strPattern = "[A-Z]{1,3}[0-9]{2,4}"
    cnt = 1

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

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

        If regEx.test(strInput) Then
         Set objMatches = regEx.Execute(strInput)
         For i = 0 To objMatches.Count - 1
            ActiveCell.Offset(cnt).Value = objMatches.Item(i)
            cnt = cnt + 1
         Next
        End If

    End If

End Sub

Output: