How do I combine a regular expression function in vlookup?

Question

I have a VBA regular expression which I would like to combine with VLOOKUP however it does not return the value based on the regular expression if used with VLOOKUP.

This is what it returns when I execution the function =udfRegEx(A2,B2)

String

Microsoft Windows Server 2003, Standard Edition (64-bit)

Regular expression

^([^,]*)

Result

Microsoft Windows Server 2003

However when I execute =IFERROR(VLOOKUP(udfRegEx(A2,RegularExpression!B2),[Sample.xls]Sheet1!$B$2:$E$4177,4,FALSE),0) it still returns Microsoft Windows Server 2003, Standard Edition (64-bit)

Column B2 is the regular expression ^([^,]*)

@Jerry - Sorry about that. The A2 in `udfRegEx(A2,RegularExpression!B2)` refers to the data source and B2 refers to the regular expression. The B2 in `[Sample.xls]Sheet1!$B$2:$E$4177` refers to the array which needs to match. — PeanutsMonkey, Oct 10 '13 at 04:28
Are you willing to use external addins or do you need it to use your UDF ? — BlueTrin, Feb 13 '15 at 12:14

score 0 · Answer 1 · answered Oct 10 '13 at 05:58

0

Try using:

=IFERROR(udfRegEx(VLOOKUP(udfRegEx(A2,RegularExpression!B2),[Sample.xls]Sheet1!$B$2:$E$4177,4,FALSE),RegularExpression!B2),0)

A shot in the dark.

answered Oct 10 '13 at 05:58

Jerry

70,495
13
100
144

Thanks. I tried that already and it doesn't work. The only way it appears to work is if I have a cell that returns a value from a `VLOOKUP` function and another cell that uses that value for the regular expression. If it helps, I'm using the function from the post at http://stackoverflow.com/questions/9744602/how-do-you-execute-a-regular-expression-in-excel – PeanutsMonkey Oct 13 '13 at 18:01
@PeanutsMonkey You could have put that in your question, it would have avoided this, you know? Could you put a spreadsheet on http://ge.tt? – Jerry Oct 13 '13 at 18:09
Sorry Jerry. I thought I had. Unfortunately no. I don't have the authority to do so as it has personal information – PeanutsMonkey Oct 14 '13 at 00:05
1

@PeanutsMonkey You can't even put a dummy spreadsheet with only the minimum? – Jerry Oct 14 '13 at 04:19

BlueTrin · Answer 2 · 2015-02-13T21:13:48.353

I had to do this for my personal use, so I made an Excel Addin, here is the GitHub address.

https://github.com/BlueTrin/BlueXL

If you want I can host a compiled version if you need it. It adds a function called BXLookup, this function supports Regex, you can also select the column on which you perform the lookup and select the columns to print.

I made a binary for you:

https://bintray.com/bluetrin/BlueXL/BlueXL/0.1.0/view?sort=&order=#

Of course this does not work if you want only to use VBA, but if you do not mind using an addin, there is an example in the spreadsheet on GitHub.

Please could you clarify what you have in: [Sample.xls]Sheet1!$B$2:$E$4177

score 0 · Answer 3 · answered Feb 08 '20 at 14:16

0

From Office 365 on there is new function XLookUp, which does (finally) the hob you looked for. It is explained here: https://www.excelcampus.com/functions/xlookup-explained/

answered Feb 08 '20 at 14:16

Rk-Koma

1

score 0 · Answer 4 · 2021-07-14T06:15:09.803

You don't need a regular expression to remove everything after the first comma. The following function does the same:

MID(A1,1,SEARCH(",",A1)-1)

That said, the following works, at least with Office 365 (not tested on an earlier version):

Public Function RegExpGroup(R As String, S As String, IMatch As Integer, IGroup As Integer) As Variant
    Dim RegExp As Object, Matches As Object, SubMatches As Object
    Set RegExp = CreateObject("VBScript.RegExp")
    With RegExp
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = R
    End With
    Set Matches = RegExp.Execute(S)
    If Matches.Count >= IMatch Then
        Set SubMatches = Matches.Item(IMatch - 1).SubMatches
        If SubMatches.Count >= IGroup Then
            RegExpGroup = SubMatches.Item(IGroup - 1)
        Else
            RegExpGroup = CVErr(xlErrValue)
        End If
    Else
        RegExpGroup = CVErr(xlErrValue)
    End If
End Function

Now, with the values as:

And the formulas in A4, A5:

=RegExpGroup(A2,A1,1,1),C1:D2,2,FALSE)
=IFERROR(VLOOKUP(RegExpGroup(A2,A1,1,1),C1:D2,2,FALSE),"Not found")

You get the expected result.

How do I combine a regular expression function in vlookup?

4 Answers4