Используйте функцию REPLACE в Excel, чтобы заменить ненужные текстовые данные в ячейке рабочего листа хорошими данными или вообще ничего.
Импортированные или скопированные данные иногда содержат нежелательные символы или слова вместе с хорошими данными. Функция REPLACE — это один из способов быстро исправить эту ситуацию, как показано в примере на рисунке выше.
Это особенно верно, когда длинные столбцы импортируемых данных нуждаются в исправлении, поскольку можно использовать дескриптор заполнения или скопировать и вставить, чтобы скопировать функцию REPLACE в несколько ячеек на листе.
Типы текстовых данных, которые может заменить функция:
- форматирование символов, вторая строка выше;
- знаки препинания, добавленные к номеру телефона, четвертый ряд выше;
- буквы алфавита, строка шесть выше.
Эту функцию также можно использовать для простого удаления нежелательных символов, заменяя их ничем — третья строка выше.
Синтаксис и аргументы функции REPLACE
Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы .
Синтаксис для функции REPLACE:
= REPLACE (Old_text, Start_num, Num_chars, New_text)
Old_text (обязательный) фрагмент данных, который необходимо изменить. Этот аргумент может быть:
- фактические данные, подлежащие изменению — текстовые или числовые данные;
- ссылка на ячейку для расположения данных на листе.
Start_num указывает начальную позицию — слева — символов в Old_text для замены.
Num_chars указывает количество символов, которые будут заменены после Start_num . Если пусто, функция предполагает, что никакие символы не должны быть заменены, и добавляет символы, указанные в аргументе New_text — строка три выше.
New_text указывает новые данные, которые будут добавлены. Если пусто, функция предполагает, что никакие символы не должны быть добавлены, и просто удаляет символы, указанные для аргумента Num_chars — строка четвертая выше.
#ИМЯ? и # ЗНАЧЕНИЕ! ошибки
#ИМЯ? происходит, если текстовые данные, введенные в качестве аргумента Old_text, не заключены в двойные кавычки, строка пять выше.
#СТОИМОСТЬ! происходит, если аргументы Start_num или Num_chars отрицательны или содержат нечисловые значения, строка восемь выше.
ЗАМЕНА и ошибки расчета
При использовании функции ЗАМЕНА с числами, как описано в шагах ниже, результаты формулы (24 398 долл. США) обрабатываются Excel как текстовые данные и могут возвращать неверные результаты, если они используются в вычислениях.
ЗАМЕНА против ЗАМЕНА
Идентична функции REPLACE по назначению, а синтаксис — REPLACEB.
Согласно справочному файлу Excel, единственное различие между ними заключается в группе языков, которые каждый из них должен поддерживать.
REPLACEB — для использования с версиями Excel, использующими языки набора двухбайтовых символов — например, японский, китайский (упрощенный), китайский (традиционный) и корейский.
REPLACE — для использования в версиях Excel, использующих языки однобайтовых символов, такие как английский и другие западные языки.
Пример использования функции REPLACE в Excel
В этом примере рассматриваются шаги, используемые для ввода функции REPLACE в ячейку C5 на изображении, чтобы заменить первые три символа текстовой строки ^ & # 24,398 знаком доллара ($), чтобы получить $ 24 398.
Варианты ввода функции ЗАМЕНА включают ввод вручную всей формулы:
= ЗАМЕНА (A5,1,3, «$») ,
или используя диалоговое окно функции — как описано ниже.
Хотя можно вручную ввести функцию и ее аргументы, часто проще использовать диалоговое окно, поскольку оно заботится о синтаксисе функции, например, в скобках и разделителях запятых между аргументами.
- Нажмите на ячейку C5 на рабочем листе, чтобы сделать ее активной ячейкой;
- Нажмите на вкладку « Формулы » в меню ленты;
- Выберите « Текст» на ленте, чтобы открыть раскрывающийся список функций;
- Нажмите на REPLACE в списке, чтобы открыть диалоговое окно функции;
- В диалоговом окне нажмите на строку Old_text ;
- Нажмите на ячейку A5 на листе, чтобы ввести ссылку на эту ячейку для аргумента Old_text ;
- Нажмите на строку Start_num ;
- Введите число 1 — начинает замену с первого символа слева
- Нажмите на строку Num_chars ;
- Введите число 3 в этой строке — первые три символа будут заменены;
- Нажмите на строку New_text ;
- Введите знак доллара ( $ ) — добавьте знак доллара в начало 24 398;
- Нажмите кнопку ОК, чтобы закрыть диалоговое окно и вернуться на лист
- Сумма в 24 398 долларов должна появиться в ячейке C5
- Если щелкнуть ячейку C5, полная функция = REPLACE (A5,1,3, «$») появится в строке формул над рабочим листом.
Функция ЗАМЕНА и значение вставки
REPLACE и другие текстовые функции Excel предназначены для того, чтобы оставить исходные данные в одной ячейке, а отредактированный текст — в другой.
Это сохраняет исходные данные без изменений для последующего использования или позволяет исправить любые проблемы, возникающие во время редактирования.
Однако иногда может быть предпочтительнее удалить исходные данные и просто сохранить отредактированную версию.
Для этого объедините вывод функции REPLACE со значением вставки, которое является частью специальной функции вставки Excel.
Результатом этого является то, что значения все еще будут присутствовать, но исходные данные и функция ЗАМЕНА могут быть удалены, оставляя только исправленные данные.