Раздел функций | Текстовые |
Название на английском | SUBSTITUTE |
Волатильность | Не волатильная |
Похожие функции | ЗАМЕНИТЬ |
Что делает эта функция?
Функция ПОДСТАВИТЬ позволяет заменить все вхождения или N-ное вхождение подстроки в тексте на другой текст. Подстрокой может быть как один символ, так и несколько.
Функция является в некоторой степени аналогом процедуры Найти и Заменить в Excel, с некоторыми отличиями:
- Что очевидно и является базовым отличием, она является функцией, т.е. пересчитывается автоматически, в отличие от одноразового характера процедур, и выводит результат в другую ячейку, в то время, как процедура меняет результат на месте;
- Функция ПОДСТАВИТЬ всегда чувствительна к регистру. В процедуре “найти и заменить” чувствительность к регистру опциональна;
- Функция, как это ни странно, не поддерживает подстановочные символы (“*” и “?”);
- Зато позволяет осуществлять массовую замену нескольких (до 64 в последней версии Excel) значений одновременно;
- Еще одно преимущество функции в том, что она позволяет заменить не все вхождения, а определенное по порядку в строке, чего не позволяет сделать процедура.
Синтаксис
=ПОДСТАВИТЬ(Ячейка_или_текст;Заменяемый_текст;Чем_заменяем;[Порядковый_номер_заменяемого])
Последний аргумент является необязательным (поэтому указан в квадратных скобках в примере), если его не указывать, будут заменены все вхождения.
Форматирование
Характерные особенности функции ПОДСТАВИТЬ:
- Пустая ячейка воспринимается как строка нулевой длины (“”).
- Логические значения конвертируются в текст с сохранением регистра.
- Все вариации числовых форматов (дата, время, дата-время, проценты, финансовый формат) конвертируются в соответствующее им натуральное число в текстовом формате, поэтому для сохранения исходного форматирования может понадобиться функция ТЕКСТ.
Примеры
Функция ПОДСТАВИТЬ необычайно популярна и может использоваться:
- по прямому назначению, когда необходимым результатом является сама строка с заменнными фрагментами
- как вспомогательная функция в сложносоставных формулах, когда результатом вычислений является даже не строка, а число или логическое значение
Рассмотрю несколько примеров ситуаций, в которых лично использовал функцию.
ПОДСТАВИТЬ с несколькими условиями (транслитерация)
Заменить кириллицу на транслит автоматически с помощью формулы? Можно! Правда, длина формулы настолько велика, что ее лучше сохранить где-нибудь, а не составлять самостоятельно. Потому что это формула с 64 условиями (уровнями вложенности). Подробнее – по ссылке в начале этого абзаца.

ПОДСТАВИТЬ, чтобы посчитать слова
Тот случай, когда функция используется не по своему назначению, т.к. целевым значением является число, а не текст. Лайфхак стар как мир, но не теряет своей актуальности.
Сколько слов в ячейке? На 1 больше, чем пробелов между ними. Это будет справедливо всегда, если разделителем слов считается только пробел, и если пробелов между каждыми двумя словами не более 1. Чтобы обезопасить себя от лишних, можно воспользоваться функцией СЖПРОБЕЛЫ.
Как посчитать пробелы в строке? Как и любой символ, можно посчитать их, подсчитав длину строки с ними и без них, и вычислив разницу. Как получить строку без них? Конечно, с помощью функции ПОДСТАВИТЬ.
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. ДЛСТР измеряет длину строк:
=ДЛСТР(СЖПРОБЕЛЫ(A1)) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1
У формулы есть побочный эффект – для пустой ячейки она возвращает 1, поэтому, если пустые ячейки могут присутствовать, формулу выше придется “обернуть” выражением с Excel функцией ЕСЛИ:
=ЕСЛИ(A1="";0;ДЛСТР(СЖПРОБЕЛЫ(A1)) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1)
Заменить пробелы на переносы строк
Часто возникает задача заменить пробелы в строке на переносы строк, и наоборот. Здесь будет полезна комбинация функций ПОДСТАВИТЬ и СИМВОЛ. Так будет выглядеть формула, заменяющая все пробелы в строке на переносы строк.
=ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))
Как видно из формулы, код символа переноса строки – 10.
Обратите внимание, что результат может отображаться сплошной строкой, если не включить опцию “Перенести текст”.

ПОДСТАВИТЬ, чтобы посчитать встречаемость в тексте
Узнать, сколько раз встречается фрагмент в тексте, можно довольно просто с помощью комбинации функций ПОДСТАВИТЬ и ДЛСТР.
- Первая (ПОДСТАВИТЬ) позволяет создать строку без указанного фрагмента, заменяя его на пустоту.
- Вторая (ДЛСТР) – измерить длину результата.
- Если вычесть из исходной длины полученную, и разделить на длину этого фрагмента, можно получить его встречаемость.
- Если заведомо известно, что это один символ, процесс деления можно опустить, т.к. результат от деления на 1 не изменится.
Так выглядит формула, считающая в ячейке A1 количество вхождений 1-символьного фрагмента из ячейки B1:
=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(A1;B1;""))
Так – для подсчета встречаемости конкретного символа в ячейке, в данном случае – косой черты, что позволяет, например, посчитать уровень вложенности директории URL по ее адресу:
=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(A1;"/";""))
А так – для фрагмента длиной более 1 символа, например, если нужно посчитать, сколько раз встречается конкретное слово в тексте:
=(ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(A1;B1;""))) / ДЛСТР(B1)
ПОДСТАВИТЬ для подсчета цифр в тексте
Представим, что наша задача – провалидировать список номеров телефонов на количество цифр в нем. Корректным будем считать номер, в котором 11 цифр.
При этом номер может быть указан в любом формате – со скобками, дефисами, плюсом в начале, с пробелами и без. Помимо самого номера в строке могут присутствовать другие пометки, например, контактное лицо, является телефон мобильным или нет.
Очевидно, что функция ДЛСТР не подойдет в таком случае, как и не удастся сходу удалить все лишние символы, чтобы оставить в ячейках только цифры.
Комбинация функций ПОДСТАВИТЬ и ДЛСТР в формуле массива с функцией СУММ позволит решить данную задачу:
=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};"")))

Другие примеры
Добавить символ к каждому слову в ячейке
Удалить последнее слово в ячейке
Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Добрый день! Формула “=ДЛСТР(A1) – ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);” “;””)) + 1″ не выдает ожидаемого результата. Может ее нужно записать как “=ДЛСТР(СЖПРОБЕЛЫ(A1)) – ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);” “;””)) + 1″?
Спасибо, заметили недочет! Если в строке есть лишние пробелы, подсчет будет некорректный. Разумеется, СЖПРОБЕЛЫ нужно применять в обоих случаях – и до замены и после.
Поправил.
ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};””) заменяет только первый символ, то есть “1”
Не совсем так. Формула заменяет все десять, просто в десяти разных виртуальных ячейках – и если выводить в одну, то и увидите только одну. Это формула массива, так они работают.