Функция ПОДСТАВИТЬ в Excel и чем она отличается от ЗАМЕНИТЬ
ПОДСТАВИТЬ меняет один текст на другой, ЗАМЕНИТЬ — знаки на заданной позиции. Как заменить несколько значений сразу и почистить выгрузку из учётной системы.
Коротко
Если вы искали, как подставить значение из другой таблицы, — это задача функции ВПР, разбор здесь. Ниже — про функцию ПОДСТАВИТЬ, которая меняет текст внутри ячейки.
=ПОДСТАВИТЬ(текст;стар_текст;нов_текст;[номер_вхождения]) ищет в строке кусок и меняет его на другой. Без четвёртого аргумента меняет все вхождения, с ним — только указанное по счёту. Различает регистр: «ООО» и «ооо» для неё разные строки.
=ЗАМЕНИТЬ(стар_текст;нач_поз;число_знаков;нов_текст) работает иначе: ей неважно, что стоит в строке, она меняет знаки на месте — с такой-то позиции столько-то знаков.
Проще запомнить так: ПОДСТАВИТЬ — когда знаете, что заменить, ЗАМЕНИТЬ — когда знаете, где.
То же самое формулой в Excel
=ПОДСТАВИТЬ(A2;",";".") — заменить все запятые на точки=ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ") — неразрывный пробел из выгрузки — в обычный, после этого числа начинают считаться=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"ООО ";"");"«";"");"»";"") — несколько замен сразу — вкладываем одну ПОДСТАВИТЬ в другую=ПОДСТАВИТЬ(A2;"-";"/";2) — заменить только второй дефис — четвёртый аргумент=ЗАМЕНИТЬ(A2;1;3;"***") — скрыть первые три знака: номер карты или телефона для отчёта=ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;";";"")) — сколько раз в ячейке встречается точка с запятойПошагово
Почистить столбец из выгрузки
- Рядом с грязным столбцом поставьте формулу с нужными заменами — например, неразрывный пробел в обычный.
- Протяните вниз и проверьте результат.
- Скопируйте новый столбец и вставьте поверх старого через Специальную вставку → Значения. Формулы больше не нужны.
Заменить сразу много значений по справочнику. Вкладывать ПОДСТАВИТЬ двадцать раз неудобно. Если замен много, быстрее Ctrl+H — «Найти и заменить» по каждой паре, а если справочник меняется, — ВПР по таблице соответствий в соседнем столбце.
Сделать из текста число. После ПОДСТАВИТЬ результат — текст. Чтобы сумма заработала, оберните формулу в ЗНАЧЕН или умножьте на единицу: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;" ";"")).
Где обычно ломается
- ПОДСТАВИТЬ «не видит» слово. Регистр не совпал: функция чувствительна к нему. Приведите текст к одному регистру функцией СТРОЧН или ПРОПИСН.
- Пробел не заменяется. Это неразрывный пробел, код 160. Ищите
СИМВОЛ(160), а не обычный пробел. - Число после замены не складывается. Результат ПОДСТАВИТЬ — всегда текст. Нужен ЗНАЧЕН или умножение на единицу.
- Звёздочка и вопросительный знак. В ПОДСТАВИТЬ они обычные символы. А вот в окне Ctrl+H это подстановочные знаки — чтобы заменить саму звёздочку, пишите
~*. - На английском: ПОДСТАВИТЬ — SUBSTITUTE, ЗАМЕНИТЬ — REPLACE. В Google Таблицах есть обе.