Using a regex expression in an array formula

crookesa

Board Regular
Joined
Apr 11, 2013
Messages
88
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).

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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 :)
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top