Using a regex expression in an array formula

[crookesa]

Is it possible to search use a regular expression in an array formula?

I have some defined values in cell A11 which works but I also want to lookup for example anything that starts with 4 numbers (which will represent a time code).

=IF(ISERROR(INDEX(employee_names,SMALL(IF(employee_1=$A$11,ROW(employee_1)),ROW(1:1))-1,1))," ",INDEX(employee_names,SMALL(IF(employee_1=$A$11,ROW(employee_1)),ROW(1:1))-1,1))

 

[Special-K99]

Doesnt solve your problem but why not shorten that to

=IFERROR(INDEX(employee_names,SMALL(IF(employee_1=$A$11,ROW(employee_1)),ROW(1:1))-1,1),"")

Also remove the space from the quotes, you shouldn't really need a space there, leave it as a null.

 

[crookesa]

Doesnt solve your problem but why not shorten that to

=IFERROR(INDEX(employee_names,SMALL(IF(employee_1=$A$11,ROW(employee_1)),ROW(1:1))-1,1),"")

Also remove the space from the quotes, you shouldn't really need a space there, leave it as a null.

That's helpful in other ways. Thanks for that

 

[crookesa]

Not sure if it helps anyone or if I've done it the right way but I wrote a UDF to return the cell address for anything that matches the regex expression.

Function findAdHocDuties(DayRange As Range, ElementNumber As Integer) As Variant

Dim ArraySize As Integer: ArraySize = DayRange.count
Dim AdHocCells() As String
ReDim Preserve AdHocCells(ArraySize)


Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
Dim i As Integer: i = 0


strPattern = "^[0-9]{4}[-]"


For Each cell In DayRange
   Debug.Print cell.Address
   
    If strPattern <> "" Then
        strInput = cell.Value
        strReplace = ""


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


        If regEx.Test(strInput) Then
            AdHocCells(i) = cell.Parent.Name & "!" & cell.Address(External:=False)
            i = i + 1
        Else
            findAdHocDuties = "Not matched"
        End If
    End If
   
Next cell


ReDim Preserve AdHocCells(i - 1)
findAdHocDuties = AdHocCells(ElementNumber)


End Function

My duties all have the form of XXXX-YY-ZZZZ where XXXX is the 4 digit time (HHMM) followed by YY which is a length in hours from 1 to 24 then followed by criteria I don't care about really.

I chose to pass the element number in as I'm using an INDEX MATCH combination to find the corresponding values I need but as I wanted to separate out the results and also an array formula won't allow me to do that.

Any suggestions or advice to change or improve it would be grateful