Анализ текста с помощью регулярных выражений (RegExp) в Excel

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

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

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

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

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

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

Открытая функция RegExpExtract(Текст как строка, Шаблон как строка, Необязательный элемент как целое число = 1) Как строка При ошибке GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Текст) Затем установите совпадения = regex.Execute(Text) RegExpExtract = match.Item(Item - 1) Выход из функции End If ErrHandl: RegExpExtract = CVErr(xlErrValue) Конец функции  

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

=RegExpExtract( Txt ; Шаблон ; Элемент )

в котором

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

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

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

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

Извлечение чисел из текста

Для начала разберем простой случай — нужно из буквенно-цифровой каши извлечь первую цифру, например, мощность источников бесперебойного питания из прайс-листа:

Логика регулярного выражения проста: d означает любую цифру, а квантор + говорит, что их число должно быть один или несколько. Двойной минус перед функцией нужен для того, чтобы «на лету» преобразовать извлеченные символы в полное число из числа в виде текста.

Почтовый индекс

На первый взгляд здесь все просто – ищем ровно шесть цифр подряд. Мы используем специальный символ d для цифры и квантификатора 6 {} по количеству символов:

Однако возможна ситуация, когда слева от индекса в строке стоит еще один большой набор цифр подряд (номер телефона, ИНН, счет в банке и т.д.) Тогда наша регулярка вытянет первые 6 цифры из него, т.е. работать будет некорректно:

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

Телефон

Проблема с поиском номера телефона в тексте в том, что вариантов написания номеров очень много – с дефисами и без, через пробелы, с кодом региона в скобках или без него и т.д. Поэтому, на мой взгляд, проще сначала очистите все эти символы из исходного текста, используя несколько вложенных функций ЗАМЕНА (ЗАМЕНЯТЬ)чтобы оно склеилось в единое целое, а затем примитивным регулярным д{11} вытащите 11 цифр подряд:

СОИ

Здесь немного сложнее, ведь ИНН (в Нашей Стране) может быть 10-значным (для юридических лиц) или 12-значным (для физических лиц). Если особо не придираться, то вполне можно довольствоваться штатным. д{10,12}, но, строго говоря, выдернет все числа от 10 до 12 символов, т.е. и ошибочно введённые 11 цифр. Правильнее было бы использовать два шаблона, соединенных логическим оператором ИЛИ. | (вертикальная черта):

Обратите внимание, что в запросе мы сначала ищем 12-битные числа, а уже потом 10-битные. Если мы напишем наше регулярное выражение наоборот, то оно будет вытягивать для всех, даже длинных 12-битных TIN, только первые 10 символов. То есть после срабатывания первого условия дальнейшая проверка уже не производится:

В этом принципиальная разница между оператором | из стандартной логической функции Excel OR (OR), где перестановка аргументов не меняет результат.

Артикулы продукта

Во многих компаниях товарам и услугам присваиваются уникальные идентификаторы — артикулы, SAP-коды, SKU и т. д. Если в их обозначениях есть логика, то их можно легко вытащить из любого текста с помощью регулярных выражений. Например, если мы знаем, что наши статьи всегда состоят из трех заглавных английских букв, дефиса и последующего трехзначного числа, то:

Логика шаблона проста. [AZ] – означает любые заглавные буквы латинского алфавита. Следующий квантификатор 3 {} говорит, что для нас важно, чтобы таких букв было ровно три. После дефиса ждем три цифры, поэтому добавляем в конце д{3}

Денежные суммы

Аналогично предыдущему пункту вы также можете вытащить цены (затраты, НДС…) из описания товара. Если денежные суммы, например, указаны через дефис, то:

шаблон d с квантором + ищет любое число до дефиса и д{2} после будет искать копейки (две цифры).

Если вам нужно извлечь не цены, а НДС, то вы можете использовать третий необязательный аргумент нашей функции RegExpExtract, который указывает порядковый номер извлекаемого элемента. И, конечно же, вы можете заменить функцию ЗАМЕНА (ЗАМЕНЯТЬ) в результатах добавьте дефис к стандартному десятичному разделителю и добавьте в начале двойной минус, чтобы Excel интерпретировал найденный НДС как обычное число:

Номера автомобилей

Если вы не берете спецтехнику, прицепы и другие мотоциклы, то стандартный номер автомобиля анализируется по принципу «буква – три цифры – две буквы – код региона». При этом код региона может быть 2- или 3-значным, а в качестве букв используются только те, которые по внешнему виду схожи с латинским алфавитом. Таким образом, следующее регулярное выражение поможет нам извлечь числа из текста:

Время

Для извлечения времени в формате ЧЧ:ММ подойдет следующее регулярное выражение:

После фрагмента толстой кишки [0-5]д, как несложно догадаться, задает любое число в диапазоне 00-59. Перед двоеточием в скобках работают два шаблона, разделенные логическим ИЛИ (вертикальной чертой):

• [0-1]д – любое число в диапазоне 00-19
• 2 [0-3] – любое число в диапазоне 20-23

К полученному результату можно дополнительно применить стандартную функцию Excel ВРЕМЯ (КОМАНДА)преобразовать его в формат времени, понятный программе и пригодный для дальнейших расчетов.

Проверка пароля

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

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

Фактически, при таком шаблоне мы требуем, чтобы между началом (^) и конец ($) в нашем тексте были только символы из набора, данного в квадратных скобках. Если вам также необходимо проверить длину пароля (например, не менее 6 символов), то квантор + можно заменить интервалом «шесть и более» в виде {6,}:

Город по адресу

Допустим, нам нужно вытащить город из адресной строки. Поможет штатная программа, извлекающая текст из «г». до следующей запятой:

Давайте подробнее рассмотрим этот узор.

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

Следующие два символа в нашем шаблоне, точка и звездочка-квантификатор, обозначают любое количество любых символов, т.е. любое название города.

В конце шаблона стоит запятая, потому что мы ищем текст из «г». до запятой. Но запятых в тексте может быть несколько, верно? Не только после города, но и после улицы, дома и т. д. На каком из них остановится наш запрос? Вот для чего нужен вопросительный знак. Без него наше регулярное выражение вытащило бы максимально длинную строку:

С точки зрения регулярных выражений такой шаблон является «жадным». Чтобы исправить ситуацию, нужен вопросительный знак — он делает квантор, после которого он стоит, «скупым» — и наш запрос принимает текст только до первой встречной запятой после «г.»:

Имя файла из полного пути

Другая очень распространенная ситуация — извлечение имени файла из полного пути. Здесь поможет простое регулярное выражение вида:

Хитрость здесь в том, что поиск, по сути, происходит в обратном направлении – из конца в начало, потому что в конце нашего шаблона находится $, и мы ищем все до него, вплоть до первой обратной косой черты справа. Обратная косая черта экранируется, как и точка в предыдущем примере.

PS

«Под конец» хочу уточнить, что все вышеперечисленное — это малая часть всех возможностей, которые предоставляют регулярные выражения. Спецсимволов и правил их использования очень много, на эту тему написаны целые книги (рекомендую для начала хотя бы эту). В каком-то смысле написание регулярных выражений — это почти искусство. Практически всегда придуманное регулярное выражение можно улучшить или дополнить, сделав его более элегантным или способным работать с более широким диапазоном входных данных.

Для анализа и парсинга чужих регулярных выражений или отладки своих существует несколько удобных онлайн-сервисов: RegEx101, RegExr и более

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

Если вы не новичок в теме и вам есть чем поделиться, оставляйте в комментариях ниже регулярные выражения, полезные при работе в Excel. Один ум – хорошо, а два сапога – пара!

• Замена и очистка текста с помощью функции ПОДСТАВИТЬ
• Поиск и подсветка латинских символов в тексте
• Поиск ближайшего похожего текста (Иванов=Ивонов=Иванов и т.д.)