Функция 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;" ";"")))

Урок подготовлен для Вас командой сайта office-guru.ru

Источник: http://blog.contextures.com/archives/2011/01/28/30-excel-functions-in-30-days-27-substitute/