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

Функция ЗАМЕНИТЬ() в 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”).

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

382 13. 04.2017 Скачать пример

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

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

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

где

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Использование функций 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 2016: Использование функции поиска и замены

560d890a369ca18833b60c20 563d118dca7fac0d9c7b3f7f

Урок 10. Использование функции «Найти и заменить»

/en/excel2016/working-with-multiple-worksheets/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь.

Посмотрите видео ниже, чтобы узнать больше об использовании функции «Найти и заменить».

Чтобы найти содержимое:

В нашем примере мы будем использовать команду «Найти», чтобы найти определенный отдел в этом списке.

1. На вкладке Home щелкните команду Find and Select , затем выберите Найдите в раскрывающемся меню.
2. Появится диалоговое окно «Найти и заменить ». Введите содержимое , которое вы хотите найти. В нашем примере мы введем название отдела.
3. Щелкните Найти далее . Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
4. Нажмите Найти далее , чтобы найти другие экземпляры, или Найти все , чтобы просмотреть все экземпляры поискового запроса.
5. Когда закончите, нажмите Закройте , чтобы закрыть диалоговое окно «Найти и заменить».

Вы также можете получить доступ к команде «Найти», нажав Ctrl+F на клавиатуре.

Щелкните Параметры , чтобы просмотреть критерии расширенного поиска в диалоговом окне «Найти и заменить».

Для замены содержимого ячейки:

Иногда вы можете обнаружить, что неоднократно допускали ошибку в своей книге (например, неправильно написали чье-то имя) или что вам нужно заменить определенное слово или фразу на другое. Вы можете использовать Excel Функция «Найти и заменить» для быстрого внесения изменений. В нашем примере мы будем использовать функцию «Найти и заменить», чтобы исправить список названий отделов.

1. На вкладке Home щелкните команду Найти и выбрать , затем выберите Заменить в раскрывающемся меню.
2. Появится диалоговое окно «Найти и заменить ». Введите текст, который вы хотите найти, в поле Find what: .
3. Введите текст, который вы хотите заменить, в поле Замените поле на: , затем щелкните Найти далее .
4. Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
5. Просмотрите текст, чтобы убедиться, что вы хотите его заменить.
6. Если вы хотите заменить его, выберите один из вариантов заменить . Выбор Заменить заменит отдельные экземпляры , а Заменить все заменит все экземпляры текста во всей книге. В нашем примере мы выберем этот вариант, чтобы сэкономить время.

7. Появится диалоговое окно, подтверждающее количество сделанных замен. Нажмите OK , чтобы продолжить.

8. Содержимое выбранной ячейки будет заменено на .

9. Когда вы закончите, нажмите «Закрыть», чтобы выйти из диалогового окна «Найти и заменить».

Как правило, лучше не использовать Заменить все , потому что это не дает возможности пропустить то, что вы не хотите менять. Вы должны использовать эту опцию только в том случае, если вы абсолютно уверены, что она не заменит ничего, что вы не планировали.