Замена значений эксель: Поиск или замена текста и чисел на листе

Содержание

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

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

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

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

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

где

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

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

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

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

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

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов

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

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

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

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

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

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

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

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

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

Зачистка текста от лишних пробелов, непечатаемых символов и т.д.
Как подсчитать количество слов в ячейке
Преобразование чисел-как-текст в полноценные числа

Функция ЗАМЕНИТЬ() в EXCEL.

Примеры и описание

Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Синтаксис функции

ЗАМЕНИТЬ ( исходный_текст ; нач_поз ; число_знаков ; новый_текст )

Исходный_текст — текст, в котором требуется заменить некоторые знаки. Нач_поз — позиция знака в Исходном_тексте , начиная с которой знаки заменяются текстом новый_текст . Число_знаков — число знаков в Исходном_тексте

, которые заменяются текстом новый_текст . Новый_текст — текст, который заменяет знаки в Исходном_тексте .

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

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

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

Пусть в ячейке А2 введена строка Продажи (январь) . Чтобы заменить слово январь

, на февраль , запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль»)

т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д. Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь) . Запишем формулы: =ЗАМЕНИТЬ(A2;10;6;»февраль») =ПОДСТАВИТЬ(A2; «январь»;»февраль») получим в первом случае строку Продажи (февраль), прибыль (январь) , во втором — Продажи (февраль), прибыль (февраль) .

Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль) .

Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

Использование функции для вставки нового текста в строку

Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида » ID-567(ASD) «, необходимо перед текстом ASD вставить новый текст Micro , чтобы получилось » ID-567(MicroASD) «. Для этого напишем простую формулу: =ЗАМЕНИТЬ(A2;8;0;»Micro»).

Использование функций Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ

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

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

ЗАМЕНИТЬ функцию в Excel
- Использование функции ЗАМЕНА с числовыми значениями
- Использование функции Excel REPLACE с датами
- Вложенные функции REPLACE в Excel
- Как заменить строку в другой позиции в ячейке
ПОДСТАВИТЬ функцию в Excel
- Вложение нескольких функций ПОДСТАВКИ в одну формулу
ЗАМЕНИТЬ и ЗАМЕНИТЬ — сходства и различия

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

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

ЗАМЕНИТЬ(старый_текст, начальный_номер, число_символов, новый_текст)

Как видите, функция ЗАМЕНИТЬ в Excel имеет 4 аргумента, и все они обязательны.

Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором необходимо заменить некоторые символы.
Start_num — позиция первого символа в old_text, который вы хотите заменить.
Num_chars — количество символов, которые вы хотите заменить.
New_text — текст замены.

Например, чтобы заменить слово « солнце » на « сын «, можно использовать следующую формулу:

=ЗАМЕНИТЬ("солнце", 2, 1, "о")

И если вы поместите исходное слово в какую-либо ячейку, скажем, A2, вы можете указать соответствующую ссылку на ячейку в аргументе old_text:

=ЗАМЕНИТЬ(A2, 2, 1, "o")

Примечание. Если аргумент start_num или num_chars отрицательный или нечисловой, формула замены Excel возвращает ошибку #ЗНАЧ! ошибка.

Использование функции Excel REPLACE с числовыми значениями

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

=ЗАМЕНИТЬ(A2, 7, 4, "2016")

Обратите внимание, что мы заключаем «2016» в двойные кавычки, как вы обычно делаете с текстовыми значениями.

Аналогичным образом вы можете заменить одну или несколько цифр в номере. Например:

=ЗАМЕНИТЬ(A4, 4, 4,"6")

И снова нужно заключить значение замены в двойные кавычки («6»).

Примечание. Формула Excel REPLACE всегда возвращает текстовую строку , а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.

Использование функции Excel REPLACE с датами

Как вы только что видели, функция REPLACE отлично работает с числами, за исключением того, что она возвращает текстовую строку 🙂 Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены для дат. Результаты будут весьма смущающими.

Например, у вас есть дата в A2, скажем, 1 октября 14, и вы хотите изменить « окт » на « ноябрь ». Итак, вы пишете формулу REPLACE(A2, 4, 3, «Nov»), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4 ^th char… и получил следующий результат:

Почему так? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Итак, наша формула замены изменяет последние 3 цифры в указанном выше серийном номере на « Nov » и возвращает текстовую строку «419Nov».

Чтобы заставить функцию Excel ЗАМЕНИТЬ правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки с помощью функции ТЕКСТ или любого другого метода, показанного в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")

Помните, что результатом приведенной выше формулы является текстовая строка , и поэтому это решение работает только в том случае, если вы не планируете использовать измененные даты в дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию ДАТАЗНАЧ, чтобы преобразовать значения, возвращаемые функцией ЗАМЕНИТЬ в Excel, обратно в даты:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4 , 3, "ноя"))

Вложенные функции REPLACE для выполнения нескольких замен в ячейке

Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучшим и более профессиональным способом является использование вложенных функций REPLACE , которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте «вложение» означает размещение одной функции внутри другой.

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

Вставить первый дефис очень просто. Вы пишете обычную формулу замены Excel, которая заменяет нулевых символов дефисом, т.е. добавляет дефис в 4-й ^-й-й позиции в ячейке:

=ЗАМЕНИТЬ(A2,4,0,"-")

Результат приведенной выше формулы замены выглядит следующим образом:

Итак, теперь нам нужно вставить еще один дефис в позицию 8 ^th. Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы вставляете его в аргумент old_text другой функции, чтобы вторая функция ЗАМЕНИТЬ обработала значение, возвращаемое первой ЗАМЕНОЙ, а не значение в ячейке A2:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2 ,4,0,"-"),8,0,"-")

В результате вы получите телефонные номера в нужном формате:

Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/), где это необходимо:

=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))

Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНЫ с помощью Функция ДАТАЗНАЧ:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(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»:

=НАЙТИ("@abc",A2)

А затем укажите вышеуказанную функцию НАЙТИ в аргументе start_num вашей формулы ЗАМЕНЫ:

=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2) , 4, "@bca")

Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.

Как видно на следующем снимке экрана, формула без проблем находит и заменяет старый текст новым. Однако если заменяемая текстовая строка не найдена, формула возвращает ошибку #ЗНАЧ! ошибка:

И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)

И это улучшилось формула работает отлично, не так ли?

Другое практическое применение функции ЗАМЕНИТЬ — сделать первую букву в ячейке прописной. Всякий раз, когда вы имеете дело со списком имен, продуктов и т.п., вы можете использовать приведенную выше формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ.

Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалог Excel НАЙТИ и ЗАМЕНИТЬ.

Функция Excel ПОДСТАВИТЬ

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

Синтаксис функции ПОДСТАВИТЬ в Excel следующий:

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

Первые три аргумента являются обязательными, а последний необязательным.

Текст — исходный текст, в который необходимо заменить символы. Может быть предоставлен в виде тестовой строки, ссылки на ячейку или результата другой формулы.
Old_text — символы, которые вы хотите заменить.
New_text — новые символы для замены old_text.
Instance_num — вхождение старого_текста, который вы хотите заменить. Если этот параметр опущен, каждое вхождение старого текста будет заменено новым текстом.

Например, все приведенные ниже формулы заменяют «1» на «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=ПОДСТАВИТЬ(A2, "1", " 2", 1) — Заменяет первое вхождение «1» на «2».

=ПОДСТАВИТЬ(A2, "1", "2", 2) — Заменяет второе вхождение «1» на «2».

=ПОДСТАВИТЬ(A2, "1", "2") — Заменяет все вхождения «1» на «2».

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

Как удалить символы или слова из строки
Как удалить ненужные символы из ячеек

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

Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)

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

Предположим, у вас есть текстовая строка типа « PR1, ML1, T1 » в ячейке A2, где «PR» означает «Проект», «ML» означает «Веха», а «T» означает «Задание». нужно заменить три кода полными именами.Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:

=ЗАМЕНИТЬ(A2,"PR", "Проект")

=ЗАМЕНИТЬ(A2, "ML", "Веха")

=ЗАМЕНИТЬ(A2, "T", "Задание")

А затем вложить их друг в друга:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")

Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.

Чтобы узнать о других способах одновременной замены нескольких значений, см. статью Массовый поиск и замена в Excel.

Excel REPLACE и Excel SUBSTITUTE

Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга тем, что обе предназначены для замены текстовых строк. Различия между этими двумя функциями заключаются в следующем:

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

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

Загрузить учебное пособие

Примеры формул ЗАМЕНИТЬ и ПОДСТАВИТЬ (файл . xlsx)

Вас также может заинтересовать

Как заменить текст в Excel с помощью функции ЗАМЕНА (2023)

Перейти к содержимому Как заменить текст в Excel с функцией ЗАМЕНИТЬ (2023)

Нужно заменить текст в нескольких ячейках?

Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ значительно упрощают этот процесс.

Давайте посмотрим, как работают две функции, чем они отличаются и как их использовать в реальной электронной таблице🔍

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

Содержание

Замена символов в тексте с помощью функции ЗАМЕНА

Функция ЗАМЕНА заменяет текстовую строку другой текстовой строкой.

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

Но изменить нужно только часть идентификатора продукта, а не весь.

Здесь часть «29FA» всех идентификаторов продуктов необходимо изменить на «39ЛУ».

Чтобы сделать это с помощью функции ЗАМЕНА, мы рассмотрим синтаксис функции ЗАМЕНА, который выглядит следующим образом:

=ЗАМЕНИТЬ(старый_текст, начальный_номер, число_символов, новый_текст) не так устрашающе, как кажется. На самом деле это довольно просто👍

Шаг 1: Старый текст

Аргумент старый текст — это ссылка на ячейку, в которой вы хотите заменить текст. Напишите:

=ЗАМЕНИТЬ(A2

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

Шаг 2: Начальный номер

Аргумент start_num определяет, откуда функция REPLACE должна начинать замену символов.

В нашем случае часть «29FA» начинается с 3-го символа в тексте.

Итак, напишите:

=ЗАМЕНИТЬ(A2, 3,

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

Все еще со мной? Тогда давайте углубимся в следующий аргумент синтаксиса REPLACE🤿

Шаг 3: Число символов

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

Как правило, это должна быть длина текста, которым вы хотите заменить старый текст.

Итак, это связано со следующим аргументом.

Если вы хотите заменить «29FA» на «39LU», то вы заменяете следующие 4 символа.

Пишите:

=ЗАМЕНИТЬ(A2, 3, 4

И завершите запятой🎁

Существуют ситуации, когда длина аргумента num_chars должна отличаться от длины аргумента new_text. Я расскажу вам об этом позже.

Каспер Лангманн , специалист Microsoft Office

Шаг 4: Новый текст

Аргумент new_text заменяет старый текст.

Итак, просто напишите новые символы, которые должны заменить старые 4 символа:

=ЗАМЕНИТЬ(A2, 3, 4, «39LU»

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

Завершите формулу закрывающей скобкой и нажмите Enter.

Теперь часть «29FA» старого идентификатора продукта заменена на «39LU».

Круто, правда? 😎

СОВЕТ ПРОФЕССИОНАЛА: Длина num_chars vs new_text

Если вам нужно, чтобы замещающий текст был короче или длиннее, чем текст, который он заменяет, вы можете указать разную длину в 3-м и 4-м аргументах ЗАМЕНЯТЬ. Позвольте мне дать вам несколько примеров формулы:

Если вы хотите заменить «29FA» на «39L», вы должны написать:

=ЗАМЕНИТЬ(A2, 3, 4, «39L»)

Но длина 4-го аргумента будет короче, чем 4 символа, определенные в третьем аргументе.

С другой стороны, если вы хотите заменить «FA» на «LLUU», вы должны написать так:

=REPLACE(A2, 5, 2, «LLUU»)

Замена текстовых строк на Функция ПОДСТАВИТЬ

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

Синтаксис ЗАМЕНЫ выглядит следующим образом:

=ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

Это немного отличается от последнего синтаксиса функции ЗАМЕНА, поэтому будьте осторожны, чтобы не получить их перепутал⚠️

Шаг 1: Текст
Текстовый аргумент — это просто ссылка на ячейку, в которой вы хотите заменить текст.
Напишите:
=ПОДСТАВИТЬ(A2
И, конечно же, поставьте запятую для перехода к следующему аргументу.
Шаг 2: Старый текст
Функция ПОДСТАВИТЬ не заменяет символы с фиксированной позиции в ячейке.
Вместо этого он ловко ищет текстовую строку и начинает заменять символы оттуда🔍
Итак, если вы хотите заменить часть «FA» идентификатора продукта на «LU», напишите:
= ПОДСТАВИТЬ(A2 , «FU»
Шаг 3: Новый текст
Аргумент new_text — это то, на что следует заменить старый текст
В данном примере это «LU»
=ПОДСТАВИТЬ(A2, «FA», «LU»

Новый текст не должен быть той же длины, что и старый текст.

Каспер Лангманн 900 Специалист по Microsoft Office5
Шаг 4: Номер экземпляра
Необязательный аргумент номер экземпляра определяет, сколько раз текст должен быть заменен. оставьте поле пустым, каждые экземпляра старого текста заменяются новым текстом😊
В следующем примере формулы первые 2 идентификатора продукта имеют по 2 экземпляра «FA». Таким образом, аргумент номера экземпляра определяет, заменяется ли только первый экземпляр «FA» на «LU» или оба экземпляра «FA» заменяются на «LU».
Как видно из рисунка ниже, напишите 1, если хотите заменить только первый экземпляр «FA».
=ЗАМЕНИТЬ(A2, «FA», «LU», 1)
Или не используйте аргумент номера экземпляра, если необходимо заменить каждый экземпляр «FA»:
= ПОДСТАВИТЬ (A2, «FA», «LU»)
И вот как заменить текст динамически на основе на месте текста, который вы хотите заменить.
Разница между REPLACE и SUBSTITUTE
Между этими двумя «функциями замены» есть несколько тонких различий.
Оба они заменяют один или несколько символов в текстовой строке другой текстовой строкой.
Разница заключается в том, как идентифицируется первая строка.
REPLACE выбирает первую строку на основе позиции. Таким образом, вы можете заменить четыре символа, начиная с шестого символа в строке.
SUBSTITUTE выбирает на основе того, соответствует ли строка предварительно определенному поиску. Например, вы можете указать Excel заменить любой экземпляр «FA» на «LU».
В остальном две функции идентичны👬🏻
Замена текста с помощью функции «Найти и заменить»
Другой способ замены текста — функция «Найти и заменить» Excel.
Это способ замены символов в исходной ячейке вместо добавления дополнительных столбцов с формулами.
1. Выберите все ячейки, содержащие заменяемый текст.
2. На вкладке «Главная» нажмите «Найти и выбрать».
3. В диалоговом окне «Найти и заменить» (на вкладке замены) напишите текст, который вы хотите заменить, в поле «Найти что:».
4. В диалоговом окне «Найти и заменить» введите новый текст для замены старого текста в поле «Заменить на:».
5. Когда вы нажимаете кнопку «Заменить все», Excel заменяет все экземпляры старого текста новым текстом в выбранных ячейках.

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

Каспер Лангманн , специалист по Microsoft Office
Вот и все. Что теперь?
С помощью функций REPLACE и SUBSTITUTE вы можете заменить очень специфические строки другими строками. Вы можете использовать буквы, цифры или другие символы.
Короче говоря, вы можете заменить текст с предельной точностью. И это экономит вам много времени, когда вам нужно внести много правок.
Кроме того, вы можете использовать инструмент «Найти и заменить», который является наиболее недооцененной функцией Excel.
Но никто не получил предложение о работе , а только , основываясь на своих навыках замены символов в тексте в Microsoft Excel.