На первый взгляд, в 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\+\1 | 5+10=10+5 |
| \2 | Содержимое группы 2 |
Якоря
Якоря указывают позицию во входной строке, где искать соответствие.
| Якорь | Описание | Пример | Найдено |
| ^ | Начало строки Примечание: [^в скобках] означает «не» | ^\d+ | Любое количество цифр в начале строки. 5 кошек и 10 собак Найдено 5 |
| $ | Конец строки | \д+$ | Любое количество цифр в конце строки. В 10 плюс 5 получается 15 , найдено 15 |
| \b | Конец слова | кот\b | 1 кот и 10 котят Соответствует кот, но не котят |
| \В | НЕ конец слова | кот\B | 1 кот и 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> |
Обратите внимание, что хорошей практикой считается не использовать оператор . , в пользу более строгого выражения: <[^<>]+>
<[^<>]+> соответствует любому символу, кроме скобок < или >, один или более раз встречающемуся между этими скобками.
Теперь, когда вы знаете синтаксис регулярных выражений, давайте перейдем к самой интересной части — использованию регулярных выражений на реальных данных для разбора строк и поиска необходимой информации.
