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

Что делает эта функция?

Функция ПОДСТАВИТЬ позволяет заменить все вхождения или 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};"")))

Другие примеры

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

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

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

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