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

Фильтрация и поиск в Excel — непростая задача. Функции регулярных выражений изменят это. Теперь вы можете точно указать, что вам нужно — сложные шаблоны, частичные совпадения или структурированное извлечение данных — без лишних усилий.

Оглавление

• Что такое Regex?
• Поиск шаблонов
• Дополнительные данные с использованием Regex
• Найти и заменить с помощью регулярного выражения
• Объединение Regex с другими функциями

Что такое Regex?

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

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

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

Ниже приведены символы, которые будут использоваться в этом руководстве:

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

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

Поиск шаблонов

Первая функция, которую мы рассмотрим, — REGEXTEST. Эта функция принимает текстовую строку, которую вы хотите использовать для поиска, и шаблон регулярного выражения, а затем использует последний шаблон для поиска совпадений в первом шаблоне. Функция вернет True или False.

Синтаксис функции REGEXTEST следующий:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

Первые два параметра, string_to_search и regex_pattern_to_use, говорят сами за себя. Параметр [case_sensitivity] является необязательным (все, что заключено в квадратные скобки, когда речь идет о синтаксисе Excel, является необязательным) и указывает, хотите ли вы, чтобы поиск был чувствительным к регистру (0) или нечувствительным к регистру (1). По умолчанию регистр учитывается.

В этом примере будет использоваться REGEXTEST для проверки того, ввел ли пользователь действительный адрес электронной почты, с использованием следующей формулы:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

Здесь мы ищем в ячейке B3 адрес электронной почты, используя шаблон регулярного выражения ниже:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

Если вы поместите формулу в ячейку C3 и введете john.doe@example.com в ячейку B3, формула вернет значение True, поскольку оно соответствует подписи электронной почты.

Дополнительные данные с использованием Regex

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

Синтаксис функции REXEXEXTRACT следующий:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

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

Формула будет выглядеть так:

=REGEXEXTRACT(B3, "([^@]+)")

В этой формуле мы извлекаем все до символа @ в адресе электронной почты, введенном в B3.

Найти и заменить с помощью регулярного выражения

Последняя функция регулярного выражения, которую мы рассмотрим, — REGEXREPLACE. Эта функция похожа на функцию REPLACE в Excel, но также поддерживает RegEx. Он берет текстовую строку, которую вы хотите изменить, и проверяет, соответствует ли какая-либо подстрока определенному шаблону регулярного выражения. Если строка найдена, она заменяется предоставленной строкой замены.

Синтаксис функции REGEXREPLACE следующий:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

Вот важные параметры, которые следует учитывать в этой функции:

• string_to_modify : Текстовая строка, которую вы хотите изменить.
• replacement_string : Строка, на которую следует заменить подстроку.
• number_of_occurrences : точное количество случаев, которые вы хотите заменить.

Вот пример использования функции для замены части имени пользователя в сообщении электронной почты другой текстовой строкой:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

Значение B3 — john.doe@example.com , и после ввода указанной выше формулы в ячейку C3 она вернет jane.doe@example.com.

Объединение Regex с другими функциями

Вы также можете комбинировать функции регулярных выражений с другими функциями в Excel. Например, вы можете объединить функцию REGEXTEST с оператором IF в Excel и выводить соответствующие сообщения на основе результатов.

Вот пример формулы:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

Эта формула использует оператор ЕСЛИ для проверки допустимости адреса электронной почты, введенного в ячейку B6, а затем выводит сообщение: Это допустимый адрес электронной почты! если ИСТИНА или адрес электронной почты недействителен! если ЛОЖЬ . Кроме того, вы можете объединить эту формулу с функцией НАЙТИ, чтобы быстро находить данные в Excel.

Это хороший способ начать использовать RegEx в Excel. Варианты использования и возможности ограничены только вашим воображением.