В Excel 2010 замените любое число, соответствующее шаблону, поисковым значением.

Question

Я пытаюсь найти способ замены / замены / регулярного выражения определенного шаблона в таблице Excel с соответствующим значением из другой таблицы. Шаблон, который появляется x раз внутри текстового блока, всегда следует этим правилам:

{[4,9]{1}[0-9]{5}} (заключены в фигурные скобки, "4" или "9", за которыми следуют 5 чисел, каждое из которых может быть любым от 0 до 9, например как "{412345}" или "{961723}".)

Нужная формула должна затем снять скобки, найти номер в другой таблице и вернуть соответствующую запись из другого столбца.

Мне удалось сделать это с помощью четырех предопределенных чисел:

Однако мне нужно расширить формулу, чтобы включить любое число, соответствующее шаблону. Я полагаю, что VBA UDF - это то, что нужно, хотя я понятия не имею, как это сделать ...

PS: если кто-то заинтересован в том, чтобы расширить границы, я должен добавить следующий шаг.X к числам внутри фигурных скобок, где X представляет таблицу, в которой значения должны быть проверены. Например: «{412345.2}» будет означать "проверить 412345 в таблице 2 и заменить найденным значением", а «{912345.5}» должно быть "проверить 912345 в таблице 5 и заменить найденным значением".

Answer 1

Использование макросов / 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

Откройте VBA /Macros с помощью alt+F11, вставьте новый модуль в ThisWorkbook и вставьте код с правой стороны.

Чтобы регулярные выражения работали с VBA, вам нужно обратиться к регулярным выражениям Microsoft VBScript 5.5:

• Выберите «Инструменты / Ссылки»
• Установите флажок рядом с Регулярные выражения Microsoft VBScript 5.5.
• Нажмите "ОК"

Этот UDF использует две переменные:

• rango1: справочная таблица, содержащая предметы.
• strPattern: регулярное выражение.

Если данные находятся в ячейке A1 , то в B1 вы должны поставить =patSub(A1) .