Пример функции ПОДСТАВИТЬ в формуле Excel для работы с текстом

Часто при работе в Excel возникает необходимости динамически заменять один текст другим текстом. Для решения такого рода задач была создана функция ПОДСТАВИТЬ.

Исправляем ошибки в тексте с помощью функции ПОДСТАВИТЬ

Одним из примеров применения функции ПОДСТАВИТЬ является наличие дефиса в русскоязычных словах или апострофа перед суффиксом ‘S в английских названиях фирм, которые искажают работу функции ПРОПНАЧ. Данная функция должна только первые буквы в словах изменять на большие, прописные. А в результате в исходном слове где иметься апостроф функция возвращает 2 большие буквы, что искажает результат ее работы:

Дело в том, что текстовая функция ПРОПНАЧ работает по принципу замены всех первых символов на большую букву, которые находятся после символов, которые не соответствуют буквам: !,?,-,',*,/, и т.д. Поэтому если внутри слова находится символы, которые не являются буквами работа функции ПРОПНАЧ будет искажена.

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

В основе выше указанной формулы лежит функция ПОДСТАВИТЬ, которая требует заполнения 3-х обязательных из 4-х аргументов:

1. Текст – исходный текст или ссылка на ячейку с исходным текстом в котором следует выполнить замену символов.
2. Стар_текст – старый заменяемый текст.
3. Нов_текст – новый текст на который следует заменить старый.
4. Номер_вхождения – опциональный необязательный аргумент. Если строка содержит несколько одинаковых старых заменяемых текстов, то с помощью этого аргумента можно указать какой именно по очереди заменить старый текст. Если этот аргумент опущен, тогда заменяются все найденные одинаковые старые заменяемые тексты в исходной строке.

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



Часть 1:

Сначала функция ПОДСТАВИТЬ используется для замены апострофа на временный текст «zzz». На первый взгляд такой подход выглядит не профессиональным и бессмысленным, но это не так. Чтобы получить правильный результат при работе функции ПОДСТАВИТЬ и при этом не удалять из текста символы отличные от букв, нам придется пойти на небольшую хитрость в Excel. Сохраняя в оригинале наличие всех символов в исходном тексте. Первая часть формулы для второй части вернет такой результат для следующей обработки:

MICHAELzzzS DELI

Часть 2:

Вторая часть формулы охватывает первую часть и работает с тем, что первая формула возвратила ей в результате, а именно ту же исходную строку, но с временным текстом «zzz» вместо апострофа «'». Далее в работу вступает пострадавшая функция ПРОПНАЧ, которая заменяет во всех словах первые буквы на большие. В результате получим текстовую строку следующего вида:

Michaelzzzs Deli

Часть 3:

Полезный совет! Вместо ввода символа одинарной кавычки апострофа (') более читабельно в формуле будет выглядеть функция СИМВОЛ(39), которая возвращает тот же символ по коду таблицы Unicode. О чем свидетельствует код 39 в аргументе функции СИМВОЛ. То есть следующая более читабельная формула возвращает тот же результат:

А после осталось лишь функции ПОДСТАВИТЬ обратно заменить временный текст «zzz» на апостроф «'». Чтобы слова приобрели свой оригинальный вид по наличию тех же символов.

Michael's Deli

Иногда приходиться находить собственные альтернативные решения в Excel если нет стандартных предусмотренных инструментов.