В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски).
Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.
Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ используются для замены одной буквы или части текста в ячейке. Но делают они это немного по-разному. Об этом и поговорим далее.
Как работает функция ЗАМЕНИТЬ
Функция ЗАМЕНИТЬ позволяет заместить слово, один или несколько символов в текстовой строке другим словом или символом.
ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков, новый_текст)
Как видите, функция ЗАМЕНИТЬ имеет 4 аргумента, и все они обязательны для заполнения.
- Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
- Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
- Число_знаков — количество символов, которые вы хотите заместить новыми.
- Новый_текст – текст замены.
Например, чтобы исправить слово «кит» на «кот», следует поменять вторую букву в слове. Вы можете использовать следующую формулу:
=ЗАМЕНИТЬ(«кит»;2;1;"о")
И если вы поместите исходное слово в какую-нибудь ячейку, скажем, A2, вы можете указать соответствующую ссылку на ячейку в аргументе старый_текст:
=ЗАМЕНИТЬ(А2;2;1;"о")
![функция ЗАМЕНИТЬ](https://mister-office.pro/wp-content/uploads/2022/12/replace-01-600x169.png)
Примечание. Если аргументы начальная_позиция или число_знаков отрицательные или не являются числом, формула замены возвращает ошибку #ЗНАЧ!.
Использование функции ЗАМЕНИТЬ с числами
Функция ЗАМЕНИТЬ предназначена для работы с текстом. Но безусловно, вы можете использовать ее для замены не только букв, но и цифр, являющихся частью текстовой строки, например:
=ЗАМЕНИТЬ(A1; 9; 4; "2023")
![как заменить значения в ячейке Эксель](https://mister-office.pro/wp-content/uploads/2022/12/replace-02-600x161.png)
Обратите внимание, что мы заключаем «2023» в двойные кавычки, как вы обычно делаете с текстовыми значениями.
Аналогичным образом вы можете заменить одну или несколько цифр в числе. Например формула:
=ЗАМЕНИТЬ(A1;3;2;"23")
И снова вы должны заключить значение замены в двойные кавычки ("23").
![использование функции ЗАМЕНИТЬ с числами](https://mister-office.pro/wp-content/uploads/2022/12/replace-03-600x141.png)
Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.
Как заменить часть даты
Как вы только что видели, функция ЗАМЕНИТЬ отлично работает с числами, за исключением того, что она возвращает текстовую строку :) Помните, что во внутренней системе Excel даты хранятся в виде чисел. Поэтому нельзя пытаться заменить часть даты, если она предварительно не преобразована в текст.
Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; "Май"), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:
![](https://mister-office.pro/wp-content/uploads/2022/12/replace-05-600x165.png)
Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».
Чтобы заставить функцию ЗАМЕНИТЬ правильно работать с датами, вы должны сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент старый_текст функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(ТЕКСТ(A3; "дд-ммм-гг"); 4; 3; "Май")
Помните, что результатом приведенной выше формулы является текстовая строка, и поэтому это решение работает только в том случае, если вы не планируете использовать измененные даты в своих дальнейших расчетах. Если вам нужны даты, а не текстовые строки, используйте функцию ДАТАЗНАЧ , чтобы преобразовать значения, возвращаемые функцией Excel ЗАМЕНИТЬ, обратно в даты:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; "дд-ммм-гг"); 4; 3; "Май"))
![как заменить часть даты](https://mister-office.pro/wp-content/uploads/2022/12/replace-04-600x203.png)
Как заменить сразу несколько букв или слов
Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке Excel. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучший и более профессиональный способ — использовать вложенные функции ЗАМЕНИТЬ, которые позволяют выполнить сразу несколько замен с помощью одной формулы. В этом смысле «вложение» означает размещение одной функции внутри другой.
Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на привычные нам телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».
Вставить первый дефис легко. Вы пишете обычную формулу замены Excel, которая заменяет ноль символов дефисом, т.е. просто добавляет дефис на четвёртой позиции в ячейке:
=ЗАМЕНИТЬ(A3;4;0;"-")
Результат приведенной выше формулы замены выглядит следующим образом:
![заменить сразу несколько букв или слов](https://mister-office.pro/wp-content/uploads/2022/12/replace-06-600x270.png)
А теперь нам нужно вставить еще один дефис в восьмую позицию. Для этого вы помещаете приведенную выше формулу в еще одну функцию Excel ЗАМЕНИТЬ. Точнее, вы встраиваете её в аргумент старый_текст другой функции, чтобы вторая функция ЗАМЕНИТЬ обрабатывала значение, возвращаемое первой формулой, а не первоначальное значение из ячейки А3:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;"-");8;0;"-")
В результате вы получаете номера телефонов в нужном формате:
![вложенные функции ЗАМЕНИТЬ](https://mister-office.pro/wp-content/uploads/2022/12/replace-07-600x235.png)
Аналогичным образом вы можете использовать вложенные функции ЗАМЕНИТЬ, чтобы текстовые строки выглядели как даты, добавляя косую черту (/) там, где это необходимо:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;"/");6;0;"/")
![](https://mister-office.pro/wp-content/uploads/2022/12/replace-08-600x238.png)
Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:
=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;"/");6;0;"/"))
И, естественно, вы не ограничены в количестве функций, которые вы можете последовательно, как матрёшки, вложить друг в друга в одной формуле (современные версии Excel позволяют использовать до 8192 символов и до 64 вложенных функций в одной формуле).
Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:
=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;"/");6;0;"/");9;0;" ");12;0;":")
![](https://mister-office.pro/wp-content/uploads/2022/12/replace-09-800x143.png)
Как найти и заменить часть текста
До сих пор во всех примерах мы имели дело с простыми задачами и производили замены в одной и той же позиции в каждой ячейке. Но реальные задачи часто бывают сложнее. В ваших рабочих листах заменяемые символы могут не обязательно появляться в одном и том же месте в каждой ячейке, и поэтому вам придется найти позицию первого символа, начиная с которого нужно заменить часть текста. Следующий пример продемонстрирует то, о чем я говорю.
Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Изменилось и название их почтового домена. Таким образом, вы должны соответствующим образом обновить адреса электронной почты всех клиентов и заменить три буквы в адресах электронной почты, где это необходимо.
Но проблема в том, что имена почтовых ящиков имеют разную длину, и поэтому нельзя указать, с какой именно позиции начинается название домена. Другими словами, вы не знаете, какое значение указать в аргументе начальная_позиция функции Excel ЗАМЕНИТЬ. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию, с которой начинается доменное имя в адресе электронной почты:
=НАЙТИ("@abc"; A3)
Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A3; НАЙТИ("@abc";A3); 4; "@bca")
Примечание. Мы включаем символ «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных ошибочных замен в именах почтовых ящиков электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.
Как вы видите на скриншоте ниже, у формулы нет проблем, чтобы поменять символы в разных позициях. Однако если заменяемая текстовая строка не найдена и менять в ней ничего не нужно, формула возвращает ошибку #ЗНАЧ!:
![Excel как заменить буквы в адресе](https://mister-office.pro/wp-content/uploads/2022/12/replace-10-800x321.png)
И мы хотим, чтобы формула вместо ошибки возвращала исходный адрес электронной почты без изменения. Для этого заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ("@abc";A3); 4; "@bca");A3)
И эта доработанная формула прекрасно работает, не так ли?
![Как найти и заменить часть текста](https://mister-office.pro/wp-content/uploads/2022/12/replace-11-800x248.png)
Заменить заглавные буквы на строчные и наоборот
Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.
Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу
=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))
![excel заменить первые буквы на заглавные](https://mister-office.pro/wp-content/uploads/2022/12/replace-15-800x277.png)
Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).
Быть может, это будет полезно.
Описание функции ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ в Excel заменяет один или несколько экземпляров заданного символа или текстовой строки указанными символами.
Синтаксис формулы ПОДСТАВИТЬ в Excel следующий:
ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_вхождения])
Первые три аргумента являются обязательными, а последний – нет.
- Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
- Старый_текст – что именно вы хотите заменить.
- Новый_текст – новый символ или слово для замены старого_текста.
- Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.
Например, все приведенные ниже формулы подставляют вместо «1» – цифру «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:
=ПОДСТАВИТЬ(A3;"1";"2";1) - Заменяет первое вхождение «1» на «2».
=ПОДСТАВИТЬ(A3;"1";"2";2) - Заменяет второе вхождение «1» на «2».
=ПОДСТАВИТЬ(A3;"1";"2") - Заменяет все вхождения "1" на "2".
![функция ПОДСТАВИТЬ](https://mister-office.pro/wp-content/uploads/2022/12/replace-12-600x216.png)
На практике формула ПОДСТАВИТЬ также используется для удаления ненужных символов из текста. Вы просто меняете их на пустую строку “”.
Например, чтобы удалить пробелы из текста, замените их на пустоту.
=ПОДСТАВИТЬ(A3;" ";"")
Примечание. Функция ПОДСТАВИТЬ в Excel чувствительна к регистру . Например, следующая формула меняет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.
=ПОДСТАВИТЬ(A3;"Х";"Y")
![](https://mister-office.pro/wp-content/uploads/2022/12/replace-13-600x121.png)
Замена нескольких значений при помощи одной формулы
Как и в случае с функцией ЗАМЕНИТЬ, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы сделать несколько подстановок одновременно, т.е. заменить несколько символов или подстрок при помощи одной формулы.
Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:
=ПОДСТАВИТЬ(A3;"пр";"Проект ")
=ПОДСТАВИТЬ(A3;"эт";"Этап ")
=ПОДСТАВИТЬ(A3;"з";"Задача ")
А затем вложить их друг в друга:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;"пр";"Проект "); "эт";"Этап ");"з";"Задача ")
Обратите внимание, что мы добавили пробел в конце каждого аргумента новый_текст для лучшей читабельности.
![Замена нескольких значений при помощи одной формулы](https://mister-office.pro/wp-content/uploads/2022/12/replace-14-800x232.png)
Другие полезные применения функции ПОДСТАВИТЬ:
- Замена неразрывных пробелов в ячейке Excel обычными
- Убрать пробелы в числах
- Удалить перенос строки в ячейке
- Подсчитать определенные символы в ячейке
Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?
Функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ очень похожи друг на друга в том смысле, что обе они предназначены для подмены отдельных символов или текстовых строк. Различия между двумя функциями заключаются в следующем:
- ПОДСТАВИТЬ замещает один или несколько экземпляров данного символа или текстовой строки. Итак, если вы знаете тот текст, который нужно поменять, используйте функцию Excel ПОДСТАВИТЬ.
- ЗАМЕНИТЬ замещает символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel ЗАМЕНИТЬ.
- Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (номер_вхождения), указывающий, какой по счету экземпляр старого_текста следует заместить на новый_текст.
Вот как вы можете заменить текст в ячейке и использовать функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel. Надеюсь, эти примеры окажутся полезными при решении ваших задач.
343?44?456,645,456 466 примере как заменяет эту "?" "," " " знаки одновременно на
", " ", " ", " запятой пробелом