In excel 2010, substitute any number corresponding to pattern with look-up value

Question

I'm trying to figure out a way to replace/substitute/regex a specific pattern in an Excel table with the corresponding value from another table. The pattern, which appears x times inside a text block, always follows these rules:

{[4,9]{1}[0-9]{5}} (enclosed in curly brackets, a "4" or a "9" followed by 5 numbers, each of which can be anything from 0 to 9, such as "{412345}" or "{961723}".)

The desired formula should then strip the brackets out, lookup the number in another table, and return the corresponding entry from another column.

I've managed to accomplish this with four predefined numbers:

However, I need to expand the formula to include any number matching the pattern. I suppose a VBA UDF is the way to go, though I haven't got a clue how to do it...

PS: if anyone is interested in pushing the limits a little further, the next step I'll have to deal with is to add .X to the numbers inside the curly brackets, where X represents the table within which the values have to be checked against. For example: "{412345.2}" would mean "check 412345 in table 2 and replace with found value", and "{912345.5}" should be "check 912345 in table 5 and replace with found value".

Answer 1

Using Macros / VBA:

Public Function patSub(theValue As String) As String
    result = theValue
    Dim rango As Range
    Set rango = ActiveSheet.Range("E1:F6")
    strPattern = "{[4,9]{1}[0-9]{5}}"
    Dim regEx As New RegExp
    Dim matches
    With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
    End With
    Set matches = regEx.Execute(theValue)
    For Each Match In matches
        On Error Resume Next
        lookupmatch = Match.Value
        lenMatch = Len(lookupmatch)
        lookupmatch1 = Mid(lookupmatch, 2, lenMatch - 2)
        lookupValue = Application.VLookup(lookupmatch1 + 0, rango, 2, Falso)
        If lookupValue <> "Error 2042" Then
            result = Replace(result, lookupmatch, lookupValue)
        End If
    Next
    patSub = result
End Function

Open VBA /Macros with alt+F11, insert a new module under ThisWorkbook and paste the code on the right side.

To make Regular Expressions work with VBA you need to reference to Microsoft VBScript Regular Expressions 5.5:

• Select "Tools / References"
• Check the box next to Microsoft VBScript Regular Expressions 5.5
• Click "OK"

This UDF uses two variables:

• rango1: The reference table that contains the items.
• strPattern: The regular expression.

If the data is in cell A1, then in B1you have to put =patSub(A1).