Загрузите образец книги
В этом руководстве показано, как использовать Функция ПОДСТАВИТЬ в Excel в Excel, чтобы найти и заменить текст.
ЗАМЕНА Обзор функции
Функция ЗАМЕНИТЬ Находит и заменяет существующий текст новой текстовой строкой. Деликатный случай.
Чтобы использовать функцию ЗАМЕНЫ листа Excel, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
Функция ЗАМЕНА Синтаксис и входные данные:
| 1 | = ПОДСТАВИТЬ (текст; старый_текст; новый_текст; номер_экземпляра) |
текст - Исходная строка текста.
old_text - Текстовая строка, которую вы хотите найти и заменить.
new_test - Текст замены.
instance_num - По желанию. Номер экземпляра для замены. Если пусто, заменяются все экземпляры.
Что такое ЗАМЕНА?
Функция ЗАМЕНА в Excel используется для замены строки символов в текстовой строке другой указанной текстовой строкой.
Давайте посмотрим на следующий пример, заменив строку «быстрый коричневый» на «медленный белый».
| 1 | = ПОДСТАВИТЬ (C2; C3; C4) |
SUBSTITUTE просматривает строку в C2, находит «быстрый коричневый» и заменяет его на «медленный белый».
Как использовать ЗАМЕНА
Функция ПОДСТАВИТЬ принимает 3 обязательных аргумента и 1 необязательный:
- Текст: текст, который вы хотите заменить символы.
- Old_text: текст, который нужно заменить
- New_text: текст, который вы хотите заменить Old_text на
- Instance_num (необязательно): указывает вхождение Old_text, которое вы хотите заменить на New_text. По умолчанию все вхождения Old_text будут заменены, если экземпляр не указан.
Вложенная ЗАМЕНА
Что, если бы нам пришлось заменить две части одной и той же строки? Давайте посмотрим, как мы объединим две функции ЗАМЕНА.
| 1 | = ПОДСТАВИТЬ (ПОДСТАВИТЬ (C2; C3; C4); C5; C6) |
Обратите внимание, как результат ЗАМЕНЫ (C2, C3, C4) используется в качестве первого аргумента для второй ЗАМЕНА. Если разложить формулу на части:
= ПОДСТАВИТЬ (C2, C3, C4) оценивается как «The медленный белый лиса перепрыгивает через ленивую собаку ».
Затем SUBSTITUTE («Медленная белая лиса перепрыгивает через ленивую собаку», C5, C6), которая оценивается как «Медленная белая лиса. лев перепрыгивает через ленивую собаку ».
Разница между ЗАМЕНА и ЗАМЕНИТЬ
Аналогичная функция ЗАМЕНЫ - ЗАМЕНИТЬ. В большинстве случаев они могут использоваться как взаимозаменяемые, однако применяется следующее практическое правило.
Функцию ЗАМЕНА следует использовать, когда строка текста быть замененным известен, или заменяется целое слово или слова, как в нашем примере с лисой, который использовался ранее.
| 1 | = ПОДСТАВИТЬ (C2; C3; C4) |
Функцию REPLACE следует использовать, когда положение текстовых символов быть замененным в строке известны, или когда заменяется часть слова или строки. Например, удаление дефиса из строки чисел.
Функция REPLACE также может быть вложенной, как SUBSTITUTE, однако важно отметить, что относительное положение начального символа может измениться в зависимости от длины строки, которая должна быть заменена на первом шаге вложенной формулы.
Например, замена «быстрого коричневого» на «медленного белого» означает замену 11 символов на 10 символов. Следовательно, если они вложены, начальный символ для второго REPLACE будет на 1 символ отличаться от начальных позиций символа.
ЗАМЕНА Советы
- ПОДСТАВИТЬ можно использовать с подстроками:
- Помните, что вам нужно указать instance_num, если вы пытаетесь заменить набор символов, которые повторяются в строке:
Обратите внимание, что 3030 неверно, поскольку и «20», и «20» в «2020» были заменены на «30».
Заинтересованы в дополнительных функциях текста?
См. Другие наши статьи, чтобы узнать больше о REPLACE или о том, как используются другие текстовые функции в Excel, такие как LEFT, MID и RIGHT.
ЗАМЕНА в Google Таблицах
Функция ЗАМЕНА в Google Таблицах работает точно так же, как и в Excel:
Дополнительные замечания
Функция ПОДСТАВИТЬ чувствительна к регистру !. Вы можете использовать функции LOWER или UPPER для преобразования ваших строк текста в согласованный регистр перед использованием функции SUBSTITUTE. Затем, при желании, вы можете использовать функцию PROPER, чтобы сделать первую букву каждого слова заглавной.
В качестве альтернативы вы можете использовать функции ПОИСК и ЗАМЕНИТЬ для имитации подстановки без учета регистра. Используйте функцию ПОИСК, чтобы найти начальную позицию текстовой строки. Функция ПОИСК не чувствительна к регистру. Затем используйте функцию REPLACE для замены текста. Вам нужно будет использовать результат функции SEARCH в качестве ввода start_num в REPLACE. Вам также нужно будет определить количество заменяемых символов (num_chars). Вы можете подсчитать их вручную или использовать функцию LEN для подсчета количества символов. Пример:
Заменить - удалить дефисы
Распространенный вопрос - как убрать дефис в тексте. Уловка состоит в том, чтобы заменить дефис ничем.
В этом примере предположим, что в ячейке A1 есть текст с дефисами. Вот как их удалить
1. В пустой вспомогательной ячейке введите = ПОДСТАВИТЬ (
2. Щелкните ячейку, в которой вы хотите заменить символы, или A1 (формула автоматически заполняется).
3. Введите (запятая)
4. Введите «-»
5. Введите (запятая)
6. Введите «» и нажмите Enter.
Ваш конечный результат в вашей вспомогательной ячейке должен выглядеть так:
= ПОДСТАВИТЬ (A1; »-«; »»)
Еще 3 примера замены:
Глядя на ячейку B3, мы хотим заменить слово «больной» на «большой». Мы можем использовать следующее выражение:
| 1 | = ПОДСТАВИТЬ (B3; «больной»; «большой»; 1) |
Если текст, который мы хотим заменить, встречается более одного раза, нам нужно указать экземпляр, который мы имеем в виду. Во втором примере слово «старый» встречается дважды. Итак, если мы хотим заменить второй экземпляр на слово «серый», мы должны использовать:
| 1 | = ПОДСТАВИТЬ (B4; «старый»; «серый»; 2) |
Обратите внимание: если вхождение не указано, заменяются все экземпляры. Так:
| 1 | = ПОДСТАВИТЬ (B4, «старый», «серый») |
Избавился бы от всех вхождений слова «старый» и заменил бы их словом «серый». Обратите внимание: если старый текст не может быть найден, строка не изменяется. Итак, посмотрим на последний пример:
| 1 | = ПОДСТАВИТЬ (B5; «черный»; «серый»; 1) |
Означает, что мы пытаемся заменить слово «черный» словом «серый». Однако, поскольку «черный» не встречается, оригинальная струна осталась левитрой, поставленной в США, без изменений:
Чтобы загрузить файл .XLSX из этой статьи, щелкните здесь
ПОДМЕНЯТЬ Примеры в VBA
Вы также можете использовать функцию ЗАМЕНА в VBA. Тип:application.worksheetfunction.substitute (текст, старый_текст, новый_текст, номер_экземпляра)
Для аргументов функции (текста и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные, которые будут использоваться вместо них.
Когда выполняется следующий код
| 1 | Диапазон ("A1") = WorksheetFunction.Substitute (Range ("A1"), "using", "", 1) |
Содержимое ячейки A1 станет:
Итак, слово «использование» было заменено пустой строкой. Последний параметр (1) указывает, какой экземпляр текста следует заменить, если текст присутствует более одного раза. Так, например, если мы выполним следующий оператор
| 1 | Range ("A1") = WorksheetFunction.Substitute (Range ("A1"), "the", "an", 1) |
Ячейка A1 будет выглядеть так
Обратите внимание, что первое появление «the» было заменено на «an», в то время как второе «the» не изменилось. Если бы мы пропустили последний параметр, оба слова «the» были бы заменены
Вернуться к списку всех функций в Excel
