Замена текста с помощью SUBSTITUTE

В формулах часто требуется найти и заменить данные: удалить дефисы из телефонных номеров, заменить слово или убрать случайные символы.

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

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

SUBSTITUTE принимает до четырёх аргументов:

• текст исходная строка или ячейка
• старый текст текст, который нужно найти
• новый текст текст, которым его нужно заменить
• номер вхождения (необязательно) какое вхождение заменить

Общий вид: =SUBSTITUTE(text, old_text, new_text, [instance_num]). Если последний аргумент не указан, заменяются все вхождения.

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

Если в A2 хранится значение 555-123-4567, приведённая ниже формула удаляет все дефисы и возвращает 5551234567.

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

SUBSTITUTE работает и с целыми словами, а не только с отдельными символами.

Если в A2 хранится значение Годовой отчёт 2023, Вы можете обновить год, заменив слово:

Результат — Годовой отчёт 2024. Каждое совпадающее вхождение в ячейке изменяется.

Важно: SUBSTITUTE сопоставляет текст точно, включая регистр.

Если в A2 хранится значение Кот кот CAT, то =SUBSTITUTE(A2, "cat", "dog") изменит только написанное строчными буквами кот, и получится Кот собака CAT.

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

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

Если в A2 хранится значение a-b-c-d и Вы хотите изменить только второй дефис, задайте для номера вхождения значение 2.

Результат — a-b/c-d: только второй дефис превратился в косую черту.

Представьте импортированную сумму, сохранённую как текст: $1,250.00. Чтобы превратить её в пригодное для использования число, необходимо удалить знак доллара и запятую.

Вложите два вызова SUBSTITUTE один в другой, чтобы удалить оба символа.

Внутренний вызов удаляет запятую, а внешний — знак доллара, оставляя значение 1250.00.

Можно вложить столько вызовов SUBSTITUTE, сколько необходимо, чтобы каждый из них удалял отдельный символ.

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

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

Не путайте SUBSTITUTE с похожей функцией REPLACE.

• SUBSTITUTE заменяет текст, сопоставляя его содержимое (найти это слово и заменить его).
• REPLACE заменяет текст по позиции (заменить 4 символа, начиная с позиции 5).

Используйте SUBSTITUTE, когда знаете, что нужно найти, и REPLACE, когда знаете, где оно находится.

Есть и полезный дополнительный приём: объедините SUBSTITUTE с LEN, чтобы посчитать, сколько раз встречается что-либо.

Вычтите длину текста после удаления символа из исходной длины. Если в A2 хранится значение a,b,c,d, этот приём посчитает запятые и вернёт 3.

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

Предположим, в A2 хранятся неаккуратные номера с точками и пробелами, например 555.123 4567. Вы хотите получить единый формат с дефисами: 555-123-4567.

Сначала удалите точки и пробелы, а затем можно было бы вставить дефисы; пока просто приведите разделители к дефисам.

Вложенные замены стандартизируют любые случайные разделители.

Проверьте, насколько хорошо Вы понимаете функцию SUBSTITUTE.

Вы научились находить и заменять текст в формулах:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Используйте "" в качестве нового текста, чтобы удалять символы.
• Функция учитывает регистр; при необходимости сначала приведите регистр к единому виду.
• Необязательный номер вхождения позволяет выбрать одно вхождение.
• Вкладывайте вызовы, чтобы удалить несколько символов; объединяйте функцию с LEN, чтобы считать вхождения.

Вы завершили раздел «Объединение и изменение текста». Отличная работа — теперь Вы умеете создавать чистые и единообразные данные!