ПОДСТАВИТЬ (анг. SUBSTITUTE)

Функция ПОДСТАВИТЬ (на английском SUBSTITUTE) заменяет в тексте один фрагмент на другой: =ПОДСТАВИТЬ(A2;".";",") заменит все точки на запятые, =ПОДСТАВИТЬ(A2;" ";"") удалит все пробелы, а четвёртый аргумент меняет только нужное по счёту вхождение — =ПОДСТАВИТЬ(A2;" ";"-";2). Функция различает регистр. Несколько замен делают вложенными ПОДСТАВИТЬ, а вместе с ДЛСТР она считает, сколько раз символ или слово встречается в ячейке.

Что делает функция ПОДСТАВИТЬ?

Функция ПОДСТАВИТЬ позволяет заменить все вхождения или N-ное вхождение подстроки в тексте на другой текст. Подстрокой может быть как один символ, так и несколько.

Функция является в некоторой степени аналогом процедуры Найти и Заменить в Excel, с некоторыми отличиями:

• Что очевидно и является базовым отличием, она является функцией, т.е. пересчитывается автоматически, в отличие от одноразового характера процедур, и выводит результат в другую ячейку, в то время, как процедура меняет результат на месте;
• Функция ПОДСТАВИТЬ всегда чувствительна к регистру. В процедуре "найти и заменить" чувствительность к регистру опциональна;
• Функция, как это ни странно, не поддерживает подстановочные символы ("*" и "?");
• Зато позволяет осуществлять массовую замену нескольких (до 64 в последней версии Excel) значений одновременно;
• Еще одно преимущество функции в том, что она позволяет заменить не все вхождения, а определенное по порядку в строке, чего не позволяет сделать процедура.

Синтаксис ПОДСТАВИТЬ

=ПОДСТАВИТЬ(Ячейка_или_текст;Заменяемый_текст;Чем_заменяем;[Порядковый_номер_заменяемого])

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

ПОДСТАВИТЬ: формат результата и исходных данных

Характерные особенности функции ПОДСТАВИТЬ:

• Пустая ячейка воспринимается как строка нулевой длины ("").
• Логические значения конвертируются в текст с сохранением регистра.
• Все вариации числовых форматов (дата, время, дата-время, проценты, финансовый формат) конвертируются в соответствующее им натуральное число в текстовом формате, поэтому для сохранения исходного форматирования может понадобиться функция ТЕКСТ.

Примеры формул с функцией ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ необычайно популярна и может использоваться:

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

Рассмотрю несколько примеров ситуаций, в которых лично использовал функцию.

ПОДСТАВИТЬ с несколькими условиями (транслитерация)

Заменить кириллицу на транслит автоматически с помощью формулы? Можно! Правда, длина формулы настолько велика, что ее лучше сохранить где-нибудь, а не составлять самостоятельно. Потому что это формула с 64 условиями (уровнями вложенности). Подробнее - по ссылке в начале этого абзаца.

ПОДСТАВИТЬ, чтобы посчитать слова

Тот случай, когда функция используется не по своему назначению, т.к. целевым значением является число, а не текст. Лайфхак стар как мир, но не теряет своей актуальности.

Сколько слов в ячейке? На 1 больше, чем пробелов между ними. Это будет справедливо всегда, если разделителем слов считается только пробел, и если пробелов между каждыми двумя словами не более 1. Чтобы обезопасить себя от лишних, можно воспользоваться функцией СЖПРОБЕЛЫ.

Как посчитать пробелы в строке? Как и любой символ, можно посчитать их, подсчитав длину строки с ними и без них, и вычислив разницу. Как получить строку без них? Конечно, с помощью функции ПОДСТАВИТЬ.

Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. ДЛСТР измеряет длину строк:

=ДЛСТР(СЖПРОБЕЛЫ(A1)) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1

У формулы есть побочный эффект - для пустой ячейки она возвращает 1, поэтому, если пустые ячейки могут присутствовать, формулу выше придется "обернуть" выражением с Excel функцией ЕСЛИ:

=ЕСЛИ(A1="";0;ДЛСТР(СЖПРОБЕЛЫ(A1)) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1)

Заменить пробелы на переносы строк

Часто возникает задача заменить пробелы в строке на переносы строк, и наоборот. Здесь будет полезна комбинация функций ПОДСТАВИТЬ и СИМВОЛ. Так будет выглядеть формула, заменяющая все пробелы в строке на переносы строк.

=ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))

Как видно из формулы, код символа переноса строки - 10.

Обратите внимание, что результат может отображаться сплошной строкой, если не включить опцию "Перенести текст".

ПОДСТАВИТЬ, чтобы посчитать встречаемость в тексте

Узнать, сколько раз встречается фрагмент в тексте, можно довольно просто с помощью комбинации функций ПОДСТАВИТЬ и ДЛСТР.

1. Первая (ПОДСТАВИТЬ) позволяет создать строку без указанного фрагмента, заменяя его на пустоту.
2. Вторая (ДЛСТР) - измерить длину результата.
3. Если вычесть из исходной длины полученную, и разделить на длину этого фрагмента, можно получить его встречаемость.
4. Если заведомо известно, что это один символ, процесс деления можно опустить, т.к. результат от деления на 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};"")))

Несколько замен одной формулой

Чтобы заменить сразу несколько фрагментов, ПОДСТАВИТЬ вкладывают друг в друга: результат внутренней замены становится текстом для внешней. Так же устроена формула транслитерации выше, только уровней в ней 64.

Другие примеры с функцией ПОДСТАВИТЬ

Удалить цифры из ячеек

Удалить латиницу из текста

Добавить символ к каждому слову в ячейке

Удалить последнее слово в ячейке

Почему ПОДСТАВИТЬ не заменяет — частые ошибки

• Ничего не заменилось. Не совпал регистр («Руб.» и «руб.») или в тексте неразрывный пробел вместо обычного — замените СИМВОЛ(160).
• Число стало текстом. ПОДСТАВИТЬ всегда возвращает текст. Для чисел поставьте перед формулой два минуса: =--ПОДСТАВИТЬ(A2;".";",").
• Заменилась часть другого слова. ПОДСТАВИТЬ ищет фрагмент, а не слово. Для слова целиком добавьте пробелы: ПОДСТАВИТЬ(" "&A2&" ";" кот ";" пёс ").
• Нужно заменить по позиции, а не по тексту. Для этого есть ЗАМЕНИТЬ.

Функция ПОДСТАВИТЬ — частые вопросы

Как работает функция ПОДСТАВИТЬ в Excel?

Ищет в тексте фрагмент и заменяет его: =ПОДСТАВИТЬ(A2;"старое";"новое").

Как называется ПОДСТАВИТЬ на английском?

SUBSTITUTE. Синтаксис тот же: =SUBSTITUTE(text,old_text,new_text,[instance_num]).

Как заменить несколько разных значений?

Вложить ПОДСТАВИТЬ друг в друга или использовать массовую замену.

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

Укажите номер четвёртым аргументом: =ПОДСТАВИТЬ(A2;" ";"-";2).

Чем ПОДСТАВИТЬ отличается от ЗАМЕНИТЬ?

ПОДСТАВИТЬ ищет текст, ЗАМЕНИТЬ меняет символы по позиции.

Итоги: функция ПОДСТАВИТЬ в Excel

Надеюсь, на этой странице вы нашли то, что искали. ПОДСТАВИТЬ — главная функция для замены и удаления фрагментов текста формулой.