Описать задачу
Функции Excel › Текстовые › ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ в Excel (SUBSTITUTE): как работает, пример и формула под вашу таблицу

пример проверен пересчётомТекстовые Excel 2007 и новее

Редакция Формулника · обновлено · примеры проверены пересчётом LibreOffice 26.8 на сервере Формулника · как мы проверяем

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

Синтаксис в русском Excel

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

В английском Excel, Google Таблицах и внутри файла .xlsx

=SUBSTITUTE(текст, стар_текст, нов_текст, [номер_вхождения])
Сделать по моим данным

Поправьте задачу под свою таблицу — Формулник напишет формулу ПОДСТАВИТЬ под ваши столбцы и проверит её.

Аргументы

текстгде меняем
стар_текстчто меняем
нов_текстна что
[номер_вхождения]какое по счёту

Пример

AB
1Телефон
28 (495) 123-45-678(495)1234567
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;" ";"");"-";"")

Результат в ячейке B2 посчитан LibreOffice на сервере Формулника.

Частые ошибки

Нужно убрать несколько разных символов

вложите ПОДСТАВИТЬ друг в друга

Где работает

ПрограммаПОДСТАВИТЬ
Excel 2010есть
Excel 2013есть
Excel 2016есть
Excel 2019есть
Excel 2021есть
Excel 2024есть
Excel 365есть
LibreOffice Calcесть
Google Таблицыесть
Яндекс Таблицыесть
Р7-Офисесть
МойОфисесть
MOffice Sheetsестьпроверено

Похожие функции

СЖПРОБЕЛЫПСТР

ПОДСТАВИТЬ: телефоны, точка вместо запятой, n-е вхождение и подсчёт символов

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

По шагам

  1. Выпишите, какие символы мешают: пробелы, дефисы, скобки, точки, кавычки-«ёлочки».
  2. Вложите ПОДСТАВИТЬ друг в друга по одной замене на уровень: внутренняя убирает первый символ, следующая — второй.
  3. Если результат должен стать числом, поставьте -- перед формулой.
  4. Проверьте результат на строке с нестандартной записью — например, телефон с «доб.» или сумма с минусом.

Телефоны к единому виду +7XXXXXXXXXX

Клиенты записаны как «8 (495) 123-45-67», «+7 495 1234567», «8-916-000-11-22». Сначала убираем всё лишнее четырьмя вложенными ПОДСТАВИТЬ — пробелы, дефисы и скобки. Затем берём последние 10 цифр через ПРАВСИМВ и приклеиваем «+7»: так и восьмёрка, и «+7» в начале приводятся к одному виду. Номера с добавочным приходится разбирать отдельно — у них цифр больше одиннадцати.

="+7"&ПРАВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;" ";"");"-";"");"(";"");")";"");10)

Числа с точкой из CSV или маркетплейса

Отчёт выгружен с десятичной точкой: «1234.50». В русской Windows Excel считает это текстом или, хуже, датой («12.5» превращается в 12 мая). Если данные уже в ячейке как текст, меняем точку на запятую и превращаем в число. Для импорта новых файлов надёжнее указать разделитель в мастере «Данные → Из текстового/CSV-файла».

=--ПОДСТАВИТЬ(A2;".";",")

Заменить только второе вхождение

В строке «ДР-2041-01-М» нужно заменить второй дефис на косую черту, чтобы получить «ДР-2041/01-М». Четвёртый аргумент 2 указывает номер совпадения. Чтобы заменить последнее вхождение, номер считают через подсчёт символов (см. таблицу ниже).

=ПОДСТАВИТЬ(A2;"-";"/";2)

Сколько раз встречается символ или слово

Сколько артикулов перечислено через запятую в ячейке заказа? Длина строки минус длина без запятых — это число запятых, плюс один — число позиций. Для слова длину разницы делят на длину слова. Регистр важен: чтобы считать без него, оберните строку в СТРОЧН.

=ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;",";""))+1

Частые замены

ЗадачаФормула
Убрать все пробелы=ПОДСТАВИТЬ(A2;" ";"")
Неразрывный пробел → обычный=ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")
Перенос строки → пробел=ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ")
«Ёлочки» → прямые кавычки=ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"«";"""");"»";"""")
Заменить без учёта регистра=ПОДСТАВИТЬ(СТРОЧН(A2);"ооо";"ООО")
Заменить по позиции (первые 3 символа)=ЗАМЕНИТЬ(A2;1;3;"")

Частые ошибки

Вопросы и ответы

Чем ПОДСТАВИТЬ отличается от ЗАМЕНИТЬ?

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

Как заменить несколько разных символов одной формулой?

Вложите ПОДСТАВИТЬ друг в друга — по одной на каждый символ. В Microsoft 365 длинную цепочку удобно разбить через LET, давая промежуточным результатам имена.

Как удалить определённое слово из ячейки?

Замените его пустой строкой и сожмите пробелы: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"ООО";"")). Без СЖПРОБЕЛЫ в начале останется пробел.

Как заменить последнее вхождение символа?

Номер последнего вхождения равен числу таких символов в строке, а его считает разница длин: =ПОДСТАВИТЬ(A2;"-";"/";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"-";""))). Для «ДР-2041-01-М» заменится третий дефис.

Работает ли ПОДСТАВИТЬ в LibreOffice и Р7-Офис?

Да, с теми же аргументами и тем же различением регистра.

Можно ли привести к одному виду весь столбец телефонов из моего файла?

Загрузите файл в Формулник и опишите нужный вид номера — он подберёт формулу под ваши записи и покажет строки, которые не подошли под шаблон.