Функция ПОДСТАВИТЬ в Excel (SUBSTITUTE): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
текст | где меняем |
стар_текст | что меняем |
нов_текст | на что |
[номер_вхождения] | какое по счёту |
Пример
| A | B | |
|---|---|---|
| 1 | Телефон | |
| 2 | 8 (495) 123-45-67 | 8(495)1234567 |
Результат в ячейке B2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
вложите ПОДСТАВИТЬ друг в друга
Где работает
| Программа | ПОДСТАВИТЬ | |
|---|---|---|
| Excel 2010 | есть | |
| Excel 2013 | есть | |
| Excel 2016 | есть | |
| Excel 2019 | есть | |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | |
| Google Таблицы | есть | |
| Яндекс Таблицы | есть | |
| Р7-Офис | есть | |
| МойОфис | есть | |
| MOffice Sheets | есть | проверено |
Похожие функции
ПОДСТАВИТЬ: телефоны, точка вместо запятой, n-е вхождение и подсчёт символов
ПОДСТАВИТЬ ищет текст по содержимому и меняет все совпадения, а с четвёртым аргументом — только совпадение с указанным номером. Она различает заглавные и строчные буквы и не понимает звёздочки, поэтому «ООО» и «ооо» для неё разные строки. Если нужно заменить символы по позиции (первые три знака, восьмой символ), используйте ЗАМЕНИТЬ — это другая функция.
По шагам
- Выпишите, какие символы мешают: пробелы, дефисы, скобки, точки, кавычки-«ёлочки».
- Вложите ПОДСТАВИТЬ друг в друга по одной замене на уровень: внутренняя убирает первый символ, следующая — второй.
- Если результат должен стать числом, поставьте
--перед формулой. - Проверьте результат на строке с нестандартной записью — например, телефон с «доб.» или сумма с минусом.
Телефоны к единому виду +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;"") |
Частые ошибки
- Замена не сработала, хотя текст виден — регистр отличается или в строке неразрывный пробел вместо обычного. Проверьте
=НАЙТИ("ооо";A2)— #ЗНАЧ! значит, точного совпадения нет. - Результат не суммируется — ПОДСТАВИТЬ возвращает текст даже из цифр. Добавьте
--перед формулой. - Пытаются заменить по маске «*» — ПОДСТАВИТЬ ищет звёздочку как обычный символ. Для замены по маске используйте «Найти и заменить» (Ctrl+H), там подстановочные знаки работают.
Вопросы и ответы
Чем ПОДСТАВИТЬ отличается от ЗАМЕНИТЬ?
ПОДСТАВИТЬ ищет по тексту: «замени все дефисы». ЗАМЕНИТЬ работает по позиции: «замени три символа, начиная с пятого», содержимое ей не важно.
Как заменить несколько разных символов одной формулой?
Вложите ПОДСТАВИТЬ друг в друга — по одной на каждый символ. В Microsoft 365 длинную цепочку удобно разбить через LET, давая промежуточным результатам имена.
Как удалить определённое слово из ячейки?
Замените его пустой строкой и сожмите пробелы: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;"ООО";"")). Без СЖПРОБЕЛЫ в начале останется пробел.
Как заменить последнее вхождение символа?
Номер последнего вхождения равен числу таких символов в строке, а его считает разница длин: =ПОДСТАВИТЬ(A2;"-";"/";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;"-";""))). Для «ДР-2041-01-М» заменится третий дефис.
Работает ли ПОДСТАВИТЬ в LibreOffice и Р7-Офис?
Да, с теми же аргументами и тем же различением регистра.
Можно ли привести к одному виду весь столбец телефонов из моего файла?
Загрузите файл в Формулник и опишите нужный вид номера — он подберёт формулу под ваши записи и покажет строки, которые не подошли под шаблон.