Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

Функции замены в Excel

Замена части строки в – частая задача при обработке текстовых данных. Конечно, вы можете воспользоваться окном «Найти и заменить», но что если нужно сохранить исходный текст? В Excel есть две функции, позволяющие сделать замену, они достаточно гибкие и, в комбинации с другими текстовыми функциями, позволяют решить большинство задач:

  1. Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]).

Имеет 3 обязательных аргумента и один необязательный. Используйте функцию, когда не знаете точного расположения символов для замены.

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

В примере (рис. ниже) в первом варианте расчета указан необязательный аргумент [номер вхождения], заменено только первое вхождение. Во втором варианте этот аргумент не указан, заменены все вхождения строки в исходный текст.

функция замены в Excel ПОДСТАВИТЬ

Примеры применения функции ПОДСТАВИТЬ

Эту функцию применяют, когда вы знаете какой текст нужно заменить, но не знаете, где он расположен.

  1. Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

Эту функцию применяют, когда вы знаете место вставки. На мой взгляд, эта функция сложнее и менее практична, но применение ей существует. Она содержит четыре обязательных аргумента:

  • Исходный текст – строка или ссылка, в которой нужно произвести замену
  • Начальная позиция – порядковый номер первого символа для замены
  • Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
  • Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Excel

Функция ЗАМЕНИТЬ в Эксель

Комбинируйте функции замены с функциями ДЛСТР, ПОИСК, НАЙТИ и другими, чтобы добиться наилучших результатов. Изучите правила написания формул, использования функций, чтобы правильно применять описанные функции.

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

 

8 комментариев

  1. Сергей

    Здравствуйте, уважаемый Александр Томм

    Выручите меня, пожалуйста.
    Как написать формулу в Excel?
    Уважаемые программисты, подскажите пожалуйста, с помощью каких функций Excel можно заменить в предложении несколько разных слов в предложениях (из столбца2) на слова из столбца1

    Если заменять одно слово, то у меня получается с помощью функции ПОДСТАВИТЬ (столбец2строка1;»проживает по улице»;столбец1строка1)

    столбец1 ————-столбец2 (предложения)
    адрес прописки —Анна Петровна проживает по улице
    место проживания —Алексей Васильевич живет на улице

    После преобразования должно получиться так:

    столбец1 —————столбец2 (предложения)
    адрес прописки— —-Анна Петровна адрес прописки
    место проживания —Алексей Васильевич место проживания

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

    1. Александр Томм (Автор записи)

      Сергей, здравствуйте. Можно использовать несколько вложенных функций «ПОДСТАВИТЬ» в одной формуле. То есть, аргументом «Исходный текст» может быть результат вычисления другой функции «ПОДСТАВИТЬ». Так, последовательно, формула произведет все подстановки в одной ячейке. Функция будет выглядеть как-то так:
      =ПОДСТАВИТЬ(ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]); Что искать; На что заменить;[номер вхождения]).
      Используйте столько уровней подчинения, сколько нужно сделать замен.

  2. Марина

    Добрый день. Подскажите как из ячейки удалить справа несколько например 16 символов ?

    1. Александр Томм (Автор записи)

      Марина, здравствуйте. Способов много, можно, например, так: =ПСТР(A1;1;ДЛСТР(A1)-16). Здесь ДЛСТР вычислит полную длину строки, а ПСТР — вернет строку, длина которой на 16 симв. меньше полной.
      Как работает ПСТР описано в статье об извлечении части текста
      Работу ДЛСТР разобрали в статье о подсчете количества символов

  3. Омар

    Здравствуйте!
    У меня такая проблемка, допустим есть запись формата 111-111-111-11, необходимо убрать последний дефис и поставить вместо него пробел. Это можно как-то реализовать?
    Спасибо!

    1. Александр Томм (Автор записи)

      Здравствуйте, Омар. Воспользуйтесь такой формулой: =ПОДСТАВИТЬ(C5;»-«;» «;3). Вместо С5 поставьте ссылку на ту ячейку, в которой Ваш текст

      1. Омар

        Спасибо большое. Получилось

        1. Александр Томм (Автор записи)

          Омар, всегда пожалуйста. Возвращайтесь на наш блог!

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *