Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

55798 13.04.2017 Скачать пример

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

Её синтаксис таков:

=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)

где

  • Ячейка - ячейка с текстом, где производится замена
  • Старый_текст - текст, который надо найти и заменить
  • Новый_текст - текст, на который заменяем
  • Номер_вхождения - необязательный аргумент, задающий номер вхождения старого текста на замену

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Обратите внимание, что:

  • Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 - обе "Маши" заменены на "Олю").
  • Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая "Маша" заменена на "Олю").
  • Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. "маша" написана с маленькой буквы)

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

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом - спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами - заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM). Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Удаление неразрывного пробела

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

Количество слов

Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея - та же.

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

Извлечение первых двух слов

У нее простая логика:

  1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
  2. ищем позицию символа # функцией НАЙТИ (FIND)
  3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Ссылки по теме

 





kalmar
27.04.2017 15:29:46
Для замены неразрывных пробелов аналогично отработает формула
=ЗНАЧЕН(ПОДСТАВИТЬ(A1;СИМВОЛ(160);""))
Ссылка
juniorExcelист
11.04.2022 18:05:20
может кому пригодится формула для облегчения заполнения дат в одной ячейке, которая приводит "02 03 15 31" в "02.04.2022, 03.04.2022, 15.04.2022, 31.04.2022" (наваял с учетом (возможных?) ошибок заполняющих лиц)
A1=ИНСТРУКЦИЯ: вписать в A2 .месяц.год в формате ".04.2022" , а ниже в столбец A только числа месяца через пробел, пример "02 03 15 31". После заполнения столбца A выделить столбец B и включить Перенос текста
A2=.04.2022
B3=ЕСЛИ(ИЛИ(A3="";СЖПРОБЕЛЫ(A3)="";);"";ПОДСТАВИТЬ(СЦЕПИТЬ(СЖПРОБЕЛЫ(A3);$A$2);" ";$A$2&", ";))
Ссылка
Константин З
29.03.2023 07:24:47
Столкнулся с тем, что из-за функции ПОДСТАВИТЬ(), превысил длину  разрешённую длину написания формулы.
Может есть безмакросный вариант, указав не символ в одной функции, а список символов?
Спасибо.
Ссылка
juniorExcelист
31.01.2024 02:30:45
может кому пригодится формула очистить телефон до 10 знаков. удалить 8, 7, +7, ()скобки, -дефис, пробел
=ЕСЛИ(ЛЕВСИМВ(A1)="8";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;8;;1);"+7";"");"-";"");"(";"");")";"");" ";"");
ЕСЛИ(ЛЕВСИМВ(A1)="7";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;7;;1);"+7";"");"-";"");"(";"");")";"");" ";"");
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"+7";"");"-";"");"(";"");")";"");" ";"")))
Ссылка
Наверх