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

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