ПОДСТАВИТЬ в Excel (функция)

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

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

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

Разглядим работу функции на примерах и её индивидуальности, которых, вообщем, совершенно незначительно.

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

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

В обобщённом виде формула смотрится таковым образом:
ПОДСТАВИТЬ(текст; стар_текст; нов_текст; [номер_вхождения])

В квадратных скобках указаны аргументы, которые можно пропустить. Предназначение аргументов описано ниже в перечне.

«текст» — Обычно это ссылка на ячейку, в какой находится обрабатываемый текст. Можно указать лишь одну ячейку. Также можно указать и константу, заключённую в двойные кавычки, которые в Excel постоянно обозначают текстовую строк.;
«стар_текст» — Это тот текст, который необходимо нам поменять. Быть может константой либо адресом ячейки.
«нов_текст» — То, на что мы заменяем отысканный текст. Это, естественно, если в обозначенном тексте будет найдено хотя бы одно вхождение заменяемой последовательности знаков.
«номер_вхождения» — Этот аргумент указывать не непременно. Он значит номер вхождения, в каком необходимо выполнить подмену. Если не указать его, то делаются подмены всех вхождений разыскиваемой последовательности знаков.

На выходе функция ПОДСТАВИТЬ возвращает наш текст, в каком уже выполнены все нужные подмены. Если заменяемый текст, который указан во 2-м аргументе формулы, не найден, то в итоге текст ворачивается функцией в постоянном виде. Этот же итог будет в этом случае, если заменяемый текст найден, но номер его вхождения не соответствует крайнему аргументу функции.

Нумерация вхождений начинается с единицы, а не с нуля!

Интересно почитать: Статистические функции в excel

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

Примеры использования функции ПОДСТАВИТЬ

Несколько увлекательных примеров рассмотрены на видео и в прикреплённом файле Excel, который можно скачать опосля статьи. Посмотрев видео-урок, скачайте файл и поэкспериментируйте без помощи других с разными видами замен в тексте.

Давайте создадим примитивную подмену, чтоб стремительно проверить функцию в работе. Скопируйте в всякую ячейку собственной таблицы последующее выражение: «=ПОДСТАВИТЬ(«Хороший денек, Вася!»;»денек»;»вечер»)». Опосля нажатия Enter мы получим таковой текст: «Хороший вечер, Вася!». Остальные примеры, наиболее сложные и достойные внимания, смотрите в файле.

Стоит увидеть, что возможность автоматической массовой обработки с выполнением огромного количества замен существенно расширяет способности программки по автоматической обработке импортированного текста.

Научиться применять все способности Excel можно по нашему специальному видеокурсу, включающему 13 часов практических заданий, разделённых на 51 урок.

Глядеть видео
ПОДСТАВИТЬ в Excel (функция)

Прикреплённые документы

Вы сможете просмотреть хоть какой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, потому для закрытия документа пожалуйста не используйте клавишу «Вспять» браузера.

Справка по функции ПОДСТАВИТЬ в Excel.pdf

Файлы для загрузки

Вы сможете скачать прикреплённые ниже файлы для ознакомления. Обычно тут располагаются разные документы, также остальные файлы, имеющие конкретное отношение к данной публикации.

Excel функция подставить

Microsoft Excel ЗАМЕНА функция подменяет текст либо знаки в текстовой строке иным текстом либо знаками.

Синтаксис

=SUBSTITUTE( text , old_text , new_text , [ instance_num ])

аргументы

Текст (Непременно): текст либо ссылка на ячейку, содержащую текст для подмены.

Старый_текст (Непременно): текст, который необходимо поменять.

New_text (Непременно): текст, которым вы желаете поменять старенькый текст.

[Instance_num] (Необязательно): показывает, какой экземпляр следует поменять. Если опущено, все экземпляры будут изменены новеньким текстом.

Возвращаемое значение

Текст опосля заменен

Примеры

Пример 1. Поменяйте все экземпляры текста иным

Функция ЗАМЕНА может посодействовать поменять все экземпляры определенного текста новеньким текстом.

Как показано в приведенном ниже примере, вы желаете поменять все числа «3» на «1» в ячейке B2, изберите ячейку для вывода результата, скопируйте приведенную ниже формулу в панель формул и нажмите кнопку Enter, чтоб получить итог.
=SUBSTITUTE(B3,»3″,»1″)

Нотки:

1) В этом случае [instance_num] аргумент опущен, потому все числа 3 заменяются на 1.
2) Держите новый_текст пустой аргумент, он удалит старенькый текст и ничего не поменяет. См. Формулу ниже и скриншот:

Интересно почитать: Модуль в excel функция

Пример 2. Поменять определенный экземпляр текста иным

Как показано на снимке экрана ниже, вы желаете поменять лишь 1-ый экземпляр «1» на «2» в B3, изберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите кнопку Enter, чтоб получить итог.
=SUBSTITUTE(B3,»1″,»2″,1)

Примечание: Тут [instance_num] аргумент равен 1, что значит, что 1-ый экземпляр «1» будет заменен. Если вы желаете поменять 2-ой экземпляр «1», измените аргумент [instance_num] на 2.

Связанные функции

Функция ТЕКСТ в Excel
Функция ТЕКСТ конвертирует значение в текст с данным форматом в Excel.

Функция Excel TEXTJOIN
Функция Excel TEXTJOIN соединяет воединыжды несколько значений из строчки, столбца либо спектра ячеек с определенным разделителем.

Функция Excel TRIM
Функция Excel TRIM удаляет все излишние пробелы из текстовой строчки и сохраняет лишь отдельные пробелы меж словами.

Функция ВЕРХНИЙ в Excel
Функция Excel ВЕРХНИЙ конвертирует все буковкы данного текста в верхний регистр.

Наилучшие инструменты для работы в кабинете

Kutools for Excel — поможет для вас выделиться из толпы

Желаете стремительно и идеально делать свою ежедневную работу? Kutools for Excel дает массивные расширенные функции 300 (объединение книжек, сумма по цвету, разделение содержимого ячеек, дата преобразования и так дальше . ) и экономия 80% времени вам.

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Функция в excel расцепить

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

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

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

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

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

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

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

Пример 2: Удаляем непечатаемые знаки

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

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

Пример 3: Заменяем крайний знак пробела

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

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