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

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

Что такое регулярное выражение?

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

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

Регулярные выражения имеют собственный синтаксис, состоящий из специальных символов, операторов и конструкций. Например, [0-5] соответствует любой одиночной цифре от 0 до 5.

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

Поддерживает ли Excel регулярные выражения?

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

Шпаргалка по регулярным выражениям Excel

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

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

Символы

Это наиболее часто используемые шаблоны для соответствия определенным символам.

ШаблонОписаниеПримерНайдено
.Подстановочный знак: соответствует любому одиночному символу, кроме разрыва строки..откот , лот , @от
\dСимвол цифры: любая одиночная цифра от 0 до 9\dВ a1b найдено  1
\DЛюбой символ, НЕ являющийся цифрой\DВ a1b найдено  a и b
\sПробельный символ: пробел, табуляция, новая строка и возврат каретки.\s.В   3 яблока   найдено  3 я
\SЛюбой непробельный символ. Анти-вариант предыдущего\S+В  30 яблок найдено  30 и яблок
\wСимвол слова: любая буква ASCII, цифра или подчеркивание.\w+В  5_яблок*** найдено  5_яблок
\ B+Любой символ, который НЕ является буквенно-цифровым символом или символом подчеркивания\В+В  5_яблок*** найдено  ***
\tТабуляция
\nНовая строка\n\d+В двухстрочной строке ниже соответствует 10 5 кошек
10 собак
\Позволяет использовать специальный символ как обычный\.
 
 
\w+\.
Игнорирует подстановочный знак, чтобы вы могли найти буквальный символ "." в строке
 
Mr. , д-р. , проф.

Классы

Используя эти шаблоны, вы можете сопоставлять элементы разных наборов символов.

ШаблонОписаниеПримерНайдено
[символы]В квадратных скобках можно указать один или несколько символов, допустимых на указанной позиции в тексте.ст[оу]лстол   стул
[^символы]На указанной позиции в тексте будут разрешены все символы, кроме перечисленных в скобках. [^жм]утьСоответствует путь, суть
Не соответствует жуть,  муть
[от-до]Соответствует любому символу[0-9]
[а-я]
[А-Я] [б-ф]   [а-яА-ЯёЁ]
Любая цифра от 0 до 9
Любая строчная буква
Любая прописная буква Любая из букв в скобках [бвгдежзиклмнопрстуф] Все буквы русского алфавита (буква Ë указывается отдельно!)

Квантификаторы

Квантификаторы — это специальные выражения, которые определяют количество совпадающих символов. Квантификатор всегда применяется к символу, стоящему перед ним перед ним.

ШаблонОписаниеПримерНайдено
*Ноль или более вхождений1а*1, 1а , 1аа, 1ааа и т. д.
+Одно или более вхожденийко+В кот найдено  ко
В кооперация найдено  коo
?Ноль или одно вхождениеко?ткот, корт
*?Ноль или более вхождений, но как можно меньше1а*?В 1a , 1aa и 1aaa найдено  1a
+?Одно или несколько событий, но как можно меньшеко+?В кот и кооперация найдено  ко
{n}Строго определённое количество вхождений\d{3}Ровно 3 любых цифры
{n,}Не менее n вхождений\d{3,}3 или более цифр
{,n}Не более n вхождений\d{,3}Не более 3 цифр
{n, m}Соответствует предыдущему шаблону от n до m раз\d{3,5}От 3 до 5 цифр

Группы

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

СинтаксисОписаниеПримерНайдено
(шаблон)Группа захвата: захватывает совпадающую подстроку и присваивает ей порядковый номер(\d+)5 кошек и 10 собак  5 (группа 1) и 10 (группа 2)
(?:шаблон)Группа без захвата: соответствует группе, но не захватывает ее(\d+)(?:собак)5 кошек и 10 собак  10
\1Содержимое группы 1(\d+)\+(\d+)=\2\+\15+10=10+5
\2Содержимое группы 2

Якоря

Якоря указывают позицию во входной строке, где искать соответствие.

ЯкорьОписаниеПримерНайдено
^Начало строки Примечание: [^в скобках] означает «не»^\d+Любое количество цифр в начале строки. 5 кошек и 10 собак Найдено  5
$Конец строки\д+$Любое количество цифр в конце строки. В 10 плюс 5 получается 15 , найдено  15
\bКонец словакот\b1 кот и 10 котят  Соответствует кот, но не котят
НЕ конец словакот\B1 кот и 10 котят  Соответствует котят, но не кот

Конструкция ИЛИ

Операнд чередования включает логику ИЛИ, поэтому вы можете сопоставить тот или иной элемент.

ПостроитьОписаниеПримерСоответствие
|Соответствует любому отдельному элементу, разделенному вертикальной чертой(счет|invoice)Любое из перечисленных слов

Поиск

Конструкции поиска полезны, когда вы хотите найти что-то, за чем следует или не следует что-то другое. Эти выражения иногда называют «утверждениями нулевой ширины» или «совпадением нулевой ширины», потому что они соответствуют позиции, а не фактическим символам.

Примечание. В варианте VBA RegEx просмотр назад не поддерживается.

ШаблонОписаниеПримерНайдено
= знак равноПоложительный впередХ(?=Y)Соответствует выражению X, когда за ним следует Y (т. е. если Y предшествует X)
(?!)Отрицательный впередХ(?!У)Соответствует выражению X, если за ним НЕ следует Y
(?<=)Положительный назад(?<=Y)ХСоответствует выражению X, когда ему предшествует Y (т. е. если Y находится позади X)
(?<!)Отрицательный назад(?<!Y)ХСоответствует выражению X, если ему НЕ предшествует Y

Жадные и ленивые сопоставления

Квантификаторы (* + {}) ― это «жадные» операторы, потому что они продолжают поиск соответствий, как можно глубже ― через весь текст.

ШаблонОписаниеПримерНайдено
<.+>Жадный поиск<.+>В выражении Это <div> простой div</div> тест возвращает <div> простой div</div>

Чтобы найти только тэг div ― можно использовать оператор ?, сделав выражение «ленивым»:

ШаблонОписаниеПримерНайдено
<.+?>Ленивый поиск<.+?>В выражении Это <div> простой div</div> тест возвращает 2 совпадения: <div>  </div>
<[^<>]+>Ленивый поиск<[^<>]+>В выражении Это <div> простой div</div> тест возвращает 2 совпадения: <div>  </div>

Обратите внимание, что хорошей практикой считается не использовать оператор . , в пользу более строгого выражения:  <[^<>]+>

<[^<>]+> соответствует любому символу, кроме скобок < или >,один или более раз встречающемуся между этими скобками.

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

Проверка данных с помощью регулярных выражений - В этом руководстве показано, как выполнять проверку данных в Excel с помощью регулярных выражений и пользовательской функции RegexMatch. Когда дело доходит до ограничения пользовательского ввода на листах Excel, проверка данных очень полезна. Хотите…
Как удалить пробелы и пустые строки в Excel с помощью Regex - Хотите обрабатывать пробелы наиболее эффективным образом? Используйте регулярные выражения, чтобы удалить все пробелы в ячейке, заменить несколько пробелов одним символом, обрезать пробелы только между числами и т. д. Какие бы исходные…
Удаление символов или текста при помощи регулярных выражений - В этом руководстве показано, как использовать регулярные выражения для удаления части содержимого текстовой ячейки в Excel. Вы когда-нибудь задумывались, насколько мощным был бы Excel, если бы кто-нибудь мог обогатить его…
Поиск и замена в Excel с помощью регулярных выражений - В этом руководстве показано, как быстро добавить пользовательскую функцию в свои рабочие книги, чтобы вы могли использовать регулярные выражения для замены текстовых строк в Excel. Когда дело доходит до замены…
Как извлечь строку из текста при помощи регулярных выражений - В этом руководстве вы узнаете, как использовать регулярные выражения в Excel для поиска и извлечения части текста, соответствующего заданному шаблону. Microsoft Excel предоставляет ряд функций для извлечения текста из ячеек. Эти функции…