Как использовать регулярные выражения (Regex)в Microsoft Excel как внутри ячейки,так и в цикле.

regex excel vba


Как использовать регулярные выражения в Excel и воспользоваться преимуществами мощной сетки,подобной Excel,для работы с данными?

  • Функция In-cell для возврата совпаденного образца или замененного значения в строке.
  • Подключиться к циклу через столбец данных и извлечь совпадения с соседними ячейками.
  • Какая настройка необходима?
  • Что такое специальные символы Excel для регулярных выражений?

Я понимаю, что Regex не идеален для многих ситуаций ( использовать или не использовать регулярные выражения? ), Так как Excel может использовать команды типа Left , Mid , Right , Instr для подобных манипуляций.





Answer 1 Portland Runner


Регулярные выражения используются для сопоставления с образцом.

Для использования в Excel выполните следующие действия:

Шаг 1. Добавьте ссылку VBA на «Регулярные выражения Microsoft VBScript 5.5»

  • Выберите вкладку «Разработчик» ( у меня нет этой вкладки, что мне делать? )
  • Выберите иконку "Visual Basic" в разделе ленты "Code".
  • В окне "Microsoft Visual Basic for Applications" в верхнем меню выберите "Tools".
  • Выберите "Ссылки"
  • Установите флажок рядом с "Microsoft VBScript Regular Expressions 5.5" для включения в вашу рабочую книгу.
  • Нажмите "ОК"

Шаг 2 : Определите свой шаблон

Основные определения:

- Диапазон

  • Например, a-z соответствует строчным буквам от a до z
  • Например, 0-5 соответствует любому числу от 0 до 5

[] Подберите точно один из объектов в этих скобках.

  • Например, [a] соответствует букве а
  • Например, [abc] соответствует одной букве, которая может быть a, b или c
  • Например, [a-z] соответствует любой отдельной строчной букве алфавита.

() Группирует разные совпадения для целей возврата. Смотрите примеры ниже.

{} Множитель для повторных копий шаблона, определенного перед ним.

  • Например, [a]{2} соответствует двум последовательным буквам в нижнем регистре a: aa
  • Например, [a]{1,3} соответствует по крайней мере от одной до трех строчных букв a , aa , aaa

+ Совпадение хотя бы одного или нескольких шаблонов, определенных перед ним.

  • Например, a+ будет соответствовать последовательным a, a , aa , aaa и т. Д.

? Совпадение нуля или одного из шаблонов, определенных перед ним.

  • Например,модель может присутствовать или не присутствовать,но может быть сопоставлена только один раз.
  • Например, [a-z]? соответствует пустой строке или любой строчной букве.

* Матч ноль или более шаблона, определенного перед ним. - Например, подстановочный знак для шаблона, который может присутствовать или не присутствовать. - Например, [a-z]* соответствует пустой строке или строке строчных букв.

. Соответствует любому символу, кроме новой строки \n

  • Например , a. Соответствует двухсимвольной строке, начинающейся с a и заканчивающейся чем угодно, кроме \n

| Оператор ИЛИ

  • Например, a|b означает, что a или b могут быть сопоставлены.
  • Например, red|white|orange соответствует точно одному из цветов.

^ НЕ оператор

  • Например, символ [^0-9] не может содержать число
  • Например , [^aA] символ не может быть в нижнем регистре a или верхний регистр A

\ Избегает специального символа, который следует (переопределяет вышеуказанное поведение)

  • Например , \. , \\ , \( , \? , \$ , \^

Закрепление паттернов:

^ Совпадение должно происходить в начале строки

  • Например, ^a первый символ должен быть строчной буквой a
  • Например, ^[0-9] Первый символ должен быть числом.

$ Match должен встречаться в конце строки

  • Например , a$ Последний символ должен быть строчной буквой a

Таблица приоритетов:

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

Предопределенные сокращения символов:

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

Пример 1 : Запуск от имени макроса

В следующем примере макроса выполняется поиск значения в ячейке A1 , чтобы определить, являются ли первые 1 или 2 символа цифрами. Если это так, они удаляются, и отображается остальная часть строки. Если нет, то появится окно, сообщающее, что совпадений не найдено. Значения ячейки A1 , 12abc , вернут abc , значение 1abc вернет abc , значение abc123 вернет «Not Matched», поскольку цифры не были в начале строки.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value

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

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

Пример 2 : Запуск в качестве функции в ячейке

Этот пример такой же,как и пример 1,но он настроен на выполнение функции внутри ячейки.Для использования измените код на этот:

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

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

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Поместите свои строки ("12abc") в ячейку A1 . Введите эту формулу =simpleCellRegex(A1) в ячейку B1 , и результатом будет «abc».

results image


Пример 3 : проходной диапазон

Этот пример такой же,как и пример 1,но проходит через ряд ячеек.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

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

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

Пример 4 : Разделение разных шаблонов

В этом примере выполняется циклический просмотр диапазона ( A1 , A2 и A3 ) и поиск строки, начинающейся с трех цифр, за которыми следует один буквенный символ, а затем 4 цифровых знака. Выходные данные разделяются на совпадения шаблона на соседние ячейки с помощью () . $1 представляет первый шаблон, найденный в первом наборе () .

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value

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

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Results:

results image


Дополнительные примеры шаблонов

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit



Answer 2 Patrick Böker


Для использования регулярных выражений непосредственно в формулах Excel может помочь следующий UDF (функция,определяемая пользователем).Он более или менее непосредственно раскрывает функциональность регулярных выражений в качестве функции Excel.

Как это работает

Нужно 2-3 параметра.

  1. Текст для использования регулярного выражения.
  2. Регулярное выражение.
  3. Строка формата, определяющая, как должен выглядеть результат. Он может содержать $0 , $1 , $2 и так далее. $0 - это полное совпадение, $1 и выше соответствуют соответствующим группам совпадений в регулярном выражении. По умолчанию $0 .

Некоторые примеры

Извлечение адреса электронной почты:

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

Результаты: [email protected]

Извлечение нескольких подложек:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Результаты в: E-Mail: [email protected], Name: Peter Gordon

Разделить комбинированную строку в одной ячейке на компоненты в нескольких ячейках:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Результаты: Peter Gordon [email protected] ...

Как пользоваться

Чтобы использовать этот UDF, сделайте следующее (грубо говоря, на этой странице Microsoft . У них есть хорошая дополнительная информация!):

  1. В Excel в файле с поддержкой макросов ('.xlsm') нажмите ALT+F11 , чтобы открыть редактор Microsoft Visual Basic для приложений .
  2. Добавьте ссылку на VBA в библиотеку регулярных выражений (бесстыдно скопировано из ответа Portland Runners ++ ):
    1. Нажмите на Инструменты -> Ссылки (извините за скриншот на немецком)Tools -> References
    2. Найдите в списке регулярные выражения Microsoft VBScript 5.5 и установите флажок рядом с ним.
    3. Нажмите ОК .
  3. Нажмите на Вставить модуль . Если вы даете модуль другое имени убедитесь , что модуль имеет не иметь такое же имя , как ОДС ниже (например , присвоение имен модуль Regex и функция regex вызывают #NAME! Ошибку).

    Second icon in the icon row -> Module

  4. В большом текстовом окне посередине вставьте следующее:

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
        Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
        Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
        Dim replaceNumber As Integer
    
        With inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        With outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        End With
        With outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With
    
        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
        Else
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber
    
                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Else
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Exit Function
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Next
            regex = outputPattern
        End If
    End Function
  5. Сохраните и закройте окно редактора Microsoft Visual Basic для приложений .




Answer 3 SAm


Расширение на patszim «s ответ для тех , кто в спешке.

  1. Открыть рабочую книгу Excel.
  2. Alt+F11чтобы открыть окно VBAMacros.
  3. Добавьте ссылку на регулярное выражение в Инструменты, затем Ссылки
    ![Excel VBA Form add references
  4. и выбрав Microsoft VBScript Regular Expression 5.5
    ![Excel VBA add regex reference
  5. Вставьте новый модуль (код должен находиться в модуле,иначе он не будет работать).
    ![Excel VBA insert code module
  6. В только что вставленный модуль,
    ![Excel VBA insert code into module
  7. добавьте следующий код:

    Function RegxFunc(strInput As String, regexPattern As String) As String
        Dim regEx As New RegExp
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = regexPattern
        End With
    
        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            RegxFunc = matches(0).Value
        Else
            RegxFunc = "not matched"
        End If
    End Function
  8. ![Excel regex function in-cell usageШаблон регулярного выражения помещается в одну из ячеек, и для него используется абсолютная ссылка . Функция будет привязана к книге, в которой она создана.
    Если необходимо использовать его в разных книгах, сохраните функцию в Personal.XLSB.




Answer 4 Vikas Gautam


Вот моя попытка:

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If

    End With
End Function



Answer 5 DeezCashews


Мне нужно было использовать это как функцию ячейки (например, SUM или VLOOKUP ) и я обнаружил, что это легко:

  1. Убедитесь,что вы находитесь в файле Excel с поддержкой макросов (сохраните как xlsm).
  2. Открытые инструменты для разработчиковAlt+F11
  3. Добавьте Microsoft VBScript Regular Expressions 5.5, как в других ответах
  4. Создайте следующую функцию либо в рабочей книге,либо в собственном модуле:

    Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
        Dim regex As New VBScript_RegExp_55.RegExp
        Dim strInput As String
    
        strInput = myRange.Value
    
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
    
        REGPLACE = regex.Replace(strInput, outputPattern)
    
    End Function
  5. Затем вы можете использовать в ячейке с =REGPLACE(B1, "(\w) (\d+)", "$1$2") (например: от "A 243" до "A243")