REGEXEXTRACT Совет по функциям Google Таблиц:

Вот несколько важных советов, которые необходимо помнить при использовании функции REGEXEXTRACT.

• Эта функция работает только с вводом текста. Не работает с числами
• Если вы хотите использовать числа в качестве входных данных (например, телефонные номера), вам необходимо сначала преобразовать их в текст, используя функцию TEXT.
• Функция REGEXEXTRACT чувствительна к регистру. Следовательно, вам нужно будет указать правильный регистр внутри регулярного выражения или преобразовать всю входную строку в верхний или нижний регистр с помощью функций UPPER или LOWER.

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

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

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

Быстрее и удобнее обрабатывать текст в гугл таблицах

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

Для этого лучше использовать регулярные выражения.

Привожу шаблон Гугл таблиц с несколькими самыми популярными для аналитика запросами.

https://docs.google.com/spreadsheets/d/11IuUtQVZUFygTjNzZvaZepb_Y01rb1BT2EJSunD9vl0/edit?usp=sharing

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

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

Применение в google таблицах

Задачи могут сильно различаться. Например в гугл таблицах нужно убрать из utm ссылок всё, что идёт после адреса страницы.

Ссылки с utm метками

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

REGEXREPLACE(текст; регулярное_выражение; замена)

— С текстом всё понятно, выбираем ячейку с ссылкой;
— С регулярным выражением сложнее, пишем код \&.*|\?.* в отдельной ячейке и выбираю её. Далее разберу подробнее, что он значит;
— Для замены используем пустое место “”

Таблица замены

Получаем в столбце “Формула” ссылку без utm меток. Фактически происходит следующее:
Мы говорим, возьми текст из ячейки А2 → выбери по регулярному выражению в ячейке В2 весь текст из А2 → подставь пустое место (“”)

Разберём регулярное выражение \&.*|\?.*
Используя такой код, мы говорим возьми все символы (.*) до символа & (\&) или (|) возьми все символы (.*) до символа ? (\?)

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

Для этого нужно определить символ и вывести значения до него. \:]*

Где посмотреть все регулярные выражения

Значения регулярных выражений можно посмотреть на специальном сайте https://regexr.com/. Очень удобно вставить анализируемый текст и подбирать коды регулярных выражений. Подсветкой будет выделен выбираемый текст.

Скрин моделирования регулярных выражений

Коды приведены в левом пунтке меню Cheatsheet

Кратко привожу их тут

Код	Описание
.	Самое простое – это точка. Она обозначает любой символ в шаблоне на указанной позиции.
\s	Любой символ, выглядящий как пробел (пробел, табуляция или перенос строки).
\S	Анти-вариант предыдущего шаблона, т.е. любой НЕпробельный символ.
\d	Любая цифра
\D	Анти-вариант предыдущего, т.е. любая НЕ цифра
\w	Любой символ латиницы (A-Z), цифра или знак подчеркивания
\W	Анти-вариант предыдущего, т.	Начало строки
$	Конец строки
\b	Край слова

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

Квантор	Описание
?	Ноль или одно вхождение. Например .? будет означать один любой символ или его отсутствие.
+	Одно или более вхождений. Например \d+ означает любое количество цифр (т.е. любое число от 0 до бесконечности).
*	Ноль или более вхождений, т.е. любое количество. Так \s* означает любое количество пробелов или их отсутствие.
{число} или {число1,число2}	Если нужно задать строго определенное количество вхождений, то оно задается в фигурных скобках. Например \d{6} означает строго шесть цифр, а шаблон \s{2,5} – от двух до пяти пробелов

Полезные ссылки по теме

Справка гугла
Помимо использования REGEXREPLACE регулярные выражения можно применять и в
REGEXEXTRACT: Извлекает определенную часть текста, соответствующую регулярному выражению.
REGEXMATCH: Проверяет, соответствует ли текст регулярному выражению.
ПОДСТАВИТЬ: Заменяет один текст на другой.
ЗАМЕНИТЬ: Заменяет выбранный текст на другой

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

Друзья, если вам помогла моя статья, пожалуйста, помогите и вы мне — подпишитесь, пожалуйста, на один из моих блогов: канал в Телеграм, сообщество Вконтакте, email-рассылку или просто поблагодарите в комментарии к статье. Буду вам очень признателен.

Павел — Автор блога

Телеграм канал

Сообщество Вк

Подписка email

Дзен

REGEXEXTRACT: Объяснение формул Google Sheets

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

REGEXEXTRACT Синтаксис

Синтаксис формулы REGEXEXTRACT в Google Sheets следующий:

=REGEXEXTRACT(текст, регулярное_выражение)

Где:

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

При использовании REGEXEXTRACT формула вернет первый экземпляр указанного шаблона, найденный во входном тексте. Если совпадений не найдено, формула вернет ошибку.

REGEXEXTRACT Примеры

Давайте рассмотрим несколько примеров, чтобы лучше понять, как формула REGEXEXTRACT работает в Google Таблицах.

Пример 1. Извлечение слова из предложения

Предположим, в ячейке A1 есть следующее предложение:

«Быстрая коричневая лиса перепрыгивает через ленивую собаку».

Вы хотите извлечь из этого предложения слово «лиса». Вы можете использовать формулу REGEXEXTRACT следующим образом:

=REGEXEXTRACT(A1, «лиса»)

Эта формула вернет слово «лиса» в качестве вывода.

Пример 2. Извлечение числа из текстовой строки

Представьте, что у вас есть следующий текст в ячейке A1:

«Номер заказа: 12345»

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

=REGEXEEXTRACT(A1, «\d+»)

Эта формула возвращает число «12345» в качестве вывода.

Пример 3. Извлечение адреса электронной почты из текстовой строки

Рассмотрим следующий текст в ячейке A1:

«Пожалуйста, отправьте свой отзыв на [email protected]. Спасибо!»

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

=REGEXEXTRACT(A1, «[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}»)

Эта формула вернет адрес электронной почты «[email protected] » в качестве вывода.

REGEXEXTRACT Советы и приемы

Вот несколько советов и приемов, которые помогут вам получить максимальную отдачу от формулы REGEXEXTRACT в Google Таблицах:

1. При создании шаблона регулярного выражения не забудьте использовать двойные кавычки для вложите шаблон. Например, используйте «лиса» вместо «лиса».
2. Используйте специальные символы и квантификаторы в шаблоне регулярного выражения для соответствия определенным шаблонам текста. Например, используйте «\d+» для соответствия одному или нескольким цифры или «[A-Za-z]+», чтобы соответствовать одной или нескольким буквам.0016
3. Если вы хотите извлечь несколько экземпляров шаблона из входного текста, рассмотрите возможность использования функции SPLIT в сочетании с REGEXEXTRACT. Например, вы можете сначала разделить входной текст разделителем, а затем применить REGEXEXTRACT к каждой части разделенного текста.

Распространенные ошибки при использовании REGEXEXTRACT

Вот некоторые распространенные ошибки, которые допускают пользователи при использовании формулы REGEXEXTRACT в Google Таблицах:

1. Использование одинарных кавычек вместо двойных для заключения шаблона регулярного выражения. Это приведет к ошибке. Всегда используйте двойные кавычки, например: «шаблон».
2. Неправильный экранирование специальных символов в шаблоне регулярного выражения. Например, если вы хотите сопоставить точку (.), вы должны использовать «\». вместо просто «.». Последний будет соответствовать любому символу, а не только точке.
3. Использование неправильных или неполных шаблонов регулярных выражений. Это может привести к неожиданным результатам или ошибкам. Обязательно тщательно протестируйте шаблоны регулярных выражений, прежде чем использовать их в формуле REGEXEXTRACT.

Почему мой REGEXEXTRACT не работает?

Если ваша формула REGEXEXTRACT не работает должным образом, рассмотрите следующие шаги по устранению неполадок:

1. Дважды проверьте шаблон регулярного выражения. Убедитесь, что он заключен в двойные кавычки и правильно экранирует все специальные символы.
2. Убедитесь, что вводимый текст имеет правильный формат. Например, если ваш вводимый текст содержит разрывы строк или лишние пробелы, это может повлиять на способность формулы REGEXEXTRACT соответствовать указанному шаблону.
3. Протестируйте шаблон регулярного выражения с помощью онлайн-тестера регулярных выражений, например regex101.com. Это может помочь вам определить любые проблемы с шаблоном и внести необходимые коррективы.
4. Если у вас по-прежнему возникают проблемы, подумайте о том, чтобы обратиться за помощью к онлайн-форумам или сообществам, таким как справочное сообщество Google Таблиц или Stack Overflow. Другие пользователи могут предоставить помощь или предложения по вашей конкретной проблеме.

REGEXEXTRACT: Связанные формулы

Вот некоторые связанные формулы, которые могут оказаться полезными при работе с REGEXEXTRACT в Google Таблицах:

1. REGEXMATCH: Эта формула проверяет, соответствует ли данный текст указанному шаблону регулярного выражения. Он возвращает TRUE, если есть совпадение, и FALSE в противном случае.
2. REGEXREPLACE: Эта формула заменяет все вхождения указанного шаблона регулярного выражения в заданном тексте указанным замещающим текстом.
3. РАЗДЕЛИТЬ: Эта формула разбивает заданный текст на отдельные части на основе указанного разделителя. Его можно использовать в сочетании с REGEXEXTRACT для извлечения нескольких экземпляров шаблона из входного текста.
4. НАЙТИ: Эта формула возвращает положение указанного текста в другом тексте. Его можно использовать как альтернативу REGEXEXTRACT, когда вам нужно только найти позицию определенного текста, а не извлечь его.
5. MID: Эта формула извлекает указанное количество символов из заданного текста, начиная с указанной позиции. Его можно использовать как альтернативу REGEXEXTRACT, когда вы знаете точную позицию и длину текста, который хотите извлечь.

С помощью этого подробного руководства вы теперь должны иметь четкое представление о формуле REGEXEXTRACT в Google Sheets. Освоив его синтаксис, примеры, советы и приемы, распространенные ошибки, устранение неполадок и соответствующие формулы, вы будете хорошо подготовлены для решения широкого круга задач по извлечению текста из ваших электронных таблиц.

Как использовать REGEXEXTRACT Функция Google Sheets

REGEXEXTRACT Функция Google Sheets является частью набора функций REGEX, доступных многим пользователям.

Эта функция очень удобна, когда вы пытаетесь найти определенную строку, являющуюся частью более крупной.

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

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

Эта статья охватывает:

Что делает функция REGEXEXTRACT?

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

Синтаксис функции REGEXEXTRACT

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

 REGEXEXTRACT(  text  ,  reg_exp  ) 

Здесь

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

Примечание: Функция всегда возвращает первую часть текста , которая соответствует шаблону в reg_exp .

Применение функции REGEXEXTRACT

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

Вот несколько полезных применений функции REGEXEXTRACT. Вы можете использовать его для:

• извлечения первых или последних нескольких символов из строки
• Извлечь числа из строки
• Извлечение целых слов на основе частичного совпадения
• Извлечь одно из списка слов
• Извлечь содержимое между определенными символами
• Извлечение различных частей URL-адреса
• Извлечение различных частей адресов электронной почты

Давайте посмотрим, как можно использовать REGEXREPLACE в каждом из вышеперечисленных приложений.

Использование функции REGEXEXTRACT для извлечения первых или последних нескольких символов из строки

Давайте сначала посмотрим, как вы можете использовать REGEXREPLACE для извлечения первых или последних нескольких символов или слов из строки.

Допустим, у вас есть следующий список названий книг в столбце A:

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

 =REGEXEXTRACT(A2,"...") 

Вот результат, который вы получите:

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

Таким образом, ваша формула будет:

 =REGEXEXTRACT(A2,"...$") 

Вот результат, который вы получите:

Символ точки представляет собой любой символ, включая пробел или любой другой символ. Таким образом, использование «.+» в регулярном выражении просто извлечет весь текст в ячейке.

Если вы хотите убедиться, что извлекаете только буквенно-цифровые символы, то вместо символа точки вы можете использовать метасимвол \w , который представляет собой одиночный буквенно-цифровой символ (цифру, букву или знак подчеркивания).

Итак, если вы хотите извлечь целое первое слово , вам нужно будет использовать комбинацию «\w+». Это гарантирует, что все символы до первого пробела будут извлечены следующим образом:

 =REGEXEXTRACT(A2,"\w+") 

Аналогичным образом, для извлечения последнего слова формула будет выглядеть так: Строка

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

 =REGEXEXTRACT(A2,"\d+") 

Для следующего списка строк, вот результаты, которые вы получите:

Использование функции REGEXEXTRACT для извлечения целых слов на основе частичного совпадения

Допустим, у вас есть следующий список строк и вы хотите извлечь все номерные знаки, которые начинаются с символов ‘L-‘:

Затем вы можете использовать требуемый шаблон строки, за которым следует «\w+», следующим образом:

 =REGEXEXTRACT(A2,"L-\w+") 

Это даст вам следующий результат:

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

Если вы хотите извлечь первое слово в каждой строке, которая начинается с ‘ bo ‘ и заканчивается на ‘d ‘, вы можете использовать функцию REGEXEXTRACT следующим образом:

 =REGEXEXTRACT(A2," bo\w+d") 

Это даст вам следующий результат:

Обратите внимание, что в последнем примере функция извлекла только первых слов в строке, следующей за образцом ‘bo…d’. Если вместо этого вы хотите, чтобы он извлек последних , вам нужно добавить в конце метасимвол доллара ($):

 =REGEXEXTRACT(A2,"bo\w+d$") 

Это даст вам следующий результат:

Используя функция REGEXEXTRACT для извлечения одного из списка слов

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

Допустим, у вас есть следующий список строк:

Если вы хотите извлечь первое вхождение любого из слов red , blue , green или yellow в ячейке A2, вы можете использовать функцию REGEXEXTRACT следующим образом:

 = REGEXEXTRACT(A2,"красный|синий|зеленый|желтый") 

Это даст вам следующий результат:

Использование функции REGEXEXTRACT для извлечения содержимого между определенными символами

содержимое между определенными символами. Например, предположим, что вы скопировали некоторый текст разметки с веб-сайта и вам нужно извлечь из него только текстовую часть, удалив теги HTML:

Теперь вы можете подумать, что простого использования регулярного выражения ‘>.+<' будет достаточно, чтобы извлечь все содержимое между символами '>‘ и ‘<':

 =REGEXEXTRACT(A2,">. +<") 

Однако это также приведет к извлечению символов вместе с текстом между ними, как показано ниже:

приложить “ .+ ” метасимвол в групповых скобках ‘()’. Это гарантирует, что будет извлечено только содержимое групповых скобок:

 =REGEXEXTRACT(A2,">(.+)<") 

Это даст вам следующий результат:

Использование функции REGEXEXTRACT для извлечения Различные части URL-адреса

Если вы хотите извлечь доменное имя из URL-адреса, вы можете использовать REGEXEXTRACT следующим образом:

 =REGEXEXTRACT(A2,"http.+\ / \ /(.+) \ /" ) 

При этом будет извлечено все содержимое между шаблоном HTTP:// (или HTTPS://) и символом '/'.

Приведенная выше формула даст вам следующий результат:

Если вы в дальнейшем хотите удалить все и извлечь только основное доменное имя (без каких-либо поддоменов или расширений, то ваша функция REGEXEXTRACT может быть уточнена следующим образом :

 =REGEXEXTRACT(A27,"http.+\ / \ / \ w+\.(.+)\.[org|com]") 

Здесь мы убедились, что любые слова до точки и после точки удалено, так как слово после точки может быть любым из слов ‘org’ или ‘com’ , мы указали их в квадратных скобках.

Это даст вам следующий результат:

Использование функции REGEXEXTRACT для извлечения различных частей адреса электронной почты

Как и в предыдущем примере, мы также можем использовать REGEXEXTRACT для извлечения частей адреса электронной почты. Например, предположим, что у вас есть следующий список адресов электронной почты:

Если вы хотите извлечь только часть имени пользователя из адресов электронной почты, вы можете использовать функцию REGEXEXTRACT, чтобы извлечь все, что идет до «@». символ следующим образом:

 =REGEXEXTRACT(A33,"(.+)@") 

Это даст вам следующий результат:

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

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

 =REGEXEXTRACT(A33,"@(.+)") 

Это даст вам следующий результат:

Использование функции REGEXEXTRACT для извлечения определенного шаблона символов

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

Поскольку все телефонные номера в США следуют одному и тому же шаблону, вы можете использовать функцию REGEXEXTRACT следующим образом:

 =REGEXEXTRACT(A40,"\(. .. \)...-....") 

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

Таким образом, вместо «…» вы можете использовать в своем выражении « .{3} ». Это означает, что приведенную выше формулу также можно записать в виде:

 =REGEXEXTRACT(A40,"\(.{3}\).{3}-.{4}") 

У вас есть 3 числа в круглых скобках, за которыми следует еще три цифры, затем дефис и еще 4 цифры.

Это даст вам следующий результат:

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

Таким образом, чтобы разделить результат приведенной выше формулы на три разных столбца, ваша функция REGEXEXTRACT может быть записана как:

 =REGEXEXTRACT(A40,"\((. {3})\)(.{3})-( .{4})") 

Это даст вам следующий результат:

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

REGEXEXTRACT Совет по функции Google Sheets:

Вот несколько важных советов, которые необходимо помнить при использовании функции REGEXEXTRACT.

• Эта функция работает только при вводе текста. Не работает с номерами
• Если вы хотите использовать числа в качестве входных данных (например, номера телефонов), вам необходимо сначала преобразовать их в текст с помощью функции ТЕКСТ.
• Функция REGEXEXTRACT чувствительна к регистру. Поэтому вам нужно будет указать правильный регистр внутри регулярного выражения или преобразовать всю входную строку в верхний или нижний регистр с помощью функций UPPER или LOWER.

Функция REGEXEXTRACT может найти множество применений, если вы научитесь эффективно ее использовать.

This entry was posted in Популярное