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

Обновлено: 21.11.2024

Excel прекрасен. Эксель — это жизнь. В нем есть все, что вы могли когда-либо хотеть. Как вы могли бы улучшить совершенство? Вы не могли бы предложить ничего, чтобы хоть немного изменить мое мнение, я полностью.

Блин, это было бы очень мило.

Пошаговое регулярное выражение во всех ваших книгах

Загрузите следующую книгу Excel.
Включить редактирование защищенного листа.
Сохраните книгу как надстройку. 1. Файл > Сохранить как > Выбрать надстройку Excel (*.xlam) 2. На этом этапе он автоматически выберет правильное место назначения.
Затем вам нужно открыть меню надстроек 1. Файл > Параметры (откроется новое окно) > Надстройки >Управление надстройками Excel Перейти
Наконец включите надстройку с тем же именем, что и у этого листа. Если вы не изменили имя, это Regular-Expressions-Examples.
Вы готовы к работе! Теперь во всех ваших книгах есть регулярное выражение.

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

Какие функции вы получаете?

Это даст вам доступ к следующим формулам.

Найти регулярное выражение
RegExpFindExtended
RegExpFindSubmatch
RegExpReplace
RegExpReplaceExtended
RegExpReplaceRange

Как много формул.

Я остановлюсь только на двух, которые большинство людей охватывают большинство основных вариантов использования:

Если вы просто хотите скопировать и вставить, в следующем разделе есть примеры.

Если вы хотите разобраться во всех тонкостях, я написал соответствующую документацию в следующем разделе.

Полезные примеры формул регулярных выражений Excel

Соответствует ли ячейка регулярному выражению?* (без учета регистра)

=ISNUMBER(RegExpFind(cell_reference, regex_goes_here, ,FALSE,1,FALSE))

Соответствует ли ячейка регулярному выражению?* (с учетом регистра)

=ISNUMBER(RegExpFind(cell_reference, regex_goes_here, ,TRUE,1,FALSE))

Извлечь значение из строки (без учета регистра) (x-е совпадение)

=RegExpFind(cell_reference, regex_goes_here,x,FALSE,0,FALSE)
Это вернет именно то, что соответствует. К сожалению, он не поддерживает захват групп, поэтому вы сопоставляете x-е совпадение всего регулярного выражения.

Извлечь значение из строки (без учета регистра) (x-е совпадение)

=RegExpFind(cell_reference, regex_goes_here,x,TRUE,0,FALSE)

Заменить значение в строке (без учета регистра)

=RegExpReplace(cell_reference,regex_goes_here,replace_value,FALSE)

Документирование функций

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

Для тех из вас, кто не знаком с этим конкретным форматом документации, любые входные данные со знаком равенства после них, например. case_sensitive=FALSE являются необязательными. Значение после равенства является значением по умолчанию. "" означает пустой.

Найти регулярное выражение

RegExpFind(search, pattern,match_to_return="", case_sensitive=TRUE, return_type=0, multiline=FALSE)

search (строка или ссылка на ячейку) — строка или ссылка на ячейку, которую нужно найти.
pattern (str) — регулярное выражение, которому вы хотите соответствовать.

По умолчанию: возвращает массив всех совпадений с нулевым индексом
x: возвращает x-е совпадение
0, -1 : возвращает последнее совпадение.
-x: возвращает x-е последнее совпадение.

с учетом регистра (boolean) — если TRUE, регулярное выражение чувствительно к регистру, и наоборот.

0 – возврат совпадения
1 — числовая позиция первого символа совпадения.
2 – длина совпадений.

многострочный (boolean) — если FALSE, то ^ и $ будут соответствовать началу и концу всего текста в ячейке. Если TRUE, то они будут соответствовать каждой отдельной строке в ячейке (например, если вы скопировали всю статью).

RegExpReplace

RegExpReplace(поиск, шаблон, replace_with="", replace_all=TRUE, с учетом регистра=TRUE, многострочный)

search (строка или ссылка на ячейку) — строка или ссылка на ячейку, которую нужно найти.
pattern (str) — регулярное выражение, которому вы хотите соответствовать.
replace_with (str) : строка, которой вы хотите заменить совпадение.

replace_all (boolean) : если TRUE заменит все совпадения, если FALSE заменит только первое.

По умолчанию TRUE.
Если вам нужно заменить n-е совпадение, вы должны использовать RegExpReplaceRange (который на данный момент здесь не задокументирован. Однако он есть в необработанном коде в сообщении Патрика Мэтьюса.

с учетом регистра (int) : если FALSE регулярное выражение не чувствительно к регистру и наоборот.

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

Вы можете использовать текстовые функции для управления текстовыми строками в Excel. Однако вы не можете использовать их с регулярными выражениями. На момент написания этой статьи VBA — ваш единственный вариант. В этом руководстве мы покажем вам, как использовать регулярные выражения в Excel.

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

Регулярное выражение (также известное как regex или сокращенно regexp) – это специальная текстовая строка для указания шаблона поиска. Они как подстановочные знаки. Вместо указания количества символов вы можете создавать шаблоны для поиска определенной группы символов, например, поиск между «b» и «o», используя логику ИЛИ, исключая некоторые символы или повторяющиеся значения.

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

Шаблон	Описание	Образцы
^jack	начинаются с "jack"	мастер на все руки, дом Джека
jack$	заканчивается на "jack"	hijack
^jack$	именно " гнездо"	гнездо
цвет[u]r	может включать "u" не менее 0 раз	цвет, цвет (не цвет)
col[ o\|u]r	включает либо "o", либо "u"	цвет, цвет (не цвет)
col[^u]r	может принимать любой символ, кроме "u"	цвет (не цвет или цвет)

Как использовать регулярные выражения

Давайте начнем использовать регулярные выражения в Excel, открыв VBA. Нажмите клавиши Alt + F11, чтобы открыть окно VBA (Visual Basic для приложений). Добавьте модуль для ввода кода.

Следующий шаг — добавить ссылку на регулярное выражение в VBA. Щелкните Инструменты > Ссылки на панели инструментов VBA. Найдите и отметьте элемент Microsoft VBScript Regular Expressions 5.5 в окне "Ссылки". Нажмите OK, чтобы добавить ссылку.

Используя ссылку VBScript, мы можем создать объект регулярного выражения, который определяется как RegExp в VBA. Объект RegExp имеет 4 свойства и 3 метода:

Свойства

True

Методы

Имя	Тип	Описание
Global	Boolean	Установить True, чтобы найти все случаи, соответствующие шаблону. Установите False, чтобы найти первое совпадение.
IgnoreCase	Boolean	Установите True, чтобы не выполнять поиск с учетом регистра. Установите False, чтобы сделать поиск с учетом регистра.
Multiline	Boolean
Шаблон	Строка	Шаблон регулярного выражения, который вы хотите найти.

Имя	Аргументы	Описание
Выполнить	sourceString как строка	Возвращает массив, содержащий все вхождения шаблона, совпавшего в строке.
Заменить	sourceString As String replaceVar As Variant	Возвращает строку, в которой все вхождения шаблона в строке заменены строкой replaceVar.
Тест	sourceString как строка	Возвращает True, если есть совпадение. В противном случае False.

Примеры кода

Функция, которая возвращает ИСТИНА/ЛОЖЬ, если шаблон найден в строке

Открытая функция RegExFind(str As String, pat As String) As Boolean

'Определить объект регулярного выражения

Затемнить регулярное выражение как новое регулярное выражение

'Настройка свойств регулярного выражения

.Global = False 'Все вхождения не обязательны, поскольку достаточно одного вхождения

.IgnoreCase = True 'Без учета регистра

.MultiLine = True 'Проверить все строки

.Pattern = погладить 'шаблон

После написания кода вы можете использовать эту функцию как обычную функцию Excel.

Функция, которая заменяет шаблон заданной строкой

Открытая функция RegExReplace(str As String, pat As String, replaceStr As String) As String

'Определить объект регулярного выражения

Затемнить регулярное выражение как новое регулярное выражение

'Настройка свойств регулярного выражения

.Global = False 'Все вхождения не обязательны, поскольку достаточно одного вхождения

.IgnoreCase = True 'Без учета регистра

.MultiLine = True 'Проверить все строки

.Pattern = погладить 'шаблон

RegExReplace = RegEx.Replace(str, replaceStr) 'Вернуть измененную строку со значением замены

В следующем примере показано, как заменить строки, которые начинаются с «col», продолжаются 0 или 1 вхождением «o» и одиночным «u» и, наконец, заканчиваются символом «r» строкой «Color».< /p>

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

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

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

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

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

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

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

Шпаргалка Excel Regex

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

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

Если вы знакомы с регулярными выражениями, вы можете сразу перейти к функциям RegExp.

Персонажи

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

Классы персонажей

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

Шаблон	Описание	Пример	Совпадения
[characters]	Соответствует любому одиночному символу в квадратных скобках	d[oi]g	dog и dig
[^characters]	Соответствует любому одиночному символу НЕ в квадратных скобках	d [^oi]g	Соответствует dag, dug, d1g Не соответствует dog и dig
[from–to]	Соответствует любому символу в диапазоне между скобками	7 [az] [AZ]	Любая одиночная цифра от 0 до 9 Любая одиночная строчная буква Любая одиночная заглавная буква

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

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

Шаблон	Описание	Пример	Совпадения
	Ноль или более вхождений	1a*	1, 1a, 1aa, 1aaa и т. д.
+	Одно или несколько вхождений	po+	In pot, соответствует po В бедный, соответствует poo
?	Ноль или одно вхождение	roa?d	дорога, стержень< /td>
*?	Ноль или больше вхождений, но как можно меньше	1a*?	В 1a, 1aa и 1aaa соответствует 1a
+?	Одно или несколько вхождений, но как можно меньше	po+?	В pot и плохо, соответствует po
??	Ноль или одно появление, но как можно меньше	roa??	На дороге и rod, соответствует ro
	Соответствует предыдущему шаблону n раз	\d	Точно 3 цифры
	Соответствует предыдущему шаблону n или более раз	\d	3 или более цифр
	Соответствует предыдущему шаблону от n до m раз	\d	От 3 до 5 цифр

Группировка

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

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

Якоря

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

Конструкция чередования (ИЛИ)

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

Конструкция	Описание	Пример	Совпадения
\|	Соответствует любому отдельному элементу, разделенному вертикальной чертой	(s\|sh)ells	В она продает морские раковины, соответствует sells и оболочкам

Осмотр

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

Шаблон	Описание	Пример	Совпадения
(?=)< /td>	Положительный просмотр вперед	X(?=Y)	Соответствует выражению X, когда за ним следует Y (т. е. если Y предшествует X)
(?!)	Отрицательный прогноз	X(?!Y)	Соответствует выражению X, если за ним НЕ следует Y
(? =RegExpMatch(A5, "\b[AZ]-\d\b")

Для удобства можно ввести регулярное выражение в отдельную ячейку и использовать абсолютную ссылку ($A$2) в качестве аргумента шаблон. Это гарантирует, что адрес ячейки останется неизменным при копировании формулы в другие ячейки:

Чтобы отобразить собственные текстовые метки вместо ИСТИНА и ЛОЖЬ, вложите RegExpMatch в функцию ЕСЛИ и укажите нужные тексты в аргументах value_if_true и value_if_false:

=IF(RegExpMatch(A5, $A$2), "Да", "Нет")

Дополнительные примеры формул см.:

Функция извлечения регулярных выражений Excel

Функция RegExpExtract ищет подстроки, соответствующие регулярному выражению, и извлекает все совпадения или конкретное совпадение.

Текст (обязательно) – текстовая строка для поиска.
Шаблон (обязательно) — регулярное выражение для сопоставления.
Instance_num (необязательно) – серийный номер, указывающий, какой экземпляр нужно извлечь. Если этот параметр опущен, возвращаются все найденные совпадения (по умолчанию).
Match_case (необязательно) — определяет, следует ли учитывать (ИСТИНА или опустить) или игнорировать (ЛОЖЬ) регистр текста.

Вы можете получить код функции здесь.

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

Расширяя наш пример, давайте извлечем номера счетов. Для этого мы будем использовать очень простое регулярное выражение, которое соответствует любому 7-значному числу:

Поместите выкройку в формат A2, и вы получите результат с помощью этой компактной и элегантной формулы:

Если шаблон совпадает, формула извлекает номер счета, если совпадения не найдено, ничего не возвращается.

Функция замены регулярных выражений Excel

Функция RegExpReplace заменяет значения, соответствующие регулярному выражению, на указанный вами текст.

Текст (обязательно) – текстовая строка для поиска.
Шаблон (обязательно) — регулярное выражение для сопоставления.
Replacement (обязательно) – текст, которым нужно заменить совпадающие подстроки.
Instance_num (необязательно) — экземпляр для замены. Значение по умолчанию — «все совпадения».
Match_case (необязательно) – определяет, следует ли учитывать (ИСТИНА или опускать) или игнорировать (ЛОЖЬ) регистр текста.

Код функции доступен здесь.

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

Некоторые из наших записей содержат номера кредитных карт. Эта информация является конфиденциальной, и вы можете захотеть заменить ее чем-то или удалить вовсе. Обе задачи можно выполнить с помощью функции RegExpReplace. Как? Во втором сценарии мы будем заменять пустой строкой.

В нашем образце таблицы все номера карт состоят из 16 цифр, разделенных пробелами на 4 группы. Чтобы найти их, мы повторяем шаблон, используя это регулярное выражение:

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

Замена: XXXX XXXX XXXX XXXX

А вот полная формула замены номеров кредитных карт неконфиденциальной информацией:

=RegExpReplace(A5, "\b\d \d \d \d\b", "XXXX XXXX XXXX XXXX")

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

В Excel «удаление» — это частный случай «замены». Чтобы удалить номера кредитных карт, просто используйте пустую строку ("") в качестве аргумента replacement:

=RegExpReplace(A5, "\b\d \d \d \d\b", "")

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

Для получения дополнительной информации см.:

Инструменты регулярных выражений для сопоставления, извлечения, замены и удаления подстрок

Пользователи нашего Ultimate Suite могут получить всю мощь регулярных выражений, не добавляя ни одной строки кода в свои книги. Весь необходимый код написан нашими разработчиками и плавно интегрирован в ваш Excel во время установки.

Как использовать регулярное выражение в Excel

С установленным Ultimate Suite использование регулярных выражений в Excel выполняется так же просто, как два шага:

Выберите исходные данные.
Введите шаблон регулярного выражения.
Выберите нужный вариант: Сопоставить, Извлечь, Удалить или Заменить.
Чтобы получить результат в виде формулы, а не значения, установите флажок Вставить как формулу.
Нажмите кнопку действия.

Например, чтобы удалить номера кредитных карт из ячеек A2:A6, мы настраиваем следующие параметры:

Мгновенно функция AblebitsRegex будет вставлена в новый столбец справа от исходных данных. В нашем случае формула такова:

=AblebitsRegexRemove(A2, "\b\d \d \d \d\b")

После создания формулы ее можно редактировать, копировать или перемещать, как и любую нативную формулу.

Как вставить формулу регулярного выражения прямо в ячейку

Функции AblebitsRegex также можно вставлять непосредственно в ячейку без использования интерфейса надстройки. Вот как:

Дополнительную информацию см. в разделе Инструменты регулярных выражений для Excel.

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

Регулярные выражения могут быть очень полезны для проверки данных и управления ими. К сожалению, в Excel нет встроенной поддержки регулярных выражений. Вы можете использовать VBA, но я думаю, что это слишком грязно.

В моем плагине Excel SeoTools я добавил четыре метода:

=RegexpIsMatch(строковый ввод; строковое выражение)

Сопоставляет входную строку с регулярным выражением и возвращает true, если есть совпадение.

=RegexpFind(строковый ввод; строковое выражение; группа целых чисел)

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

=RegexpFindOnUrl(URL-адрес строки; регулярное выражение строки; группа int)

То же, что и RegexpFind, но входная строка представляет собой содержимое веб-страницы.

=RegexpReplace(строковый ввод; строковое выражение; замена строки)

В указанной входной строке заменяет все строки, соответствующие указанному регулярному выражению, указанной замещающей строкой.

Все приведенные ниже примеры предполагают, что вы загрузили и добавили SeoTools в документ, над которым работаете.

Пример 1. Проверка адресов электронной почты с помощью RegexpIsMatch

Допустим, у вас есть столбец с кучей адресов электронной почты, и вы хотите найти все недействительные адреса. Следующая формула вернет FALSE для всех недействительных адресов электронной почты:

Пример 2. Поиск определенной части текста

Теперь предположим, что у вас есть столбец с текстом, из которого вы хотите извлечь определенную часть, например, «Спорт (id:4)», где мы хотели бы извлечь «4» в новый столбец.

Пример 3. Получение количества страниц, проиндексированных в Google по набору ключевых слов

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

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

Читайте также: