30 функций Excel за 30 дней: ПОДСТАВИТЬ (SUBSTITUTE)

Вчера в марафоне 30 функций Excel за 30 дней мы использовали функцию OFFSET (СМЕЩ), чтобы возвратить ссылку, а также увидели, что она очень похожа на функцию INDEX (ИНДЕКС). Кроме этого мы узнали, что функция OFFSET (СМЕЩ) пересчитывается при любом изменении данных на листе, а INDEX (ИНДЕКС) только при изменении своих аргументов.

В 27-й день марафона мы займёмся изучением функции SUBSTITUTE (ПОДСТАВИТЬ). Как и функция REPLACE (ЗАМЕНИТЬ), она заменяет старый текст на новый, а также может сделать многочисленные замены одинакового текста в строке.

Как показывает практика, в некоторых ситуациях быстрее и проще использовать команды Find/Replace (Найти/Заменить), когда необходимо сделать замену чувствительную к регистру.

Итак, давайте внимательно изучим информацию и примеры по SUBSTITUTE (ПОДСТАВИТЬ). Если у Вас есть другие сведения или примеры использования этой функции, пожалуйста, делитесь ими в комментариях.

Функция 27: SUBSTITUTE (ПОДСТАВИТЬ)

Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет старый текст на новый текст внутри текстовой строки. Функция заменит все повторения старого текста, пока не будет выполнено определённое условие. Она чувствительна к регистру.

Как можно использовать функцию SUBSTITUTE (ПОДСТАВИТЬ)?

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

• Изменять название региона в заголовке отчёта.
• Удалить непечатаемые символы.
• Заменить последний символ пробела.

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

Функция SUBSTITUTE (ПОДСТАВИТЬ) имеет вот такой синтаксис:

SUBSTITUTE(text,old_text,new_text,instance_num)
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

• text (текст) – текстовая строка или ссылка, где будет осуществлена замена текста.
• old_text (стар_текст) – текст, который надо заменить.
• new_text (нов_текст) – текст, который будет вставлен.
• instance_num (номер_вхождения) – номер вхождения текста, который нужно заменить (не обязательный аргумент).

Ловушки SUBSTITUTE (ПОДСТАВИТЬ)

• Функция SUBSTITUTE (ПОДСТАВИТЬ) может заменить все повторения старого текста, поэтому, если Вам нужно заменить только определённое вхождение, используйте аргумент instance_num (номер_вхождения).
• Если нужно сделать замену без учёта регистра, используйте функцию REPLACE (ЗАМЕНИТЬ).

Пример 1: Изменяем название региона в заголовке отчёта

При помощи функции SUBSTITUTE (ПОДСТАВИТЬ), Вы можете создать заголовок отчёта, который автоматически изменяется в зависимости от того, какой регион выбран. В этом примере заголовок отчёта введён в ячейке C11, которой присвоено имя RptTitle. Символы yyy в тексте заголовка будут заменены на название региона, выбранное в ячейке D13.

=SUBSTITUTE(RptTitle,"yyy",D13)
=ПОДСТАВИТЬ(RptTitle;"yyy";D13)

Пример 2: Удаляем непечатаемые символы

При копировании данных с веб-сайта, в тексте могут оказаться лишние символы пробелов. Текст может содержать, как обычные пробелы (символ 32), так и не неразрывные (символ 160). Когда Вы сделаете попытку удалить их, то обнаружите, что функция TRIM (СЖПРОБЕЛЫ) не способна удалить неразрывные пробелы.

К счастью, Вы можете использовать функцию SUBSTITUTE (ПОДСТАВИТЬ), чтобы заменить каждый неразрывный пробел на обычный, а затем, используя функцию TRIM (СЖПРОБЕЛЫ), удалить все лишние пробелы.

=TRIM(SUBSTITUTE(B3,CHAR(160)," "))
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(B3;СИМВОЛ(160);" "))

Пример 3: Заменяем последний символ пробела

Чтобы не заменять все вхождения текстовой строки, Вы можете использовать аргумент instance_num (номер_вхождения) для указания, какое из вхождений нужно заменить. В следующем примере представлен список ингредиентов для рецепта, где требуется заменить только последний символ пробела.

Функция LEN (ДЛСТР) в ячейке C3 подсчитывает количество символов в ячейке B3. Функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет все символы пробела пустой строкой, а вторая функция LEN (ДЛСТР) находит длину обработанной строки. Длина оказывается на 2 символа короче, значит в строке было 2 пробела.

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))
=ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";""))

В ячейке D3 функция SUBSTITUTE (ПОДСТАВИТЬ) заменяет второй символ пробела новой строкой » | «.

=SUBSTITUTE(B3," "," | ",C3)
=ПОДСТАВИТЬ(B3;" ";" | ";C3)

Чтобы не использовать две формулы для решения этой задачи, Вы можете объединить их в одну длинную:

=SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))
=ПОДСТАВИТЬ(B3;" ";" | ";ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";"")))