Excel заменить подстроку в строке
Обновлено: 21.11.2024
В учебнике объясняются функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ с примерами их использования. Узнайте, как использовать функцию ЗАМЕНИТЬ с текстовыми строками, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.
На прошлой неделе мы обсуждали различные способы использования функций НАЙТИ и ПОИСК на листах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке в зависимости от ее местоположения или замены одной текстовой строки другой в зависимости от содержимого. Как вы уже догадались, я говорю о функциях Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ.
Функция Excel ЗАМЕНИТЬ
Функция ЗАМЕНИТЬ в Excel позволяет заменить один или несколько символов в текстовой строке другим символом или набором символов.
Как видите, функция ЗАМЕНА в Excel имеет 4 аргумента, и все они обязательны.
- Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите заменить некоторые символы.
- Начальный_номер — позиция первого символа в старом_тексте, который вы хотите заменить.
- Num_chars — количество символов, которые вы хотите заменить.
- Новый_текст – замещающий текст.
Например, чтобы заменить слово "sun" на "son", можно использовать следующую формулу:
=ЗАМЕНИТЬ("солнце", 2, 1, "о")
И если вы поместите исходное слово в какую-либо ячейку, скажем, A2, вы можете указать ссылку на соответствующую ячейку в аргументе old_text:
=ЗАМЕНИТЬ(A2, 2, 1, "o")
Использование функции Excel REPLACE с числовыми значениями
Функция ЗАМЕНИТЬ в Excel предназначена для работы с текстовыми строками. Конечно, вы можете использовать его для замены цифровых символов, являющихся частью текстовой строки, например:
=REPLACE(A2, 7, 4, "2016")
Обратите внимание, что мы заключаем «2016» в двойные кавычки, как обычно делаем с текстовыми значениями.
Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например:
И снова нужно заключить значение замены в двойные кавычки ("6").
Примечание. Формула ЗАМЕНИТЬ в Excel всегда возвращает текстовую строку, а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.
Использование функции Excel REPLACE с датами
Как вы только что видели, функция ЗАМЕНИТЬ прекрасно работает с числами, за исключением того, что она возвращает текстовую строку :) Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены дат. . Результаты будут весьма неловкими.
Например, у вас есть дата в формате A2, скажем, 1-окт-14, и вы хотите изменить "октябрь" на "ноябрь". Итак, вы пишете формулу REPLACE(A2, 4, 3, "Nov"), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4-го символа… и получили следующий результат:
Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Таким образом, наша формула замены изменяет последние 3 цифры в приведенном выше серийном номере на "Nov" и возвращает текстовую строку "419Nov".
Чтобы заставить функцию Excel ЗАМЕНИТЬ правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ или любой другой метод, показанный в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")
Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает, только если вы не планируете использовать измененные даты в дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию DATEVALUE, чтобы преобразовать значения, возвращаемые функцией Excel REPLACE, обратно в даты:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь"))
Вложенные функции REPLACE для выполнения нескольких замен в ячейке
Довольно часто может потребоваться выполнить несколько замен в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции REPLACE, которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте "вложение" означает помещение одной функции в другую.
Рассмотрите следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить "123456789" в "123-456-789".
Вставить первый дефис очень просто. Вы пишете обычную формулу замены Excel, которая заменяет нулевые символы дефисом, т.е. добавляет дефис на 4-й позиции в ячейке:
Результат приведенной выше формулы замены выглядит следующим образом:
Хорошо, а теперь нам нужно вставить еще один дефис в 8-ю позицию. Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы вставляете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:
В результате вы получаете телефонные номера в нужном формате:
Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:
=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))
Кроме того, вы можете преобразовать текстовые строки в реальные даты, заключив приведенную выше формулу ЗАМЕНИТЬ в функцию ДАТАЗНАЧ:
И, естественно, вы не ограничены в количестве функций, которые вы можете вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать до 8192 символов и до 64 вложенных функций в формуле).
Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:
=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")
Замена строки, которая появляется в разных позициях в каждой ячейке
До сих пор во всех примерах мы имели дело со значениями схожего характера и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, который необходимо заменить. Следующий пример продемонстрирует то, о чем я говорю.
Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с "ABC" на, скажем, "BCA". Таким образом, вам необходимо соответствующим образом обновить адреса электронной почты всех клиентов.
Но проблема в том, что имена клиентов имеют разную длину, и поэтому вы не можете указать, где именно начинается название компании. Другими словами, вы не знаете, какое значение указать в аргументе start_num функции Excel REPLACE. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:
Затем укажите указанную выше функцию НАЙТИ в аргументе start_num формулы ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca")
Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты.Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.
И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)
И эта улучшенная формула отлично работает, не так ли?
Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалоговое окно Excel НАЙТИ и ЗАМЕНИТЬ.
Функция Excel ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.
Синтаксис функции ПОДСТАВИТЬ в Excel следующий:
Первые три аргумента являются обязательными, а последний необязательным.
- Текст — исходный текст, в котором вы хотите заменить символы. Может быть предоставлена в виде тестовой строки, ссылки на ячейку или результата другой формулы.
- Old_text — символы, которые вы хотите заменить.
- Новый_текст — новые символы для замены старого_текста.
- Instance_num – экземпляр old_text, который вы хотите заменить. Если его не указать, каждое вхождение старого текста будет заменено новым текстом.
Например, все приведенные ниже формулы заменяют "1" на "2" в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:
=SUBSTITUTE(A2, "1", "2", 1) — первое вхождение "1" заменяется на "2".
=SUBSTITUTE(A2, "1", "2", 2) — заменяет второе вхождение "1" на "2".
=SUBSTITUTE(A2, "1", "2") — заменяет все вхождения "1" на "2".
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру. Например, следующая формула заменяет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.
Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)
Как и в случае с функцией ЗАМЕНА Excel, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы выполнять несколько подстановок одновременно, т. е. заменять несколько символов или подстрок одной формулой.
Предположим, что у вас есть текстовая строка вида "PR1, ML1, T1" в ячейке A2, где "PR" означает "Проект", "ML" означает "Веха", а "T" означает "Задача". Вам нужно заменить три кода полными именами. Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:
=ПОДСТАВИТЬ(A2, "ML", "Веха")
=ПОДСТАВИТЬ(A2, "Т", "Задание")
А затем вложите их друг в друга:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")
Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.
ЗАМЕНА в Excel и ЗАМЕНА в Excel
Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга в том смысле, что обе предназначены для замены текстовых строк. Различия между этими двумя функциями заключаются в следующем:
- SUBSTITUTE заменяет один или несколько экземпляров заданного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию Excel ПОДСТАВИТЬ.
- REPLACE изменяет символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel REPLACE.
- Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (instance_num), указывающий, какое вхождение old_text следует заменить на new_text.
Вот как вы используете функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач. Я благодарю вас за чтение и надеюсь увидеть в нашем блоге на следующей неделе!
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функций ЗАМЕНИТЬ и ЗАМЕНИТЬВ в Microsoft Excel.
Описание
REPLACE заменяет часть текстовой строки в зависимости от указанного вами количества символов другой текстовой строкой.
REPLACEB заменяет часть текстовой строки в зависимости от указанного вами количества байтов другой текстовой строкой.
Эти функции могут быть доступны не на всех языках.
REPLACE предназначен для использования с языками, использующими набор однобайтовых символов (SBCS), тогда как REPLACEB предназначен для использования с языками, использующими двухбайтовый набор символов (DBCS). Настройка языка по умолчанию на вашем компьютере влияет на возвращаемое значение следующим образом:
REPLACE всегда считает каждый символ, будь то однобайтовый или двухбайтовый, равным 1, независимо от того, какой язык установлен по умолчанию.
REPLACEB считает каждый двухбайтовый символ равным 2, если вы включили редактирование языка, поддерживающего DBCS, а затем установили его в качестве языка по умолчанию. В противном случае REPLACEB считает каждый символ равным 1.
Языки, поддерживающие DBCS, включают японский, китайский (упрощенный), китайский (традиционный) и корейский.
Синтаксис
ЗАМЕНИТЬ(старый_текст, начальный_номер, число_знаков, новый_текст)
ЗАМЕНИТЬB(старый_текст, начальный_номер, число_байтов, новый_текст)
Синтаксис функций REPLACE и REPLACEB имеет следующие аргументы:
Старый_текст Обязательно. Текст, в котором вы хотите заменить некоторые символы.
Обязательный параметр Start_num. Позиция символа в old_text, которую вы хотите заменить на new_text.
Необходимо Num_chars. Количество символов в old_text, которые вы хотите заменить REPLACE на new_text.
Необходимо Num_bytes. Количество байтов в old_text, которые вы хотите заменить REPLACEB на new_text.
Обязательный параметр New_text. Текст, который заменит символы в old_text.
Пример
Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.
Замена текста с помощью функций ПОДСТАВИТЬ и ЗАМЕНИТЬ
Посмотрите видео и/или следуйте инструкциям ниже. В Excel есть две функции, позволяющие заменять текст в текстовой строке.
ПОДСТАВИТЬ позволяет вам указать контент, который вы хотите заменить — например, вы можете заменить все дефисы в коде продукта пробелом.
REPLACE позволяет вам указать позицию в текстовой строке, которую вы хотите заменить, а также позволяет указать количество символов. Таким образом, вы можете заменить имя фамилию на начальную фамилию имени.
ЗАМЕНА И ВПР
Вы можете использовать ЗАМЕНУ вместе с функцией ВПР. Например, у меня есть основной список кодов продуктов и соответствующих цен. У меня есть отдельная таблица заказов продуктов, которые я хочу автоматически оценивать с помощью ВПР. В данный момент функция ВПР не работает, так как коды продуктов в списке заказов (столбец D) содержат дефисы.
Чтобы удалить все дефисы, я бы использовал следующую формулу:
Первый аргумент (текст) просит вас указать текстовую строку, в которой вы хотите выполнить замену, в моем примере это D2.
Второй аргумент (Old_text) просит вас указать символ или символы, которые вы хотите заменить, в нашем примере это дефис. Обратите внимание, двойные кавычки вокруг дефиса «-»
Третий аргумент (Новый_текст) просит вас указать символ или символы, которые вы хотите использовать для замены старого_текста. В нашем примере это пробел — снова обратите внимание на двойные дефисы. “ “
Четвертый аргумент (Instance_number) позволяет вам указать, какое вхождение старого_текста вы хотите заменить. Если оставить его пустым, как в этом примере, он заменит все вхождения, но если я введу 2, он заменит только второй дефис.
Чтобы вернуть правильную цену для каждого продукта, я бы вложил функцию ПОДСТАВИТЬ в свой ВПР, вот так.
=ВПР(ПОДСТАВИТЬ(D2,"-"", "),$A$1:$B$200,2,ЛОЖЬ)
Подсчитайте количество слов в текстовой строке с помощью функции ПОДСТАВИТЬ
Вы можете использовать функцию ПОДСТАВИТЬ в сочетании с функцией ДЛСТР для подсчета количества слов в текстовой строке. Это может быть полезно, если вы хотите подсчитать количество заказанных продуктов (когда продукты были введены в одну ячейку).
Чтобы подсчитать количество слов, нам нужно подсчитать количество пробелов в текстовой строке. Расчет работает следующим образом: вычислить количество символов в текстовой строке, включая пробелы, затем вычислить количество символов, исключая пробелы, а затем вычесть одно из другого.
Функция ДЛСТР возвращает количество пробелов в текстовой строке, поэтому ДЛСТР(I2) вычисляет количество символов в I2.
Чтобы вычислить количество символов без учета пробелов, мы использовали бы ДЛСТР и ПОДСТАВИТЬ: ДЛСТР(ПОДСТАВИТЬ(I2, ",")), где функция ПОДСТАВИТЬ заменяет пробелы пустой текстовой строкой (две двойные кавычки, между которыми ничего нет). ).
Поэтому вычитание выглядит так: =ДЛСТР(I2)-ДЛСТР(ПОДСТАВИТЬ(I2, "," ))
Теперь, если есть три слова, есть два пробела, если есть пять слов, есть четыре пробела и так далее. Таким образом, вычисление нуждается в небольшой корректировке: +1 в конце: =ДЛСТР(I2)-ДЛСТР(ПОДСТАВИТЬ(I2, ","))+1, у нас есть правильный ответ.
Функция ЗАМЕНИТЬ
Функция REPLACE имеет четыре аргумента:
Старый_текст — текст, содержащий символы, которые вы хотите заменить
Start_number — позиция первого символа в Old_text, который вы хотите заменить
Num_chars – количество символов, которые вы хотите заменить. Если 0, символы не заменяются
New_text – текст замены
В этом примере мы собираемся использовать REPLACE для преобразования имени и фамилии в фамилию. Я понимаю, что в Excel это можно сделать множеством способов, но давайте попробуем ЗАМЕНИТЬ.
Для этого используется формула =ЗАМЕНИТЬ(A2,1,ПОИСК(" ",A2)",")
Старый_текст = A2
Начальный_номер = 1
Num_chars — это сложнее, так как мы хотим заменить имя целиком, а имена в нашем списке имеют разную длину символов. Решение состоит в том, чтобы использовать функцию ПОИСК, чтобы найти положение пробела, которое дает нам количество символов для замены. ПОИСК(" ",A2)
New_text — мы хотим удалить первое имя, поэтому нужно использовать пустую строку символов «»
Пример 2
Мы также можем использовать REPLACE для преобразования имени и фамилии в начальную фамилию, как показано ниже.
Формула для этого будет =ЗАМЕНИТЬ(A2,1,ПОИСК(" ",A2)-1,ЛЕВАЯ(A2,1)
Как и в предыдущем примере, Num_chars использует функцию ПОИСК для поиска пробела, но затем вычитает один, так что пробел перед фамилией не заменяется. Аргумент New_text использует функцию LEFT для возврата инициала в имени.
Обратите внимание, что вы можете указать ноль в аргументе Num_chars. Это будет означать, что ваш New_text не заменит существующие символы, а будет просто добавлен в указанную позицию.
Например, =REPLACE(A2,1,0, «Mr») вернет Mr Bert Jones. Фактически вы не заменяете ничего, что вы на самом деле объединяете или объединяете значения.
Добро пожаловать в Blue Pecan: мы предлагаем индивидуальное обучение Excel на дому или на месте в вашем офисе. Мы базируемся в Сассексе, Великобритания, и работаем в родных округах и Лондоне, включая Сассекс, Дорсет, Хэмпшир, Кент, Эссекс, Беркшир и Бакингемшир. Позвоните по номеру 0800 612 4105, чтобы узнать подробности.
В этом посте мы рассмотрим, как найти и заменить несколько текстовых строк внутри другой строки.
В Excel есть отличная встроенная функция ПОДСТАВИТЬ, которая позволяет найти один фрагмент текста в другой текстовой строке и заменить его другим фрагментом текста.
Скопируйте и вставьте эту таблицу в ячейку A1 в Excel
Скопируйте и вставьте эту таблицу в ячейку A1 в Excel
Теперь, если мы также хотим заменить бананы шоколадом, мы можем сделать это, используя вложенную формулу ПОДСТАВКИ.
По мере того, как мы добавляем все больше и больше элементов, которые хотим заменить, нам нужно вкладывать все больше и больше функций ЗАМЕНЫ, и это станет более неуправляемым. Поэтому вместо этого мы создадим определяемую пользователем функцию в VBA, чтобы упростить это.Если вы хотите узнать, как использовать этот код VBA, прочитайте этот пост о том, как использовать код VBA, который вы найдете в Интернете.
Эта определяемая пользователем функция принимает на вход текстовый элемент и два диапазона.
- strInput – это текст, фрагменты которого вы хотите заменить.
- rngFind — это диапазон, содержащий текстовые строки, которые вы хотите найти в strInput.
- rngReplace — это диапазон, содержащий текстовые строки, которыми вы хотите заменить элементы из rngFind. Размеры rngFind и rngReplace должны быть равны, иначе функция вернет ошибку.
Скопируйте и вставьте эту таблицу в ячейку A1 в Excel
| Текст | Исправленный текст |
|---|---|
| Я ем яблоки, бананы, морковь и огурцы | < /tr>