Анализ текста регулярными выражениями (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")
regex.Pattern = Pattern
regex.Global = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
RegExpExtract = matches.Item(Item - 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function
Теперь можно закрыть редактор Visual Basic и, вернувшись в Excel, опробовать нашу новую функцию. Синтаксис у нее следующий:
=RegExpExtract( Txt ; Pattern ; Item )
где
- Txt - ячейка с текстом, который мы проверяем и из которого хотим извлечь нужную нам подстроку
- Pattern - маска (шаблон) для поиска подстроки
- Item - порядковый номер подстроки, которую надо извлечь, если их несколько (если не указан, то выводится первое вхождение)
Самое интересное тут, конечно, это Pattern - строка-шаблон из спецсимволов "на языке" RegExp, которая и задает, что именно и где мы хотим найти. Вот самые основные из них - для начала:
| Паттерн | Описание |
| . |
Самое простое - это точка. Она обозначает любой символ в шаблоне на указанной позиции. |
| \s |
Любой символ, выглядящий как пробел (пробел, табуляция или перенос строки). |
|
\S |
Анти-вариант предыдущего шаблона, т.е. любой НЕпробельный символ. |
|
\d |
Любая цифра |
|
\D |
Анти-вариант предыдущего, т.е. любая НЕ цифра |
| \w |
Любой символ латиницы (A-Z), цифра или знак подчеркивания |
| \W |
Анти-вариант предыдущего, т.е. не латиница, не цифра и не подчеркивание. |
| [символы] |
В квадратных скобках можно указать один или несколько символов, разрешенных на указанной позиции в тексте. Например ст[уо]л будет соответствовать любому из слов: стол или стул. Также можно не перечислять символы, а задать их диапазоном через дефис, т.е. вместо [ABDCDEF] написать [A-F]. или вместо [4567] ввести [4-7]. Например, для обозначения всех символов кириллицы можно использовать шаблон [а-яА-ЯёЁ]. |
| [^символы] |
Если после открывающей квадратной скобки добавить символ "крышки" ^, то набор приобретет обратный смысл - на указанной позиции в тексте будут разрешены все символы, кроме перечисленных. Так, шаблон [^ЖМ]уть найдет Путь или Суть или Забудь, но не Жуть или Муть, например. |
| | |
Логический оператор ИЛИ (OR) для проверки по любому из указанных критериев. Например (счет|счёт|invoice) будет искать в тексте любое из указанных слов. Обычно набор вариантов заключается в скобки. |
| ^ |
Начало строки |
| $ |
Конец строки |
| \b |
Край слова |
Если мы ищем определенное количество символов, например, шестизначный почтовый индекс или все трехбуквенные коды товаров, то на помощь нам приходят квантификаторы или кванторы - специальные выражения, задающие количество искомых знаков. Квантификаторы применяются к тому символу, что стоит перед ним:
| Квантор | Описание |
| ? |
Ноль или одно вхождение. Например .? будет означать один любой символ или его отсутствие. |
| + |
Одно или более вхождений. Например \d+ означает любое количество цифр (т.е. любое число от 0 до бесконечности). |
| * |
Ноль или более вхождений, т.е. любое количество. Так \s* означает любое количество пробелов или их отсутствие. |
|
{число} или {число1,число2} |
Если нужно задать строго определенное количество вхождений, то оно задается в фигурных скобках. Например \d{6} означает строго шесть цифр, а шаблон \s{2,5} - от двух до пяти пробелов |
Теперь давайте перейдем к самому интересному - разбору применения созданной функции и того, что узнали о паттернах на практических примерах из жизни.
Извлекаем числа из текста
Для начала разберем простой случай - нужно извлечь из буквенно-цифровой каши первое число, например мощность источников бесперебойного питания из прайс-листа:
Логика работы регулярного выражения тут простая: \d - означает любую цифру, а квантор + говорит о том, что их количество должно быть одна или больше. Двойной минус перед функцией нужен, чтобы "на лету" преобразовать извлеченные символы в полноценное число из числа-как-текст.
Почтовый индекс
На первый взгляд, тут все просто - ищем ровно шесть цифр подряд. Используем спецсимвол \d для цифры и квантор {6} для количества знаков:
Однако, возможна ситуация, когда левее индекса в строке стоит еще один большой набор цифр подряд (номер телефона, ИНН, банковский счет и т.д.) Тогда наша регулярка выдернет из нее первых 6 цифр, т.е. сработает некорректно:
Чтобы этого не происходило, необходимо добавить в наше регулярное выражение по краям модификатор \b означающий конец слова. Это даст понять Excel, что нужный нам фрагмент (индекс) должен быть отдельным словом, а не частью другого фрагмента (номера телефона):
Телефон
Проблема с нахождением телефонного номера среди текста состоит в том, что существует очень много вариантов записи номеров - с дефисами и без, через пробелы, с кодом региона в скобках или без и т.д. Поэтому, на мой взгляд, проще сначала вычистить из исходного текста все эти символы с помощью нескольких вложенных друг в друга функций ПОДСТАВИТЬ (SUBSTITUTE), чтобы он склеился в единое целое, а потом уже примитивной регуляркой \d{11} вытаскивать 11 цифр подряд:
ИНН
Тут чуть сложнее, т.к. ИНН (в России) бывает 10-значный (у юрлиц) или 12-значный (у физлиц). Если не придираться особо, то вполне можно удовлетвориться регуляркой \d{10,12}, но она, строго говоря, будет вытаскивать все числа от 10 до 12 знаков, т.е. и ошибочно введенные 11-значные. Правильнее будет использовать два шаблона, связанных логическим ИЛИ оператором | (вертикальная черта):
Обратите внимание, что в запросе мы сначала ищем 12-разрядные, и только потом 10-разрядные числа. Если же записать нашу регулярку наоборот, то она будет вытаскивать для всех, даже длинных 12-разрядных ИНН, только первые 10 символов. То есть после срабатывания первого условия дальнейшая проверка уже не производится:
Это принципиальное отличие оператора | от стандартной экселевской логической функции ИЛИ (OR), где от перестановки аргументов результат не меняется.
Артикулы товаров
Во многих компаниях товарам и услугам присваиваются уникальные идентификаторы - артикулы, SAP-коды, SKU и т.д. Если в их обозначениях есть логика, то их можно легко вытаскивать из любого текста с помощью регулярных выражений. Например, если мы знаем, что наши артикулы всегда состоят из трех заглавных английских букв, дефиса и последующего трехразрядного числа, то:
Логика работы шаблона тут проста. [A-Z] - означает любые заглавные буквы латиницы. Следующий за ним квантор {3} говорит о том, что нам важно, чтобы таких букв было именно три. После дефиса мы ждем три цифровых разряда, поэтому добавляем на конце \d{3}
Денежные суммы
Похожим на предыдущий пункт образом, можно вытаскивать и цены (стоимости, НДС...) из описания товаров. Если денежные суммы, например, указываются через дефис, то:
Паттерн \d с квантором + ищет любое число до дефиса, а \d{2} будет искать копейки (два разряда) после.
Если нужно вытащить не цены, а НДС, то можно воспользоваться третьим необязательным аргументом нашей функции RegExpExtract, задающим порядковый номер извлекаемого элемента. И, само-собой, можно заменить функцией ПОДСТАВИТЬ (SUBSTITUTE) в результатах дефис на стандартный десятичный разделитель и добавить двойной минус в начале, чтобы Excel интерпретировал найденный НДС как нормальное число:
Автомобильные номера
Если не брать спецтранспорт, прицепы и прочие мотоциклы, то стандартный российский автомобильный номер разбирается по принципу "буква - три цифры - две буквы - код региона". Причем код региона может быть 2- или 3-значным, а в качестве букв применяются только те, что похожи внешне на латиницу. Таким образом, для извлечения номеров из текста нам поможет следующая регулярка:
Время
Для извлечения времени в формате ЧЧ:ММ подойдет такое регулярное выражение:
После двоеточия фрагмент [0-5]\d, как легко сообразить, задает любое число в интервале 00-59. Перед двоеточием в скобках работают два шаблона, разделенных логическим ИЛИ (вертикальной чертой):
- [0-1]\d - любое число в интервале 00-19
- 2[0-3] - любое число в интервале 20-23
К полученному результату можно применить дополнительно еще и стандартную Excel'евскую функцию ВРЕМЯ (TIME), чтобы преобразовать его в понятный программе и пригодный для дальнейших расчетов формат времени.
Проверка пароля
Предположим, что нам надо проверить список придуманных пользователями паролей на корректность. По нашим правилам, в паролях могут быть только английские буквы (строчные или прописные) и цифры. Пробелы, подчеркивания и другие знаки препинания не допускаются.
Проверку можно организовать с помощью вот такой несложной регулярки:
По сути, таким шаблоном мы требуем, чтобы между началом (^) и концом ($) в нашем тексте находились только символы из заданного в квадратных скобках набора. Если нужно проверить еще и длину пароля (например, не меньше 6 символов), то квантор + можно заменить на интервал "шесть и более" в виде {6,}:
Город из адреса
Допустим, нам нужно вытащить город из строки адреса. Поможет регулярка, извлекающая текст от "г." до следующей запятой:
Давайте разберем этот шаблон поподробнее.
Если вы прочитали текст выше, то уже поняли, что некоторые символы в регулярных выражениях (точки, звездочки, знаки доллара и т.д.) несут особый смысл. Если же нужно искать сами эти символы, то перед ними ставится обратная косая черта (иногда это называют экранированием). Поэтому при поиске фрагмента "г." мы должны написать в регулярке г\. если ищем плюсик, то \+ и т.д.
Следующих два символа в нашем шаблоне - точка и звездочка-квантор - обозначают любое количество любых символов, т.е. любое название города.
На конце шаблона стоит запятая, т.к. мы ищем текст от "г." до запятой. Но ведь в тексте может быть несколько запятых, правда? Не только после города, но и после улицы, дома и т.д. На какой из них будет останавливаться наш запрос? Вот за это отвечает вопросительный знак. Без него наша регулярка вытаскивала бы максимально длинную строку из всех возможных:
В терминах регулярных выражений, такой шаблон является "жадным". Чтобы исправить ситуацию и нужен вопросительный знак - он делает квантор, после которого стоит, "скупым" - и наш запрос берет текст только до первой встречной запятой после "г.":
Имя файла из полного пути
Еще одна весьма распространенная ситуация - вытащить имя файла из полного пути. Тут поможет простая регулярка вида:
Тут фишка в том, что поиск, по сути, происходит в обратном направлении - от конца к началу, т.к. в конце нашего шаблона стоит $, и мы ищем все, что перед ним до первого справа обратного слэша. Бэкслэш заэкранирован, как и точка в предыдущем примере.
P.S.
"Под занавес" хочу уточнить, что все вышеописанное - это малая часть из всех возможностей, которые предоставляют регулярные выражения. Спецсимволов и правил их использования очень много и на эту тему написаны целые книги (рекомендую для начала хотя бы эту). В некотором смысле, написание регулярных выражений - это почти искусство. Почти всегда придуманную регулярку можно улучшить или дополнить, сделав ее более изящной или способным работать с более широким диапазоном вариантов входных данных.
Для анализа и разбора чужих регулярок или отладки своих собственных есть несколько удобных онлайн-сервисов: RegEx101, RegExr и др.
К сожалению, не все возможности классических регулярных выражений поддерживаются в VBA (например, обратный поиск или POSIX-классы) и умеют работать с кириллицей, но и того, что есть, думаю, хватит на первое время, чтобы вас порадовать.
Если же вы не новичок в теме, и вам есть чем поделиться - оставляйте полезные при работе в Excel регулярки в комментариях ниже. Один ум хорошо, а два сапога - пара!
Ссылки по теме
- Замена и зачистка текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
- Поиск и подсветка символов латиницы в русском тексте
- Поиск ближайшего похожего текста (Иванов = Ивонов = Иваноф и т.д.)
Подскажите, реально ли использовать регулярные выражения в Excel для проверки данных?
Люди!!!
Очень прошу помочь в вопросе по теме урока.
И все же, подскажите пожалуйста что делать с ошибкой #ЗНАЧ??? я уже и 2016 офис установил, а по прежнему выдает ошибку
Текст ячейки:
Моторное масло GM Motor Oil 10W-40 (2)
Арт.: 1942044
Страна: Germany / Belgium
Тип масла / двиг.: Полусинтетическое / 4Т
Вязкость: 10W40
Спецификации: ACEA C3, A3/B4 API SL/CF
Допуски ОЕМ: General Motors, Daewoo, Opel
Объём: 2л
то что выделено жирным курсивом, необходимо из ячейки вытащить.
Догадываюсь что с помощью \s (Паттерн) нужно делать, но как указать что нужно начинать с 3-го #(lf) - невидимого знака выбирать и строго до текста: "Объём" не смог разобраться.
Просьба к знатокам
Public Function RegExpExtract(Text As String, Pattern As String, Optional Multiline As Boolean = False, Optional Item As Integer = 1, Optional Group As Integer = -1) As String
On Error GoTo ErrHandl
Set regex = CreateObject("VBScript.RegExp";)
regex.Pattern = Pattern
regex.Global = True
If Multiline Then regex.Multiline = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
If Group < 0 Then
RegExpExtract = matches.Item(Item - 1)
Exit Function
End If
RegExpExtract = matches.Submatches.Item(Group - 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function
Для выдёргивания куска текста:
Public Function RegExpIgnoreExtract(Text As String, IgnorePattern As String, Optional StartIndex As Integer = 1, Optional Pattern As String = ".*";) As String
On Error GoTo ErrHandl
Set regex = CreateObject("VBScript.RegExp";)
regex.Pattern = Pattern
regex.Global = True
regex.Multiline = True
Set ignoreRegex = CreateObject("VBScript.RegExp";)
ignoreRegex.Pattern = IgnorePattern
ignoreRegex.Global = True
ignoreRegex.Multiline = True
Dim result As String
result = ""
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
For i = 0 To matches.Count - 1
Do
If i <= StartIndex - 1 Then Exit Do
matchString = matches.Item(i)
If ignoreRegex.Test(matchString) Or matchString = "" Then Exit Do
result = result + matchString + Chr(10)
Loop While False
Next
RegExpIgnoreExtract = result
Exit Function
End If
ErrHandl:
RegExpIgnoreExtract = CVErr(xlErrValue)
End Function
Пример "Иркутская обл., г. Братск, пер. Дубынинский, 30, каб. 18, 665702"
на выходе" Иркутская обл., г. Братск, пер. Дубынинский, 30, каб. 18"
1A
1. a,b,c,d,e
2. a,b,c,d,e
3. a,b,c,d,e
и.т.д.
2A
1. a,b,c,d,e
2. a,b,c,d,e
3. a,b,c,d,e
и.т.д.
3A
1. a,b,c,d,e
2. a,b,c,d,e
3. a,b,c,d,e
и.т.д.
4A
и.т.д.
Нужно вытянуть например, из 3A
1. a,b,c,d,e
2. a,b,c,d,e
3. a,b,c,d,e
и.т.д.
Получается вытянуть только, из 3A
1. a,b,c,d,e
Если убираю из регулярки 3A, начинает вытягивать всё, по порядку начиная с 1A.
Например: из строки " Отправка контейнеров: MSKU1088563, TEMU4704019, BMOU4160816, PONU7384243, MRKU4329636, SKHU9106511, TCLU7225120, SKHU9519840, MSKU2924360"
выводит только MSKU1088563, а остальное не отображается.
А нужно, чтобы выводило MSKU1088563, TEMU4704019, BMOU4160816, PONU7384243, MRKU4329636, SKHU9106511, TCLU7225120, SKHU9519840, MSKU2924360
Хотя в коде модуля regex.Global = True
В чем подвох? Или Я не понимаю чего-то? Направьте пожалуйста
Вот если Вам понадобится не первый элемент, тогда да - Global имеет значение.
Я уже писал выше (заметка от 26.09.2018 10:46:42), как извлечь массив значений.
Полученный массив надо объединить так, как вам надо, функцией VBA:
Join(SourceArray, [Delimiter])
В Excel 2016 (Office 365) есть формула ОБЪЕДИНИТЬ (TEXTJOIN). Это позволило бы сделать процедуру ниже более универсальной.
Function RegExpExtractAll(Text As String, Pattern As String)
Dim arr() As String
Dim re As Object
Dim matches As Object
Dim i&
Set re = CreateObject("VBScript.RegExp")
re.Pattern = Pattern
If re.Test(Text) Then
Set matches = re.Execute(Text)
ReDim arr(matches.Count - 1)
For i = 0 To UBound(arr)
arr(i) = matches(i)
Next
If UBound(arr) = 0 Then
RegExpExtractAll = arr(0)
Else
RegExpExtractAll = Join(arr(), ", ") ' Объединяем через запятую с пробелом
End if
Exit Function
End If
Failed:
RegExpExtractAll = CVErr(xlErrValue)
End function
Просто я внес минимальные изменения "под вас" в готовую функцию.
Не могу файл прикрепить для образца
| | | | Вторая |Прочие |
| | Всего |Первая плата| плата | оплаты |
|-----------------------+---------------+---------------+---------------+---------------|
|Наименован-|Код дороги | | | | |
|ие дороги | | | | | |
|-----------+-----------| | | | |
|ОКТ |01 | 135961125| 135961125| 10207012| -2454000|
|-----------+-----------+---------------+---------------+---------------+---------------|
|КЛГР |10 | -3911400| -3911400| 3803739| 0|
|-----------+-----------+---------------+---------------+---------------+---------------|
Как исключить всю комбинацию "руб" ?
Например, [^abc] эквивалентно [^a-c]. Они соответствуют изначально 'r' в "brisket" и 'h' в "chop."
1. Вариант с регуляркой =RegExpExtract(A1;"[^•]+(?=•[^•.]*$)")
2. или же вариант с формулой ПСТР(A1;ПОИСК("•";A1)+1;ПОИСК("•";A1;ПОИСК("•";A1)+1)-ПОИСК("•";A1)-1)
Пример: нужно извлечь цены товаров, из строк со случайным расположением цены в перемешку с другим текстом
=RegExpExtract(A1:A2;\d{4,5})
Xiaomi Redmi 9A 2/32-7500 зеленый - извлекает 7500 (верно)
Ipad 2020 silver 128 Gb - 34000нал - извлекает 2020 (НЕ верно - это модель iPad 2020)
единственное что придумал пока извлекать цены с пятью цифрами \d{5}
в синтаксисе RegExpExtract третий пареметр item может указывать на искомое вхождение например 2 , но не все товары имеют два 4х значных числа. Как искать ПОСЛЕДНЕЕ ВХОЖДЕНИЕ (первое от конца строки)?
Чтобы впереди не было 4-5 цифр и чего угодно после до конца строки.
Тогда "2020" не подойдёт, поскольку впереди есть "34000нал".
Символ конца строки позволяет уменьшить “back tracking”.
А как можно сделать "заменить на" как это реализованное в npp+ ?
((0[1-9]\.)|([12][0-9]\.)|(3[01]\.))((0[1-9]\.)|(1[0-2]\.))((1[9]|2[0])\d{2})Извлечение даты с 01 или 1 января 1900 по 31 декабря 2099. Формат "dd mmmm yyyy"
((([1-9])|(0[1-9]))|([12][0-9])|(3[01]))([ \t])(января|февраля|марта|апреля|мая|июня|июля|августа|сентября|октября|ноября|декабря)([ \t])((1[9]|2[0])\d{2})Подскажите, как извлекать десятичные дроби (не целые числа, а например, "текст )-25,06- текст"
Фантастическая!
Мне кажется так даже удобнее - всё в пределах одной таблицы. Для столбца "Процент выполнения плана" я использовал =RegExpExtract(C4;"\d+,\d{2}")/RegExpExtract(D4;"\d+,\d{2}"). В С4 и D4 текстовые данные типа 8945,00 р.
Не работает в 2019, блин... в 2003 проверил - работает, а в старшем нет, FuzzyLookup кстати тоже так и не установилась, то есть в списке Надстройки СОМ она появлялась, галка тоже стоит, а панели нет, вот...
А в функции редактор VB выдает такое со всеми вытекающими (((
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";)
regex.Pattern = Pattern
regex.Global = True
If regex.Test(Text) Then
Set matches = regex.Execute(Text)
RegExpExtract = matches.Item(Item - 1)
Exit Function
End If
ErrHandl:
RegExpExtract = CVErr(xlErrValue)
End Function
Хм, вот что-то я сделал... и оно заработало, неужели это из-за того, что книга сперва на русском называлась...
Берём функцию RegExpExtract и вызываем её с параметром Pattern:
[a-zA-Z0-9.!#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*Взято . Спасибо !
Сделал как написано. Все получилось.
Искал в тексте ООО и ИП (столбец А)
Функция такая =ЕСЛИОШИБКА(RegExpExtract(A2;$C$2;1);"" В ячейке С2 - [ООО]{3}
В другой строке =ЕСЛИОШИБКА(RegExpExtract(A2;$E$2;1);"" В ячейки E2 - [ИП]{2}
Сохранил файл. Открыл по новому и в столбцах где было пусто, тоесть ошибка появился текст Error 2015
Ссылка на файл
Подскажите в чем проблема
подскажите как можно отделить последние 2 слова в ячейке?