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

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

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

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

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

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

Шаг 1. Добавьте ссылку VBA на «Microsoft VBScript Regular Expressions 5.5».

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

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

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

- Диапазон.

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

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

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

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

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

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

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

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

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

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

* Сопоставьте ноль или больше шаблона, определенного перед ним. – Например, подстановочный шаблон для шаблона, который может быть или не быть. – Например, [az]* соответствует пустой строке или строке строчных букв.

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

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

| Оператор OR

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

^ NOT оператор

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

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

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

Шаблоны привязки:

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

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

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

  • Например a$ символ a$ Last должен быть строчной буквой 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  \<\/[a-zA-Z][0-9]\> Exact non-word character except any single alpha followed by any single digit 

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

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

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

  1. Текст для использования регулярного выражения.
  2. Регулярное выражение.
  3. Строка формата, определяющая, как должен выглядеть результат. Он может содержать $0 , $1 , $2 и т. Д. $0 – весь матч, $1 и up соответствуют соответствующим группам совпадений в регулярном выражении. По умолчанию используется значение $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 в библиотеку Regular Expressions (бесстыдно скопированную из ответа Portland Runners ++ ):
    1. Нажмите Инструменты -> Ссылки (пожалуйста, извините немецкий скриншот) Инструменты -/> Ссылки”></li>
<li>  Найдите в списке <em>Microsoft VBScript Regular Expressions 5.5</em> и установите флажок рядом с ним. </li>
<li>  Нажмите « <em>ОК»</em> . </li>
</ol>
</li>
<li>
<p>  Нажмите « <em>Вставить модуль»</em> .  Если вы укажете вашему модулю другое имя, убедитесь, что модуль <em>не</em> имеет того же имени, что и UDF ниже (например, имя модуля <code>Regex</code> и функция <code>regex</code> <em>приводят к</em> ошибкам <em>#NAME!</em> ). </p>
<p><img src= 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
    2. Сохраните и закройте окно Microsoft Visual Basic для редактора приложений .

Расширяясь на ответ патзим для тех, кто спешит.

  1. Откройте книгу Excel.
  2. Alt + F11, чтобы открыть окно VBA / Macros.
  3. Добавьте ссылку на регулярное выражение в разделе Инструменты, затем Ссылки
    ! [Excel VBA Form добавить ссылки
  4. и выбор Microsoft VBScript Regular Expression 5.5
    ! [Excel VBA добавить ссылку regex
  5. Вставьте новый модуль (код должен находиться в модуле, иначе он не будет работать).
    ! [Модуль ввода кода Excel VBA
  6. Во вновь вставленном модуле,
    ! [Excel VBA вставить код в модуль
  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 в ячейке Функция будет привязана к рабочей книге, которую она создала.
    Если необходимо, чтобы он использовался в разных книгах, сохраните функцию в Personal.XLSB

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

 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 

Вот 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 

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

  1. Убедитесь, что вы находитесь в Macro Enabled Excel File (сохранить как xlsm).
  2. Открытые инструменты разработчика Alt + F11
  3. Добавьте регулярные выражения Microsoft VBScript 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»)

  • Создать форму timestamp для Excel
  • Как вы снимаете защиту макросов VBA для книги с поддержкой макросов, если вы забыли пароль?
  • Как сделать переменные Excel VBA доступными для нескольких макросов?
  • Vba Excel делает vlookup из закрытого файла
  • Дождитесь завершения команды оболочки
  • Разбор нескольких ячеек и значений из одного запроса JSON
  • Перегрузка функций и UDF в Excel VBA
  • Неинтерфейс SQL Injection
  • Обновить результаты функции Excel VBA
  • Excel VBA для ответа на приглашение загрузки Internet Explorer 11 в Windows 10?
  • В Excel VBA, как мне сохранить / восстановить определяемый пользователем фильтр?
  • Давайте будем гением компьютера.