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

Question

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

• Функция в ячейке, чтобы возвратить совпавший образец или замененное значение в строке.
• Sub для циклического перебора столбца данных и извлечения совпадений для соседних ячеек.
• Какая настройка нужна?
• Каковы специальные символы Excel для регулярных выражений?

---

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

Answer 1

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

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

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

• Выберите вкладку "Разработчик" ( у меня нет этой вкладки, что мне делать?)
• Выберите значок "Visual Basic" в разделе ленты "Код"
• В окне "Microsoft Visual Basic для приложений" выберите "Инструменты" в верхнем меню.
• Выберите "Рекомендации"
• Установите флажок "Регулярные выражения Microsoft VBScript 5.5", чтобы включить его в свою книгу.
• Нажмите "ОК"

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

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

- Спектр.

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

[] Совпадение точно с одним из объектов в этих скобках.

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

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

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

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

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

• Например 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] Первый символ должен быть числом.

$ Соответствие должно происходить в конце строки

• Например 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".

---

Пример 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

Результаты:

---

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

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

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

Как это устроено

Требуется 2-3 параметра.

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

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

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

=regex("Peter Gordon: [e-mail], 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: [e-mail], 47", "\w+@\w+\.\w+", "$0")

Результаты в: [e-mail]

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

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

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

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

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

Результаты в: Peter [e-mail]...

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

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

1. В Excel в файле с поддержкой макросов ('.xlsm') нажмите ALT+F11 открыть редактор Microsoft Visual Basic для приложений.
2. Добавьте ссылку на VBA в библиотеку регулярных выражений (бесстыдно скопировано из ответа Portland Runners++):
   1. Нажмите на Инструменты -> Ссылки (прошу прощения за скриншот на немецком) Рекомендации">
   2. Найдите Microsoft VBScript Regular Expressions 5.5 в списке и поставьте галочку рядом с ним.
   3. Нажмите ОК.

3. Нажмите на Вставить модуль. Если вы даете своему модулю другое имя, убедитесь, что модуль не имеет того же имени, что и UDF ниже (например, называя модуль Regex и функция regex вызывает #NAME! ошибки).

   Модуль">

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

Расширение Patrick Böker для тех, кто спешит.

1. Откройте книгу Excel.
2. Alt + F11, чтобы открыть окно VBA/Macros.
3. Добавьте ссылку на регулярное выражение в Инструменты, затем Ссылки
   
4. и выбрав Microsoft VBScript Regular Expression 5.5
   
5. Вставьте новый модуль (код должен находиться в модуле, иначе он не будет работать).
   
6. В недавно вставленном модуле
   

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. Шаблон регулярного выражения помещается в одну из ячеек, и для него используется абсолютная ссылка. Функция будет привязана к книге, в которой она создана.
   Если необходимо использовать его в разных книгах, сохраните функцию в Personal.XLSB.

Answer 4

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

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

Это не прямой ответ, но он может предоставить вам более эффективную альтернативу. Дело в том, что в Google Таблицах есть несколько встроенных функций Regex, которые могут быть очень удобными и помогают обойти некоторые технические процедуры в Excel. Очевидно, что у использования Excel на вашем ПК есть некоторые преимущества, но для подавляющего большинства пользователей Google Таблицы предложат идентичный опыт и могут предложить некоторые преимущества в переносимости и совместном использовании документов.

Они предлагают

REGEXEXTRACT: извлекает совпадающие подстроки в соответствии с регулярным выражением.

REGEXREPLACE: заменяет часть текстовой строки другой текстовой строкой с использованием регулярных выражений.

ЗАМЕНА: заменяет существующий текст новым текстом в строке.

REPLACE: заменяет часть текстовой строки другой текстовой строкой.

Вы можете ввести их прямо в ячейку и получить все, что захотите.

=REGEXMATCH(A2, "[0-9]+")

Они также довольно хорошо работают в сочетании с другими функциями, такими как операторы IF, например:

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

Надеюсь, это обеспечит простой обходной путь для пользователей, которых насмехается над компонентом VBS в Excel.

Answer 6

Чтобы добавить к ценному контенту, я хотел бы создать это напоминание о том, почему иногда RegEx в VBA не идеален. Не все выражения поддерживаются, но вместо этого могут вызыватьError 5017 и может оставить автора в догадках (что я сам стал жертвой).

В то время как мы можем найти некоторые источники о том, что это поддерживается, было бы полезно знать, какие метасимволы т.д., не поддерживаются. Более подробное объяснение можно найти здесь. В этом источнике упоминается:

"Хотя" регулярное выражение VBScript... версии 5.5 реализует довольно много важных функций регулярных выражений, которые отсутствовали в предыдущих версиях VBScript. ... JavaScript и VBScript реализуют регулярные выражения в стиле Perl. Однако им не хватает целого ряда дополнительных функций, доступных в Perl и других современных разновидностях регулярных выражений:"

---

Итак, не поддерживаются:

• Начало строки ancor \A, в качестве альтернативы используйте ^ каретка для соответствия позиции перед 1-м символом в строке
• Конец строки ancor \Z, в качестве альтернативы используйте $ знак доллара для соответствия позиции после последнего символа в строке
• Позитивный взгляд назад, например: (?<=a)b(в то время как Postive LookAhead будет поддерживается)
• Отрицательный взгляд назад, например: (?<!a)b(в то время как отрицательное LookAhead будет поддерживается)
• Атомная группировка
• Притяжательные квантификаторы
• Юникод, например: \{uFFFF}
• Именованные группы захвата. В качестве альтернативы используйте нумерованные группы захвата.
• Встроенные модификаторы, например: /i (чувствительность к регистру) или /g (глобальный) и т. д. Установите их через RegExp свойства объекта> RegExp.Global = True а также RegExp.IgnoreCase = True если доступно.
• Условные
• Комментарии к регулярным выражениям. Добавьте их с помощью обычных' комментарии в скрипте

---

Я уже не раз ударялся о стену, используя регулярные выражения в VBA. Обычно сLookBehindно иногда я даже забываю модификаторы. Я сам не испытал всех этих вышеупомянутых фонов, но подумал, что постараюсь быть более подробным, ссылаясь на более подробную информацию. Не стесняйтесь комментировать / исправлять / добавлять. Большое https://www.regular-expressions.info/ за обилие информации.

PS Вы упомянули обычные методы и функции VBA, и я могу подтвердить, что они (по крайней мере, для себя) были полезны по-своему там, где RegEx не сработает.

Answer 7

Мне нужно было использовать это как функцию клетки (например, 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")

Answer 8

Вот regex_subst() функция. Примеры:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Вот упрощенный код (проще для меня, во всяком случае). Я не мог понять, как построить подходящий шаблон вывода, используя приведенное выше, чтобы работать как мои примеры:

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

Answer 9

Я не хочу включать справочную библиотеку, так как мне нужно, чтобы мои сценарии были переносимыми. Dim foo As New VBScript_RegExp_55.RegExp линия вызвана User Defined Type Not Defined ошибки, но я нашел решение, которое работало для меня.

Что вы хотите сделать, это поместить пример строки в ячейку A1, тогда проверь strPattern, Как только это работает, отрегулируйте тогда rng по желанию.

Public Sub RegExSearch()
'https://stackru.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www.vitoshacademy.com/vba-regex-in-excel/
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String

    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")

    For Each rcell In rng.Cells

        strPattern = "([a-z]{2})([0-9]{8})"
        'Search for 2 ## then 8 Digits Eg: XY12345678 = Matched

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

            With regexp
                .Global = False
                .MultiLine = False
                .ignoreCase = True
                .Pattern = strPattern
            End With

            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
            Else
                MsgBox "No Matches!"
            End If
        End If
    Next
End Sub