Регулярные выражения excel

VBA Excel. Регулярные выражения (объекты, свойства, методы)

Регулярные выражения в VBA Excel. Объекты RegExp, Match, Matches Collection и их свойства. Символы и метасимволы. Создание объекта RegExp с ранней, поздней привязкой и его методы.

В VBA Excel для работы с регулярными выражениями используется библиотека «Microsoft VBScript Regular Expression».

Создание объекта RegExp

Ранняя привязка

Обычно рекомендуется использовать объекты с ранней привязкой, так как у них выше быстродействие, а также при написании и редактировании кода доступны подсказки в виде листа свойств-методов, появляющегося автоматически или вызываемого, при необходимости, сочетанием клавиш Ctrl+Пробел.

Раннее связывание заключается в присвоении нового экземпляра объекта RegExp переменной, уже объявленной, как переменная определенного типа (в нашем случае, как RegExp).

Для осуществления ранней привязки необходимо подключить к проекту VBA ссылку на библиотеку «Microsoft VBScript Regular Expression», для чего в редакторе VBA выбираем Tools – References…

В открывшемся окне «References» находим строку «Microsoft VBScript Regular Expression 5.5» (если у вас ее нет, то строку «Microsoft VBScript Regular Expression 1.0»), отмечаем ее галочкой и нажимаем «ОК».

Готово – ссылка добавлена.

Создание объекта RegExp с ранней привязкой:

Поздняя привязка

Позднее связывание заключается в присвоении нового экземпляра объекта RegExp переменной, объявленной как Object, с помощью функции CreateObject.

Создание объекта RegExp с поздней привязкой:

Свойства и методы объекта RegExp

Свойства объекта RegExp

Свойства объекта RegExp доступны для чтения и записи.

Методы объекта RegExp

Свойства объектов Match и Matches Collection

Метод Execute объекта RegExp возвращает агрегатный объект Matches Collection, который содержит коллекцию объектов Match, представляющих все совпадения, найденные механизмом регулярных выражений, в том порядке, в котором они присутствуют в исходной строке. Если совпадений нет, метод возвращает объект Matches Collection без членов.

Свойства объекта Matches Collection

Свойства объекта Matches Collection доступны только для чтения.

Свойства объекта Match

Свойства объекта Match доступны только для чтения.

Символы и метасимволы

Все знаки, используемые для составления шаблонов, обычно делят на символы и метасимволы. Символы – это знаки, которые в шаблонах обозначают сами себя, а метасимволы (спецсимволы) – знаки, имеющие другое значение. Метасимволы могут использоваться как отдельно, так и в сочетании с другими символами и спецсимволами.
Таблица основных метасимволов и их сочетаний с другими символами

В таблицу не включены редко используемые сочетания, ознакомиться с которыми можно в справочной системе разработчика. А примеры использования метасимволов в шаблонах очень хорошо представлены на этом ресурсе в разделе 4. Метасимволы.

Источник: vremya-ne-zhdet.ru

Регулярные выражения в Excel

Многие слышали, что такое регулярные выражения, но не всем известно, что они поддерживаются «под капотом» Microsoft Excel. Регулярные выражения дают возможность многократно ускорить работу с текстом, находить в нем самые замысловатые паттерны и решать самые сложные исследовательские задачи. Единственная проблема в том, что для их использования в Excel необходимо знание VBA.

Почему Microsoft не включила их как функции листа и включит ли когда-нибудь, непонятно и неизвестно.

Но с надстройкой !SEMTools эти знания не нужны. Зато минимальное понимание синтаксиса регулярок позволит с легкостью решать задачи, решение которых практически невозможно с помощью стандартных функций, либо требуются формулы огромной длины. Примеры таких мегаформул можно посмотреть в решении задач:

Функции регулярных выражений в Excel

Для поддержки регулярных выражений при наличии подключенной надстройки !SEMTools в Excel будут работать 3 функции — REGEXMATCH, REGEXEXTRACT и REGEXREPLACE.

Их синтаксис и принцип работы аналогичен синтаксису Google Spreadsheets. Поэтому формулы, составленные в Excel, будут иметь полную зеркальную совместимость с Google Spreadsheets.

REGEXMATCH возвращает Истина или Ложь (TRUE или FALSE в английской версии Excel), в зависимости от того, соответствует текст паттерну или нет.

REGEXEXTRACT извлекает первый попадающий под паттерн фрагмент текста. Небольшое отличие от Google Spreadsheets — если в искомом тексте такого фрагмента нет, Spreadsheets отдают ошибку, а в надстройке отдается пустая строка.

Примеры задач, решаемых с помощью регулярных выражений

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

Чтобы дать обычным пользователям Excel возможность на полную мощность использовать возможности регулярных выражений, в надстройку !SEMTools был добавлен ряд быстрых процедур. Все примеры ниже будут показаны с их использованием.

Извлечение данных из ячеек с помощью RegEx

Извлечь из ячейки содержимое до / после первой цифры включительно

Такие простые два выражения. «+» — это служебный символ-квантификатор. Он обеспечивает «жадный» режим, при котором берутся все удовлетворяющие выражению символы до тех пор, пока на пути не встретится не удовлетворяющий ему, или конецначало строки. Точка обозначает любой символ, таким образом, берутся любые символы до конца строки, перед которыми есть цифра.

«d». d обозначает «digits», иначе, цифры. Поскольку квантификатора после d в примерах выше нет, то одну. Если потребуется исключить из результатов эту цифру, это можно сделать позднее. В !SEMTools есть простые способы удалить символы в начале или конце ячейки.

Цифры можно выразить и другим регулярным выражением:

«Вытянуть» цифры из ячеек

Как извлечь из строки цифры? Регулярное выражение для такой операции будет безумно простым:

В зависимости от режима извлечения, результатом будет либо первая, либо все цифры в ячейке.

Если их нужно вывести не сплошной последовательностью, а через разделитель, сохранив фрагменты, где символы следовали друг за другом, выражение будет чуть иным, с «жадным» квантификатором. А при извлечении нужно будет использовать разделитель.

Это справедливо и для любых других символов, пример с числами ниже:

Извлечь из ячейки числа из N цифр

Как видно в примере выше, помимо чисел, обозначающих годы, были извлечены и другие числа, например, 1. Чтобы извлечь исключительно последовательности из 4 цифр, потребуется видоизменить выражение. Есть несколько вариантов:

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

Извлекаем все 4-цифровые последовательности (год) через разделитель

Проверить ячейки на соответствие регулярному выражению

Если нет необходимости извлекать данные, а нужно лишь проверить, соответствуют ли они паттерну, чтобы потом отфильтровать их, удобнее использовать процедуру, эквивалентную формуле REGEXMATCH.

Найти в ячейке числа из N цифр

В зависимости от того, является N необходимым или достаточным условием, нужны разные регулярные выражения. Иными словами, считать ли последовательности из N+1, N+2 и т.д. цифр подходящими или нет. Если да — выражение будет таким же, как уже указывалось выше:

Если же нас интересуют строго последовательности из N цифр, задачу придется производить в 2 итерации:

• В первую итерацию извлекать цифры вместе с границами строк или нецифровыми символами, идущими после/перед (это станет своеобразной проверкой отсутствия других цифр)
• И во вторую уже сами цифры.

Выражения для первой итерации будут, соответственно:

Если внимательно посмотреть на отличие в синтаксисе, можно понять, что означают символы в нем:

• вертикальная черта «|» обозначает «ИЛИ»
• скобки нужны для перечисления внутри них аргументов и «отгораживания» их от остального выражения
• каретка «^» обозначает начало строки
• символ доллара «$» — конец строки
• D — нечисловые символы. Обратите внимание, верхний регистр меняет значение d на противоположное. Это справедливо также для пар w и W, s и S, обозначающих латиницу и не-латиницу, пробелы и не-пробелы соответственно.

Разбить ячейку по буквам

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

Извлечь латиницу регулярным выражением

Выражение «[a-zA-Z]» обозначает все символы латиницы. Дефис и в этом, и в предыдущем случае обозначает, что берутся все символы между a и z и между A и Z в общей таблице символов Unicode. Квадратные скобки — синоним «ИЛИ». Каждый из элементов или множеств внутри квадратных скобок рассматривается, и выражение не находит ничего, только если сравниваемая строка не содержит ни одного элемента внутри квадратных скобок.

Извлекаем латиницу с помощью регулярных выражений

Извлечь символы в конце/начале строк по условию

Стандартные формулы ПРАВСИМВ и ЛЕВСИМВ позволяют извлечь из ячейки соответственно последние и первые N символов, но на этом их возможности заканчиваются.

С помощью регулярных выражений можно извлечь:

• символы, идущие после и включая последнюю заглавную букву в ячейке, заканчивающейся на восклицательный знак. Так мы извлечем из ячеек все восклицательные предложения. Выражение — «[А-Я][а-яa-z0-9 ]+!$»
• первые N выбранных символов из определенного множества, если ячейка с них начинается
• аналогично, последние N определенных символов, если ячейка на них заканчивается

Замена подстрок по регулярному выражению

Наиболее частый кейс такой замены — замена на пустоту, когда наша задача попросту удалить из текста определенные символы. Наиболее популярны:

• удаление цифр из текста
• удаление пунктуации
• всех символов, кроме букв и цифр

Но бывают случаи, когда необходима реальная замена — например, когда нужно заменить буквы с хвостиками/умляутами/ударениями и прочими символами из европейских алфавитов на их английские аналоги. Задача популярна среди SEO-специалистов, формирующих урлы сайтов этих стран на основе оригинальной семантики. Так выглядит начало таблицы паттернов для замены диакритических символов на латиницу с помощью RegEx при генерации URL:

Диакритические символы и их английские эквиваленты

Разбить буквы и цифры в ячейке

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

А так будет выглядеть процесс на практике:

Разбиваем текст на цифры и нецифровые символы (буквы и знаки препинания) с помощью регулярного выражения

Вставить текст после первого слова

При замене по регулярному выражению в !SEMTools есть опция замены не всех, а только первого найденного фрагмента, удовлетворяющего паттерну. Это позволяет решить задачу вставки символов после первого слова. Просто заменим первый пробел на нужные нам символы с помощью соответствующей процедуры:

Регулярные выражения для поиска конкретных слов в !SEMTools

Найти слова по регулярному выражению

Извлечь слова по регулярному выражению

Удалить слова по регулярному выражению

Очистить ячейки, не соответствующие регулярному выражению

Когда в вашем распоряжении массив данных, в котором могут быть ошибки, с которыми разбираться некогда, и нужно извлечь только 100% подходящие данные, можно воспользоваться регулярными выражениями для очистки нерелевантных.

• оставить ячейки с определенным количеством слов
• оставить ячейки с определенным количеством символов
• оставить ячейки, содержащие только цифры
• оставить ячейки, содержащие только буквы
• оставить ячейки, содержащие адрес электронной почты в доменной зоне .com и .ru

Примеры использования «Извлечь ячейки по регулярному выражению».

Источник: semtools.guru

Регулярные выражения VBA

Заметка написана Андреем Макаренко

Регулярные выражения (regular expressions) — очень мощный механизм для обработки строк. С его помощью можно найти нужные части текста, проверить, удовлетворяет ли строка определённой маске, заменить найденный текст. Такие выражения вcтроены во многие языки программирования, такие, как Perl, Php, JavaScript, и, конечно VBA.

Рассмотрим, как это работает на примере обработки счетов на доставку товаров. Исходные данные содержатся в отчете Excel в форме полного адреса (рис. 1). Наша задача — из строки вида «677000, Россия, Саха /Якутия/ Респ., г. Якутск, ул. Ойунского» выделить название города.

Рис. 1. Исходные данные

Скачать заметку в формате Word или pdf, примеры в архиве (политика провайдера не позволяет напрямую загружать на сайт файлы Excel, содержащие макросы)

Для использования регулярных выражений, необходимо подключить библиотеку MS Windows Script. Для подключения, запускаем VBA (меню Разработчик –> Visual Basic). В открывшемся окне VBA проходим по меню Tools –> References и в окне References — VBAProject ставим флажок в строке Microsoft VBScript Regular Expressions 5.5 (рис. 2).

Рис. 2. Подключение библиотеки MS Windows Script для работы с регулярными выражениями

Теперь в Visual Basic добавился объект RegExp, который содержит в себе всё, что нужно для работы с регулярными выражениями. Код, выполняющий поставленную задачу, выглядит так:

Sub RegExp()
Dim myRegExp As New RegExp ‘ создаем экземпляр RegExp
Dim aMatch As Match ‘ один из совпавших образцов
Dim colMatches As MatchCollection ‘ коллекция этих образцов
Dim strTest As String ‘ тестируемая строка

‘ устанавливаем свойства объекта RegExp
myRegExp.Global = False ‘ если Global = True, то поиск ведётся во всей строке, _
если False, то только до первого совпадения
myRegExp.IgnoreCase = True ‘ игнорировать регистр символов при поиске
myRegExp.Pattern = ” , (г|c|п). .*?, ” ‘ шаблон для поиска

strTest = Sheets( ” Накладные ” ).Range( ” E2 ” ).Text ‘ присваиваем переменной текст из текущей ячейки
Set colMatches = myRegExp.Execute(strTest) ‘ получаем коллекцию совпадений с образцом

‘ перебираем коллекцию и просматриваем результаты
For Each aMatch In colMatches ‘ проходим по всей коллекции
a = aMatch.FirstIndex ‘ порядковый номер первого символа найденного образца
b = aMatch.Length ‘ кол-во символов в найденном образце
c = aMatch.Value ‘ полный образец
Next aMatch

c = Mid(c, 6, Len(c) — 6)
MsgBox a & ” | ” & b & ” | ” & c ‘ смотрим, что получилось

‘ производим замену найденного выражения
d = myRegExp.Replace(strTest, ” (здесь раньше был город) ” )

MsgBox d ‘ смотрим, что получилось
End Sub

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

Отдельного рассмотрения заслуживает синтаксис создания шаблона для поиска. Возможности его поистине неисчерпаемы. Несколько лет назад я работал в издательстве телегида. Еженедельно требовалось в ограниченные сроки подготовить тексты телепрограмм для публикации. В приведении их к единому стандарту были заняты три редактора и два верстальщика в течение нескольких часов. Программная обработка с использованием регулярных выражений занимала несколько минут после чего один корректор и один верстальщик «подчищали» результат в течение получаса. Реальная проблема, с которой мне пришлось столкнуться, это потеря предсказуемости результата при построении слишком сложных конструкций. Поэтому рекомендую вместо одной сложной использовать несколько последовательных трансформаций.

Описание синтаксиса для создания шаблона можно найти в Интернете (см., например, msdn). Здесь я дам лишь краткое представление о возможностях (рис. 3) и прокомментирую шаблон, использованный в примере.

Рис. 3. Специальные символы

Разберем использованный нами в примере шаблон:

Сам шаблон берется в кавычки. Начинается шаблон с запятой и пробела, далее следует конструкция (г|c|п), которая позволят выбрать один из трех вариантов обозначения населенного пункта: г – город, с – село, п – поселок.

. — это просто точка. Обратная косая поставлена для того, чтобы отличить ее от спецсимвола

пробел — это и есть пробел

. — точка — любой символ

* — множитель, берет ни одного или множество символов, стоящих слева от него (а это точка – то есть, любой символ) между пробелом и запятой

? — ограничивает поиск первой встреченной запятой. Если его не использовать, то выражение вернет значение ” , г. Якутск, ул. Ойунского, ” . Т.е., до последней запятой в тексте.

Итак, шаблон позволяет найти текст, который начинается с запятой, пробела, буквы («г», «с» или «п») с точкой и пробела, а заканчивается первой встреченной после этого запятой. В нашем случае шаблон извлекает следующий текст:

Далее оператор c = Mid(c, 6, Len(c) — 6) оставляет от него лишь

Источник: baguzin.ru

Регулярные выражения excel

Анализ текста регулярными выражениями (RegExp) в Excel

Одной из самых трудоемких и неприятных задач при работе с текстом в Excel является парсинг – разбор буквенно-цифровой “каши” на составляющие и извлечение из нее нужных нам фрагментов. Например:

• извлечение почтового индекса из адреса (хорошо, если индекс всегда в начале, а если нет?)
• нахождение номера и даты счета из описания платежа в банковской выписке
• извлечение ИНН из разношерстных описаний компаний в списке контрагентов
• поиск номера автомобиля или артикула товара в описании и т.д.

Обычно во подобных случаях, после получасового муторного ковыряния в тексте вручную, в голову начинают приходить мысли как-то автоматизировать этот процесс (особенно если данных много). Решений тут несколько и с разной степенью сложности-эффективности:

• Использовать встроенные текстовые функции Excel для поиска-нарезки-склейки текста: ЛЕВСИМВ (LEFT) , ПРАВСИМВ (RIGHT) , ПСТР (MID) , СЦЕПИТЬ(CONCATENATE)и ее аналоги , ОБЪЕДИНИТЬ (JOINTEXT) , СОВПАД(EXACT) и т.д. Этот способ хорош, если в тексте есть четкая логика (например, индекс всегда в начале адреса). В противном случае формулы существенно усложняются и, порой, дело доходит даже до формул массива, что сильно тормозит на больших таблицах.
• Использование оператора проверки текстового подобия Like из Visual Basic, обернутого в пользовательскую макро-функцию. Это позволяет реализовать более гибкий поиск с использованием символов подстановки (*,#,? и т.д.) К сожалению, этот инструмент не умеет извлекать нужную подстроку из текста – только проверять, содержится ли она в нем.

Кроме вышеперечисленного, есть еще один подход, очень известный в узких кругах профессиональных программистов, веб-разработчиков и прочих технарей – это регулярные выражения (Regular Expressions = RegExp = “регэкспы” = “регулярки”). Упрощенно говоря, RegExp – это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст . Регулярные выражения – это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом. Многие языки программирования (C#, PHP, Perl, JavaScript. ) и текстовые редакторы (Word, Notepad++. ) поддерживают регулярные выражения.

Microsoft Excel, к сожалению, не имеет поддержки RegExp по-умолчанию “из коробки”, но это легко исправить с помощью VBA. Откройте редактор Visual Basic с вкладки Разработчик (Developer) или сочетанием клавиш Alt+F11. Затем вставьте новый модуль через меню Insert – Module и скопируйте туда текст вот такой макрофункции:

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String

On Error GoTo ErrHandl

Set regex = CreateObject(“VBScript.RegExp”)

If regex.Test(Text) Then

Set matches = regex.Execute(Text)

RegExpExtract = matches.Item(Item – 1)

Теперь можно закрыть редактор Visual Basic и, вернувшись в Excel, опробовать нашу новую функцию. Синтаксис у нее следующий:

=RegExpExtract( Txt ; Pattern ; Item )

• Txt – ячейка с текстом, который мы проверяем и из которого хотим извлечь нужную нам подстроку
• Pattern – маска (шаблон) для поиска подстроки
• Item – порядковый номер подстроки, которую надо извлечь, если их несколько (если не указан, то выводится первое вхождение)

Самое интересное тут, конечно, это Pattern – строка-шаблон из спецсимволов “на языке” RegExp, которая и задает, что именно и где мы хотим найти. Вот самые основные из них – для начала:

Самое простое – это точка. Она обозначает любой символ в шаблоне на указанной позиции.

Любой символ, выглядящий как пробел (пробел, табуляция или перенос строки).

Анти-вариант предыдущего шаблона, т.е. любой НЕпробельный символ.

Анти-вариант предыдущего, т.е. любая НЕ цифра

Любой символ латиницы (A-Z), цифра или знак подчеркивания

Анти-вариант предыдущего, т.е. не латиница, не цифра и не подчеркивание.

В квадратных скобках можно указать один или несколько символов, разрешенных на указанной позиции в тексте. Например ст[уо]л будет соответствовать любому из слов: стол или стул .
Также можно не перечислять символы, а задать их диапазоном через дефис, т.е. вместо [ABDCDEF] написать [A-F] . или вместо [4567] ввести [4-7] . Например, для обозначения всех символов кириллицы можно использовать шаблон [а-яА-ЯёЁ] .

Если после открывающей квадратной скобки добавить символ “крышки” ^ , то набор приобретет обратный смысл – на указанной позиции в тексте будут разрешены все символы, кроме перечисленных. Так, шаблон [^ЖМ]уть найдет Путь или Суть или Забудь , но не Жуть или Муть , например.

Логический оператор ИЛИ (OR) для проверки по любому из указанных критериев. Например (счет|счёт|invoice) будет искать в тексте любое из указанных слов. Обычно набор вариантов заключается в скобки.

Если мы ищем определенное количество символов, например, шестизначный почтовый индекс или все трехбуквенные коды товаров, то на помощь нам приходят квантификаторы или кванторы – специальные выражения, задающие количество искомых знаков. Квантификаторы применяются к тому символу, что стоит перед ним:

Ноль или одно вхождение. Например .? будет означать один любой символ или его отсутствие.

Одно или более вхождений. Например d+ означает любое количество цифр (т.е. любое число от 0 до бесконечности).

Ноль или более вхождений, т.е. любое количество. Так s* означает любое количество пробелов или их отсутствие.

Если нужно задать строго определенное количество вхождений, то оно задается в фигурных скобках. Например d <6>означает строго шесть цифр, а шаблон s <2,5>– от двух до пяти пробелов

Теперь давайте перейдем к самому интересному – разбору применения созданной функции и того, что узнали о паттернах на практических примерах из жизни.

Источник: webhamster.ru

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

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

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

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

vba excel-vba regex excel

6 ответов

752 Решение Portland Runner [2014-03-20 22:09:00]

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

Для использования в 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

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

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

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

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

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

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

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

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

Пример 1. Выполнение макроса

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

Пример 2. Выполнение функции в ячейке

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

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

Пример 3: Диапазон прохождения циклы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• В Excel в файле с поддержкой макроса (.xlsm) нажмите ALT+F11 , чтобы открыть редактор Microsoft Visual Basic для приложений.
• Добавьте ссылку VBA в библиотеку регулярных выражений (бесстыдно скопированную из ответ Portland Runners ++):
  • Нажмите Инструменты → Ссылки (пожалуйста, извините немецкий скриншот) References”>
  • Найдите в списке Microsoft VBScript Regular Expressions 5.5 и установите галочку рядом с ним.
  • Нажмите “ОК”.

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

  Module”>

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

  Сохраните и закройте окно Microsoft Visual Basic для редактора приложений.

  34 SAm [2017-03-31 00:28:00]

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

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

  добавьте следующий код:

  Шаблон регулярного выражения помещается в одну из ячеек, и на нем используется абсолютная ссылка. Функция будет привязана к книге, которую она создала.
  Если есть необходимость, чтобы он использовался в разных книгах, сохраните функцию в Personal.XLSB

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

  1 jgreve [2017-07-29 00:19:00]

  Вот regex_subst() . Примеры:

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

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

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

  Создайте следующую функцию в книге или в ее собственном модуле:

  Затем вы можете использовать в ячейке с =REGPLACE(B1, “(w) (d+)”, “$1$2”) (например: “A 243” – “A243”)

  Источник: qarchive.ru

  Analyst Cave

  

  Excel Regex Tutorial (Regular Expressions)

  Regular expressions are ubiquitous in the developer world. They are used to validate website input, search for word patterns in large strings/texts and for many other uses. In Excel, Regular Expressions (VBA RegEx or simply VBA Regex) are not much advertised. Most users are good with using simple LEFT, RIGHT, MID and FIND functions for their string manipulation. These functions are, however, quite primitive and limited. Knowing how to use Regular Expressions (Regex) in Excel will save you a lot of time. This Excel Regex Tutorial focuses both on using Regex functions and in VBA. Let’s, however, not forget that VBA has also adopted the VBA Like operator which sometimes allows you to achieve some tasks reserved for Regular Expressions.

  Excel Regex example

  A Regex (Regular Expression) is basically a pattern matching strings within other strings. Let’s demonstrate this with a simple Regex example. Let us assume we have the text below. And we want to capture just the numbers. Without knowing ahead how the text looks like it would be hard to extract these numbers both using Excel Functions and VBA.
  But with a Regex we can extract the numbers with a simple pattern:

  What does the [0-9]+ pattern represent? It translates to the following: capture any pattern matching the following range of characters ([ ]), being numbers from 0-9, in a sequence of at least 1 or more (+). As you can see a Regex uses a certain code to translate your pattern.

  Regular Expression Language

  The Regular Expression language (Regex) is quite elaborate but allows you to match virtually any regular language. Below a quick reference:

  Matching characters

  Syntax	Description	Example	Example match
  .	Any character except newline (vbNewLine)	d.g	“dog” in “My dog is named dingo”
  [characters]	Matches any provided character between brackets [ ]	[af]	“a” , “f” in “alfa”
  [^characters]	Matches any character not being one of the provided between brackets [ ]	[^af]	“a” , “f” in “alfa”
  [start–end]	Matches any character belonging to the character range specified between brackets [ ]	[0-9]	“1” and “2” in “12”
  w	Any word character (letters, modifiers, digits, punctuation and connectors)	w	“I”, “a” “m” “J” “o” “h” “n” in “I_am.John”
  W	Any non-word character	w	“_” and “.” in “I_am.John”
  s	Any white space character	s	” ” in “Hi There!”
  S	Any non-white space character	S	“M” and “e” in “M e”
  d	Any decimal digit	d	“1” and “2” in “12”
  D	Any non-decimal digit	D	“d”, “_”, “.” in “d_.”
  	Followed by any special character – escapes special characters	.	“.” im “d.g”
  r	Tab (vbTab)	r
  n	Carriage return / new line (vbNewLine)

  Quantifiers

  Quantifiers allow you to specify the amount of times a certain pattern is supposed to matched against a string. It is important to understand the difference between GREEDY and non-GREEDY quantifiers:
  Greedy vs Non-Greedy Regular Expression

  Syntax	Description	Example	Example match
  *	Zero or more of (GREEDY). Matches as many as possible	W.*W	“_dogs_cats_” in “_dogs_cats_”
  +	One or more of (GREEDY). Matches as many as possible	Ww+W	“_dogs_cats_” in “_dogs_cats_”
  ?	Zero or once (GREEDY). Matches as many as possible	d?	“1” in “Live1”
  	“n” many times	d	“21” and “12” in “212”
  	At least “n” times (GREEDY)	d	“12” and “123” in “1_12_123”
  	Between “n” and “m” times (GREEDY)	d	“123” and “1234” in “1_12_123_1234”
  *?	Zero or more of (non-GREEDY). Matches as few as possible	W.*?W	“_dogs_” and “_cats_” in “_dogs_cats_”
  +?	One or more of (non-GREEDY). Matches as few as possible	W.+?W	“_dogs_” and “_cats_” in “_dogs_cats_”
  ??	Zero or once (non-GREEDY). Matches as few as possible	d??	“1” in “Live1”
  ?	At least “n” times (non-GREEDY). Matches as few as possible	d	“12” and “123” in “1_12_123”
  ?	Between “n” and “m” times (non-GREEDY). Matches as few as possible	d	“123” and “1234” in “1_12_123_1234”

  Grouping

  Below the basic grouping expressions:

  Syntax	Description	Example	Example match
  (expression)	Group and capture the expression within the parenthesis ( )	([0-9]*)	Captures “123, “345” and “789” within “123-456-789”
  (?:expression)	Group BUT DON’T CAPTURE the expression within the parenthesis ( )	(?:[0-9]*)([A-Z]*)(?:[0-9]*)	Captures only “hello” in “123hello456”

  Using Regex in VBA

  To use Regex in VBA you need to use the RegExp object which is defined in the Microsoft VBScript Regular Expressions library. To start using this object add the following reference to your VBA Project: Tools -> References -> Microsoft VBScript Regular Expressions . Otherwise, if you don’t want to reference this library every time you can also create the RegExp object using the CreateObject function.

  Option 1: Referencing the library Microsoft VBScript Regular Expressions

  Источник: analystcave.com