Замена шаблонов с помощью REGEXREPLACE

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

REGEXREPLACE находит каждую часть текста, соответствующую шаблону, и заменяет её выбранным Вами текстом.

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

REGEXREPLACE(text, pattern, replacement) принимает три аргумента: исходный текст, шаблон для поиска и строку замены.

В отличие от REGEXEXTRACT, она заменяет каждое найденное совпадение, а не только первое.

Формула ниже удаляет все цифры из A2, заменяя каждую цифру пустой строкой. Room 204 превращается в Room .

Распространённый приём — удалять символы, заменяя их пустой строкой "".

Формула ниже удаляет всё, что не является цифрой, оставляя только числа. (555) 123-4567 превращается в 5551234567. Класс [^\d] означает любой символ, не являющийся цифрой, а + позволяет сразу захватывать их последовательности.

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

Формула ниже сопоставляет один или несколько пробельных символов и заменяет каждую последовательность одним пробелом. Позже объедините её с TRIM, чтобы очистить также границы строки.

Текст замены может ссылаться на захваченные группы с помощью $1, $2 и так далее. $1 означает то, чему соответствовала первая захватывающая группа.

Это позволяет менять порядок или форматировать текст. Формула ниже превращает Smith, John в John Smith, захватывая два имени и меняя их порядок.

Предположим, что в A2 находится чистая строка из 10 цифр, например 5551234567, и Вы хотите получить (555) 123-4567.

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

Можно заменять и обычные слова. Чтобы привести сокращения к единому виду, сопоставьте слово и замените его.

Формула ниже заменяет каждое St. на Street в адресе. Не забудьте экранировать точку как \., чтобы она соответствовала именно точке, а не любому символу.

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

Формула ниже заменяет color или Color на colour. Класс [Cc] соответствует одному из вариантов регистра первой буквы.

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

Формула ниже сопоставляет все цифры, расположенные перед последними четырьмя, и заменяет каждую на X. Для 1234567890123456 получается XXXXXXXXXXXX3456.

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

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

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

Предположим, что в столбце A находятся названия товаров с лишними кодами в конце, например Chair (SKU-4821). Формула ниже удаляет код в скобках из каждой строки, оставляя только Chair . Добавьте вокруг неё TRIM, чтобы убрать оставшийся пробел.

Проверьте, насколько хорошо Вы освоили замену по шаблону.

Вы узнали, что REGEXREPLACE(text, pattern, replacement) заменяет каждое совпадение в Google Таблицах. Основные выводы:

• Используйте "" для удаления ненужных символов
• [^\d] и \s+ — основные инструменты очистки
• Захватывающие группы можно повторно использовать в замене как $1, $2
• Экранируйте специальные символы, используемые буквально, например \.
• Вкладывайте вызовы, чтобы объединять несколько этапов очистки

Далее Вы объедините регулярные выражения с SPLIT и CLEAN, чтобы разделить и окончательно привести в порядок импортированные данные.