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

Регулярные выражения 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) оставляет от него лишь

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

Регулярные выражение (Regular Expressions, RegExp) – это мощный инструмент для обработки текстовых данных, позволяющий осуществлять поиск и выполнять манипуляции с подстроками в тексте. По сути, регулярное выражение является шаблоном текста, состоящим из обычных и специальных символов. Excel не позволяет использовать всю мощь регулярных выражений на пользовательском уровне, но язык VBA, на котором создаются макросы в Excel, предоставляет такую возможность. Данный вебинар посвящен тому, как при помощи элементов языка VBA (оператора VBA Like и объекта RegExp) освоить процесс создания регулярных выражений в «Экселе».

Занятие проведёт замечательный преподаватель-практик с многосторонним опытом работы Ожиганов Сергей Иванович. Сертифицированный тренер Microsoft, обладатель статуса MCTS (Managing Projects With Microsoft Project 2013) и ряда других престижных сертификацией корпорации «Майкрософт». Виртуозно владея «Экселем», он решает реальные практические задачи, связанные со сложным анализом данных и оптимизацией работы. Участвует в разработке программного обеспечения, ежедневно оказывает услуги консалтинга по продуктам «Майрософт» и отлично понимает, какие вопросы могут возникнуть у слушателей. Сергей Иванович прекрасно умеет объяснять материал с позиции практика, не оставляя ни одной сложной темы без конкретных примеров. Среди его выпускников – специалисты крупнейших российских и международных компаний, включая ООО «МЕТРО Кэш энд Керри», ОАО «Вымпелком», ООО «Газпром-Медиа» и др.

Ближайшие группы Сортировать:

Заказ добавлен в Корзину.

Для завершения оформления, пожалуйста, перейдите в Корзину!

Анализ текста регулярными выражениями (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 и скопируйте туда текст вот такой макрофункции:

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

=RegExpExtract( Txt ; Pattern ; Item )

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

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

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

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

Регулярные выражения (по Википедии — формальный язык поиска и осуществления манипуляций с подстроками в тексте), необходимы для обработки текстов с помощью шаблонов, состоящих из символов и метасимволов, и представлены объектом 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 с поздней привязкой:

Open Notes

Обо всём, что мне интересно

Использование регулярных выражений в VBA для извлечения текста из сообщений Outlook

Есть несколько способов поиска определённого текста в теле письма с помощью VBA. Можно воспользоваться функциями InStr, Len, Left или Right, чтобы найти и извлечь текст, а можно воспользоваться регулярными выражениями. Именно о применении регулярных выражений в коде VBA пойдёт речь в данной статье.

Например, требуется извлечь код отслеживания посылки UPS, отправленной с Amazon.com. Такой код имеет формат как на скриншете ниже:

Нужно находить в тексте слова «Carrier Tracking ID», затем, возможно, пробел и двоеточие.

.Pattern = «(Carrier Tracking IDs*[:]+s*(w*)s*)»

Такое выражение извлечёт из текста из примера цифро-буквенный код 1Z2V37F8YW51233715.

Используйте s* для определения неизвестного количества пробелов (пробелы, символы табуляции, перевода строки и т.д.)
Используйте d* для определения только цифр
Используйте w* для определения цифро-буквенных символов, как используются в кодах отслеживания посылок UPS.

Чтобы использовать этот образец кода откройте редактор VBA с помощью комбинации Alt+F11. Правой кнопкой мыши нажмите на Проекте и выберите Insert > Module. Скопируйте и вставьте код макроса в модуль. Для работы макроса нужно задействовать библиотеку Microsoft VBScript Regular Expressions 5.5 в меню Tools -> References… VBA-редактора:

Если включена библиотека VBScript Expressions 1, то отключите её сняв соответствующую галочку. Невозможно одновременно использовать v1 и v5.5.

Sub GetValueUsingRegEx() ‘ Подключите библиотеку VB Script ‘ Microsoft VBScript Regular Expressions 5.5 Dim olMail As Outlook.MailItem Dim Reg1 As RegExp Dim M1 As MatchCollection Dim M As Match Set olMail = Application.ActiveExplorer().Selection(1) ‘ Debug.Print olMail.Body Set Reg1 = New RegExp ‘ s* = скрытые пробелы ‘ d* = цифры ‘ w* = цифро-буквенные выражения With Reg1 .Pattern = «Carrier Tracking IDs*[:]+s*(w*)s*» .Global = True End With If Reg1.test(olMail.Body) Then Set M1 = Reg1.Execute(olMail.Body) For Each M In M1 ‘ M.SubMatches(1) is the (w*) in the pattern ‘ use M.SubMatches(2) for the second one if you have two (w*) Debug.Print M.SubMatches(1) Next End If End Sub

Если будем искать только двоеточие .Pattern =»([:]+s*(w*)s*)» , тогда код вернёт только первое слово после двоеточия из каждой строки:

UPS
May
Standard
1Z2V37F8YW51233715
Diane

Это потому, что (w*) указывает, что нужно получить следующую после двоеточия цифро-буквенную строку, не всю строку, и не включать пробелы.

Получение двух и более значений из сообщения

Если вам требуется использовать два или несколько шаблонов, то можно повторить часть c With Reg1 до End If, для каждого из шаблонов или воспользоваться оператором Case.

Код ниже осуществляет поиск по трём шаблонам, создает строку и добавляет ее в поле темы сообщения. Каждый Case представляет свой шаблон. В этом примере отыскивается только первое вхождение каждого шаблона. .Global = False указывает, что нужно остановиться, когда находится первое совпадение.

Данные, которые мы ищем имеют следующий формат:

Order ID : VBNSA-123456
Order Date: 09 AUG 2013
Total $54.65

/n в конце шаблона соответствует концу строки, а strSubject = Replace(strSubject, Chr(13), «») удаляет любые переносы строки.

Sub GetValueUsingRegEx() Dim olMail As Outlook.MailItem Dim Reg1 As RegExp Dim M1 As MatchCollection Dim M As Match Dim strSubject As String Dim testSubject As String Set olMail = Application.ActiveExplorer().Selection(1) Set Reg1 = New RegExp For i = 1 To 3 With Reg1 Select Case i Case 1 .Pattern = «(Order IDs[:]([w-s]*)s*)n» .Global = False Case 2 .Pattern = «(Date[:]([w-s]*)s*)n» .Global = False Case 3 .Pattern = «(([d]*.[d]*))s*n» .Global = False End Select End With If Reg1.test(olMail.Body) Then Set M1 = Reg1.Execute(olMail.Body) For Each M In M1 Debug.Print M.SubMatches(1) strSubject = M.SubMatches(1) strSubject = Replace(strSubject, Chr(13), «») testSubject = testSubject & «; » & Trim(strSubject) Debug.Print i & testSubject Next End If Next i Debug.Print olMail.Subject & testSubject olMail.Subject = olMail.Subject & testSubject olMail.Save Set Reg1 = Nothing End Sub

Использование функции RegEx

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

Если вам нужно использовать более чем один шаблон с функцией, задайте для шаблон в макросе regPattern = «([0-9]<4>)» и используйте это в функции: regEx.Pattern = regPattern . Не забудьте добавить Dim regPattern As String в верхней части модуля.

Function ExtractText(Str As String) ‘ As String Dim regEx As New RegExp Dim NumMatches As MatchCollection Dim M As Match ‘этот шаблон ищет 4 подряд идущие цифры в теме письма regEx.Pattern = «([0-9]<4>)» ‘ используейте это, если нужно задвать разные шаблоны ‘ regEx.Pattern = regPattern Set NumMatches = regEx.Execute(Str) If NumMatches.Count = 0 Then ExtractText = «» Else Set M = NumMatches(0) ExtractText = M.SubMatches(0) End If code = ExtractText End Function

Этот макрос показывает, как использовать функцию Regex. Если в теме письма есть соответствующая шаблону комбинация (в пример функции, 4-х значное число), то будет создан ответ. Если нет, то появляется окно с сообщением. Чтобы использовать функцию с разными макросами, раскомментируйте строки, содержащие regPattern.