logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

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

Функции замены в Excel Дата: 7 января 2016 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

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

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

  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 поставьте ссылку на ту ячейку, в которой Ваш текст

      Ответить
  4. Здравствуйте!
    1) Столбец «дата» на листе№1 в excel в формате: хх/хх/хххх Например: 18/12/2017
    2) В этом же файле на листе №2 я создал аналогичный столбец который делает подстановку «/» на «.»
    Формула такая: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(‘для вставки’!E2;»/»;».»))
    3)Вроде бы все работает (заменяет символы и визуально появляется формат даты), НО в дальнейшем фильтр на листе №2 не распознает значение даты в виде 18.12.2017 как дату. Если скопировать данный текст и вставить в любую ячейку как Значение, а после войти в ячейку и перед текстом пару раз нажать BackSpace то Значение принимает формат даты.
    Как быть с этим????? чтобы функция Подставить сразу же присваивало тексту формат Даты и фильтр воспринимал значение к Дату а не текст.
    СпасибО!

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

    Ответить
    1. Марина, здравствуйте. Используйте такую формулу: =ЗНАЧЕН(ЕСЛИ(ЛЕВСИМВ(A1)=»7″;ПОДСТАВИТЬ(A1;7;8;1);A1)). Вместо А1 поставьте ссылку на первую ячейку списка. Получилось ли у Вас использовать эту формулу?

      Ответить
      1. Александр, спасибо огромное. Все получилось

        Ответить
  6. Добрый день, Александр. Подскажите возможно ли и как в экселе вы выполнить данные действия.
    Создаю книгу(с 3-х разных книг скопированы определенные столбцы). Нужно сверять знания построчно (т.е. табельный номер) и если значение определенной ячейки (n-ая строка, «1 столбец»)совпадает с ячейкой (n-ой строки «то есть тут идет цикл», но «2» столбца), то копирует значение ячейки n-ой строки «3 столбца» в соседнюю ячейку («4 столбец», а строка совпадает с той ячейкой, с которой начинается сравнение)

    Ответить
    1. Евгений, здравствуйте. Вы слегка сумбурно описали свой вопрос. Если я правильно понял, у Вас есть 3 столбца: A, B и С. Нужно построчно сверить эти столбцы. Там где, A=B, записать C в D. Если так, то запишите в первой строке столбца D: =ЕСЛИ(A1=B1;C1;»»). После этого, скопируйте формулу во все строки диапазона.
      Если же я неправильно понял — уточните свой вопрос.

      Ответить
      1. Если основываться на эту формулу, то где-то такой вариант =ЕСЛИ(А1=В(от 1 до 200 «вот тут нужен цикл»);С1;>>) — не знаю насколько рабочий
        За основу берется столбец А (70-80 строк), столбцы B и С — база данных (по 200-300 строк) и каждая ячейка «А» сверяется с каждой «B» и когда совпадет то уже копирует ячейку столбца «С», которая соответствует строке столбца «B» в ячейку столбца «D», которая соответствует строке столбца «А».

        Ответить
        1. Евгений, теперь понятно. Вам нужно использовать функции поиска. Примените такую формулу: =ВПР(A1;B:C;2;0). Если есть вероятность, что в базе данных присутствуют не все искомые данные, можно добавить обработчик ошибок. Получится такая формула: =ЕСЛИОШИБКА(ВПР(A1;B:C;2;0);»Данные не найдены»). Теперь должно всё получиться.

          Ответить
  7. Снова здравствуйте. Теперь такой вопрос: есть ячейка в формате «дата/время» например (12:06:00), нужно чтоб она была разбита по столбцам в «текстовом» формате (12 06 00). Я пользовался мастером переноса по столбцам, а с помощью функций/формул такое возможно?

    Ответить
    1. Здравствуйте, Евгений. Вы можете использовать функции ЧАС, МИНУТЫ, СЕКУНДЫ для выделения отдельных компонент времени. Подробнее о них в этой статье. Чтобы записать их в указанном Вами виде и в текстовом формате, можете использовать функцию ТЕКСТ. Рассказывал о ней здесь. Одна из формул выделения, видимо, будет выглядеть так: =ТЕКСТ(СЕКУНДЫ(A2);»00″)

      Ответить
  8. Здравствуйте Александр.

    Подскажите пожалуйста как мне решить задачку:) Есть столбец, в котором написано название города и в скобках название области Пример: Новосибирск (Новосибирская область). Это всё в одной ячейке. Мне нужно во всем столбце убрать города и оставить только область, т.е. то, что в скобках.

    Ответить
    1. Здравствуйте, Александра. Можно воспользоваться такой формулой: =ПСТР(A1;ПОИСК(«(«;A1;1)+1;ПОИСК(«)»;A1;1)-ПОИСК(«(«;A1;1)-1). Только вместо А1 запишите ссылку на ту ячейку, где начинается Ваша таблица с регионами. Скопируйте её во все строки таблицы.
      В формуле я использовал функции ПОИСК и ПСТР. Чтобы узнать о них подробнее изучите эти небольшие статьи о поиске данных и извлечении части строки.
      Получилось ли у Вас решить задачку?

      Ответить
      1. Спасибо большое вам за помощь и ваш замечательный сайт!

        Ответить
  9. Здравствуйте, подскажите пожалуйста, как можно удалить все данные, после тире ? Благодарю.

    Ответить
  10. Добрый день подскажите пожалуйста, есть база данных по телефонам. в ней записаны номера формата 79001234567, нужно изменить первую 7 на 8, но что бы в книге не осталось формулы замены, потому что потом книгу с помощью скрипта переводим в карточки пользователей и скрипт не понимает ничего кроме 3 полей и отказывается работать если присутствуют формулы, подстановки, сложения.

    Ответить
    1. Андрей, здравствуйте. Сделайте формулой замены, а потом замените формулы результатами вычисления

      Ответить
  11. Здравствуйте, доступно объясняете! Помогите разобраться. Как из строк со словосочетаниями, например «туалетная вода Буча 50мл, Челяб» вырезать нужное слово (50мл) и вставить на соседний столбец в той же строке?

    Ответить