Извлечение шаблонов с помощью REGEXEXTRACT

REGEXMATCH сообщает, есть ли в тексте заданный шаблон. Часто требуется получить сам найденный текст — например, число из Order #4821 или домен из адреса электронной почты.

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

Это ещё одна функция Google Таблиц. Это один из самых удобных способов извлекать структурированные фрагменты из неаккуратных импортированных данных.

REGEXEXTRACT(text, pattern) возвращает первую подстроку из text, соответствующую pattern.

Если совпадений нет, функция возвращает ошибку #N/A, а не пустое значение — это важно помнить.

Формула ниже извлекает первую последовательность цифр из A2. Для Order 4821 она возвращает 4821.

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

Чтобы преобразовать результат в настоящее число, оберните его в VALUE. Приведённая ниже формула извлекает цифры и преобразует их, чтобы с результатом можно было выполнять математические операции.

Шаблоны предназначены не только для цифр. Чтобы извлечь префикс из прописных букв, используйте класс с квантификатором.

Приведённая ниже формула извлекает начальные буквы из кода вроде INV2024 и возвращает INV. [A-Z]+ означает одну или несколько идущих подряд прописных букв.

Предположим, в A2 находится SKU-4821-RED, а вам нужна только числовая часть посередине. Её расположение можно описать с помощью окружающего контекста внутри шаблона.

Формула ниже ищет цифры между двумя дефисами и возвращает 4821. Буквальные дефисы направляют механизм к нужной последовательности цифр.

Обратите внимание на круглые скобки в последней формуле: (\d+). Это захватывающая группа.

Когда шаблон содержит захватывающую группу, REGEXEXTRACT возвращает только содержимое круглых скобок, а не совпадение целиком. Поэтому -(\d+)- возвращает 4821, отбрасывая дефисы.

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

Из sam@coddykit.com нужно получить домен coddykit.com. Сопоставьте @, затем захватите всё после него до конца.

Формула ниже возвращает часть A2, расположенную после символа «@». (.+) захватывает один или несколько любых символов, а $ привязывает совпадение к концу строки.

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

Формула ниже захватывает начальные символы A2, не являющиеся пробелами. Для John Smith она возвращает John. [^ ]+ означает один или несколько символов, не являющихся пробелом.

Поскольку неудачное извлечение вызывает #N/A, протягивание формулы вниз по столбцу со смешанными данными может усеять таблицу ошибками.

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

Вертикальная черта | означает «или». Она позволяет одному шаблону сопоставлять несколько вариантов. Это особенно удобно, когда код может начинаться с разных префиксов.

Формула ниже извлекает слово, обозначающее статус, из A2: OPEN, CLOSED или HOLD. Возвращается первый найденный вариант.

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

Представьте, что в столбце A находятся строки заказов вроде Order 4821 - shipped. Формула ниже, протянутая вниз по столбцу B, извлекает только номер заказа из каждой строки, превращая неаккуратный исходный текст в аккуратный столбец, похожий на числовой.

Проверьте, что Вы знаете об извлечении совпадений.

Вы узнали, что REGEXEXTRACT(text, pattern) возвращает первую совпавшую подстроку в Google Таблицах. Основные выводы:

• Результат всегда является текстом; для получения чисел оберните его в VALUE
• Захватывающие группы в круглых скобках возвращают только внутренний фрагмент
• При отсутствии совпадения возвращается #N/A, поэтому используйте IFERROR
• | позволяет одному шаблону сопоставлять несколько вариантов

Далее Вы выйдете за рамки чтения и начнёте изменять текст непосредственно с помощью REGEXREPLACE.