Функция SUBSTITUTE в Excel — замена текста в ячейке

Функция SUBSTITUTE в Excel находит подстроку в тексте ячейки и заменяет её другой подстрокой. Она чувствительна к регистру и ищет точные совпадения. Можно указать, заменить ли конкретный экземпляр подстроки или все её появления.

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

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: сама строка или ссылка на ячейку, в которой нужно искать.
• old_text: подстрока, которую нужно заменить.
• new_text: строка, на которую будет произведена замена.
• [instance_num]: необязательный номер вхождения old_text, которое нужно заменить; если не указан — заменяются все вхождения.

Примечание: формально Excel использует термин text, но функция применима и к символам, и к числам, представленным как текст. Чтобы удалить символ, замените его на пустую строку “”.

Пример: замена кода страны в номере телефона

В примере ниже в телефонном номере неправильно указан код страны (+98). Задача — заменить только первый фрагмент 98 на 1, чтобы получить код +1.

1. Выберите ячейку, где нужен результат — в примере это A2 для исходного значения и ячейка вывода B2 (или просто B2 как целевая).
2. Введите формулу в строке формул и нажмите Enter:

=SUBSTITUTE(A2, "98", "1", 1)

Эта формула смотрит в A2, находит подстроку “98” и заменяет её на “1”. Параметр instance_num = 1 означает, что заменится только первое вхождение “98”.

3. Если убрать последний аргумент (instance_num), Excel заменит каждое появление “98” на “1” во всей строке.

Вложенные замены

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

Пример: в ячейке A2 есть три аббревиатуры, которые нужно заменить на имена. Введите в C2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "T", "Terry"), "F", "Frank"), "J", "John")

Вложенные вызовы выполняются от внутреннего к внешнему: innermost смотрит в A2, следующий также использует результат предыдущего и т. д.

Когда SUBSTITUTE подходит, а когда лучше использовать другое решение

• Когда подходит:

  • Нужно заменить все точные вхождения подстроки или именно n‑е вхождение.
  • Требуется чувствительная к регистру замена на уровне текста.
  • Нужны формулы без макросов и без внешних надстроек.

• Когда не подходит / альтернативы:

  • Замена на основе позиции (символы по индексу): используйте REPLACE или функции LEFT/MID/RIGHT.
  • Массовая замена разных фрагментов в большом диапазоне: рассмотрите Поиск и замена (Ctrl+H) или Power Query для многократных правил.
  • Сложные шаблоны (регулярные выражения): в стандартном Excel regex нет; в Office 365 можно комбинировать новые текстовые функции, либо использовать Power Query/скрипты.

Практические советы и распространённые ошибки

• SUBSTITUTE чувствителен к регистру: “a” и “A” — разные символы.
• При замене пустой строки используйте “” (две кавычки) для удаления символа.
• Если old_text не найден — возвращается исходный текст без изменений.
• Для массовых преобразований сначала работайте в вспомогательной колонке, чтобы сохранить исходные данные.
• Проверяйте пробелы: часто заменяемый фрагмент содержит невидимые пробелы (неразрывные пробелы), которые мешают совпадению.

Мини‑методика безопасной замены данных

1. Скопируйте исходный столбец в помощник (backup‑колонка).
2. Выполните замену формулой SUBSTITUTE в новой колонке.
3. Проверьте на выборке — 5–10 строк с разными кейсами.
4. Если всё верно, скопируйте и вставьте значения поверх исходных или оставьте колонку вывода.
5. При ошибке — отмена (Ctrl+Z) или восстановление из backup‑колонки.

Критерии приёмки

• Формула корректно заменяет только указанные вхождения и не изменяет другие части строки.
• Результаты проверены на типичных и граничных данных (пустые строки, отсутствующее old_text, разные регистры).
• Исходные данные сохранены до финального принятия изменений.

Ролевые чек‑листы

Для аналитика:

• Проверить примеры данных и частые шаблоны.
• Протестировать формулу на выборке.

Для разработчика/ETL‑специалиста:

• Оценить целесообразность Power Query/скрипта для больших объёмов.
• Автоматизировать замены в конвейере данных.

Для обычного пользователя:

• Создать резервную колонку перед массовыми правками.
• Использовать Поиск и замена для простых случаев.

Примеры тестовых случаев

• Вход: “98-123-98”; SUBSTITUTE(A1, “98”, “1”) → “1-123-1”.
• Вход: “98-123-98”; SUBSTITUTE(A1, “98”, “1”, 1) → “1-123-98”.
• Вход: “test”; SUBSTITUTE(A1, “x”, “y”) → “test” (нет изменений).

Короткий словарь терминов

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

Часто задаваемые вопросы

Как заменить только определённое вхождение подстроки?

Используйте четвёртый аргумент instance_num в SUBSTITUTE — это номер вхождения, которое нужно заменить.

Можно ли одной формулой заменить разные подстроки одновременно?

Непосредственно одной функцией SUBSTITUTE — нет, но можно вложить несколько SUBSTITUTE или использовать Power Query/скрипты для более масштабных правил.

Итог

Функция SUBSTITUTE — простой и надёжный инструмент для текстовых замен в Excel, когда нужна чувствительная к регистру подстрочная замена или замена конкретного экземпляра. Для позиционных замен, массовых трансформаций или сложных шаблонов стоит рассмотреть REPLACE, Поиск и замена, Flash Fill или Power Query.

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