Как заменить символ в ячейке в Excel

Обновлено: 03.07.2024

Если вам нужно внести изменения в большой текст, функции Excel могут значительно упростить этот процесс. Обе функции REPLACE и SUBSTITUTE могут помочь, но обе они делают это по-разному.

Давайте посмотрим, как работают эти две функции, чем они отличаются и как их можно использовать в реальной электронной таблице.

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

Оглавление

ЗАМЕНИТЬ или ПОДСТАВИТЬ

Прежде чем мы углубимся в детали, мы должны рассмотреть тонкую разницу между функциями REPLACE и SUBSTITUTE.

Оба они заменяют строку (или часть строки) текста другой строкой.

Разница заключается в том, как определяется первая строка.

REPLACE выбирает первую строку в зависимости от позиции. Таким образом, вы можете заменить четыре символа, начиная с шестого символа в строке.

SUBSTITUTE выбирает на основе того, соответствует ли строка предварительно определенному поиску. Например, вы можете указать Excel заменить любой экземпляр «2016» на «2017».

В остальном эти две функции идентичны.

Я буду использовать слово «заменить», чтобы рассказать об обоих этих действиях. Если вы видите слово «заменить», написанное строчными буквами, я имею в виду действие, а не функцию.

Каспер Лангманн, соучредитель Spreadsheeto

Получите БЕСПЛАТНЫЙ файл с упражнениями

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

Загрузите его ниже и следуйте инструкциям в статье. Учиться будет намного легче!

Загрузите БЕСПЛАТНЫЙ файл упражнения

Замена символов с помощью REPLACE

Начнем с REPLACE. Давайте сначала посмотрим на синтаксис:

Синтаксис функции ЗАМЕНИТЬ

=REPLACE(old_text, start_num, num_chars, new_text)

old_text — это ячейка, содержащая текст, который вы хотите заменить.

start_num сообщает Excel, где в ячейке начинается текст, который вы хотите заменить.

num_chars — это количество символов, которые вы хотите заменить.

new_text — это текст, который вы хотите заменить этим количеством символов.

Имейте в виду, что new_text не обязательно должен иметь ту же длину, что и num_chars. Вы можете указать Excel заменить «K» на «Kasper», и это сработает.

Откройте пример рабочей книги и посмотрите на первый лист. Вы увидите, что у нас есть последовательность идентификационных номеров с соответствующими датами рождения и именами в другой ячейке.

Мы воспользуемся REPLACE, чтобы удалить идентификационные номера и заменить их именами. В конце концов, имена и даты будут в одних и тех же ячейках, и мы избавимся от идентификационных номеров (предположительно конфиденциальных).

Чтобы начать, щелкните ячейку C2.

Затем введите следующую формулу:

=ЗАМЕНИТЬ(A2, 1, 10, B2)

Взглянув на определение синтаксиса, вы увидите, что A2 — это старый_текст, а B2 — новый_текст. Таким образом, Excel возьмет текст из B2 и заменит им некоторую строку из A2. Мы используем 1 в качестве start_num, чтобы убедиться, что Excel начинается с начала ячейки. И поскольку идентификационные номера состоят из 10 цифр, мы использовали 10 в качестве num_chars.

Если бы вы прочитали эту формулу, вы бы сказали примерно следующее: "Замените десять символов, начиная с первого, из ячейки A2 содержимым ячейки B2".

Нажмите Enter и посмотрим, что произойдет.

Excel взял первые десять цифр A2 и заменил их содержимым B2! Именно то, что мы хотели.

Теперь используйте маркер заполнения, чтобы перетащить B2 вниз в конец столбца:

Независимо от того, сколько символов содержится в имени каждой записи, Excel заменяет только десять символов.

Каспер Лангманн, соучредитель Spreadsheeto

ЗАМЕНА работает, если вы всегда заменяете одно и то же количество символов, и они всегда находятся в одном и том же месте в ячейке.

Давайте рассмотрим функцию ЗАМЕНА, которая может оказаться более полезной, если ваши данные не настолько чисты, как это необходимо для ЗАМЕНЫ.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Используйте функции «Найти и заменить» в Excel для поиска чего-либо в книге, например определенного числа или текстовой строки. Вы можете либо найти элемент поиска для справки, либо заменить его чем-то другим. Вы можете включать подстановочные знаки, такие как вопросительные знаки, тильды и звездочки, или цифры в условия поиска. Вы можете выполнять поиск по строкам и столбцам, по комментариям или значениям, а также по листам или целым книгам.

Чтобы что-то найти, нажмите Ctrl+F или выберите Главная > Редактирование > Найти и выделить > Найти.

Примечание. В следующем примере мы нажали кнопку Параметры >>, чтобы отобразить диалоговое окно поиска целиком. По умолчанию он отображается со скрытыми параметрами.

В поле Найти что: введите текст или цифры, которые вы хотите найти, или нажмите стрелку в поле Найти что: и выберите элемент недавнего поиска из списка.

Советы. В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

Используйте тильду (~), а затем ?, * или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91~? находит "fy91?".

Нажмите «Найти все» или «Найти далее», чтобы выполнить поиск.

Совет. Когда вы нажимаете кнопку "Найти все", все вхождения критериев, которые вы ищете, будут перечислены, а щелчок по определенному вхождению в списке выберет его ячейку. Вы можете отсортировать результаты поиска «Найти все», щелкнув заголовок столбца.

Нажмите "Параметры>>", чтобы при необходимости уточнить параметры поиска:

Внутри: для поиска данных на листе или во всей книге выберите «Лист» или «Книга».

Поиск. Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

Искать в: чтобы найти данные с конкретными деталями, в поле нажмите "Формулы", "Значения", "Примечания" или "Комментарии".

Примечание. Формулы, значения, примечания и комментарии доступны только на вкладке "Найти"; на вкладке "Заменить" доступны только формулы.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Соответствовать всему содержимому ячейки. Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные вами в поле «Найти:».

Если вы хотите найти текст или числа с определенным форматированием, нажмите «Формат», а затем выберите нужные параметры в диалоговом окне «Найти формат».

Совет. Если вы хотите найти ячейки, соответствующие определенному формату, вы можете удалить любые критерии в поле «Найти», а затем выбрать определенный формат ячейки в качестве примера. Нажмите стрелку рядом с полем «Формат», нажмите «Выбрать формат из ячейки», а затем щелкните ячейку с форматированием, которое вы хотите найти.

Заменить

Чтобы заменить текст или цифры, нажмите Ctrl + H или выберите Главная > Редактирование > Найти и выделить > Заменить.

В поле "Заменить на:" введите текст или цифры, которыми вы хотите заменить текст поиска.

Нажмите «Заменить все» или «Заменить».

Совет. При нажатии кнопки "Заменить все" все вхождения искомых критериев будут заменены, а "Заменить" будет обновляться по одному вхождению за раз.

Нажмите "Параметры>>", чтобы при необходимости уточнить параметры поиска:

Внутри: для поиска данных на листе или во всей книге выберите «Лист» или «Книга».

Поиск. Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Существует два разных метода поиска или замены текста или чисел на Mac. Первый — использовать диалоговое окно «Найти и заменить». Во-вторых, используйте панель поиска на ленте.

Диалоговое окно «Найти и заменить»

Панель поиска и параметры

Чтобы что-то найти, нажмите Ctrl+F или выберите Главная > Найти и выделить > Найти.

В поле Найти: введите текст или цифры, которые вы хотите найти.

Нажмите «Найти далее», чтобы начать поиск.

При необходимости вы можете уточнить параметры поиска:

Внутри: для поиска данных на листе или во всей книге выберите «Лист» или «Книга».

Поиск. Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

Чтобы заменить текст или цифры, нажмите Ctrl + H или выберите Главная > Найти и выделить > Заменить.

В поле "Найти" введите текст или цифры, которые вы хотите найти.

В поле "Заменить на" введите текст или цифры, которыми вы хотите заменить текст поиска.

Нажмите «Заменить» или «Заменить все».

При необходимости вы можете уточнить параметры поиска:

Внутри: для поиска данных на листе или во всей книге выберите «Лист» или «Книга».

Поиск. Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

Нажмите любую ячейку, чтобы выполнить поиск по всему листу, или выберите определенный диапазон ячеек для поиска.

Нажмите +F или щелкните значок, чтобы развернуть панель поиска, а затем в поле поиска введите текст или число, которое вы хотите найти.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

Нажмите клавишу возврата.

Чтобы найти следующий экземпляр элемента, который вы ищете, щелкните в поле поиска и нажмите клавишу «Ввод» или в диалоговом окне «Найти» нажмите «Найти далее».

Совет. Вы можете отменить текущий поиск, нажав ESC .

Чтобы указать дополнительные параметры поиска, нажмите на увеличительное стекло, а затем выберите «Поиск на листе» или «Поиск в книге». Вы также можете выбрать параметр «Дополнительно», который откроет диалоговое окно «Поиск».

Нажмите +F, чтобы развернуть панель поиска, а затем в поле поиска введите текст или число, которое вы хотите найти.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

В поле поиска нажмите на увеличительное стекло, а затем нажмите "Заменить".

В поле Заменить на введите заменяющие символы.

Чтобы заменить символы в поле "Найти" ничем, оставьте поле "Заменить на" пустым.

Нажмите "Найти далее".

Чтобы заменить выделенное вхождение, нажмите Заменить.

Чтобы заменить все вхождения символов на листе без предварительного просмотра, нажмите «Заменить все».

Совет. Отмените текущий поиск, просто нажав клавишу ESC.

Чтобы что-то найти, нажмите Ctrl+F или выберите Главная > Редактирование > Найти и выделить > Найти.

Примечание. В следующем примере мы нажали > Параметры поиска, чтобы отобразить все диалоговое окно поиска. По умолчанию он отображается со скрытыми параметрами поиска.

В поле Найти: введите текст или цифры, которые вы хотите найти.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

Нажмите «Найти далее» или «Найти все», чтобы выполнить поиск.

Нажмите > Параметры поиска, чтобы уточнить параметры поиска, если это необходимо:

В пределах: для поиска данных в пределах определенного выбора выберите «Выделение». Для поиска данных на листе или во всей книге выберите Лист или Книга.

Направление: можно выбрать поиск вниз (по умолчанию) или вверх.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Соответствовать всему содержимому ячейки. Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные вами в поле «Найти».

Заменить

В поле Найти: введите текст или цифры, которые вы хотите найти.

Используйте вопросительный знак (?), чтобы найти любой отдельный символ — например, s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например, s*d находит "sad" и "started".

В поле "Заменить на:" введите текст или цифры, которыми вы хотите заменить текст поиска.

Нажмите «Заменить» или «Заменить все».

Нажмите > Параметры поиска, чтобы уточнить параметры поиска, если это необходимо:

Направление: можно выбрать поиск вниз (по умолчанию) или вверх.

Учитывать регистр — установите этот флажок, если вы хотите искать данные с учетом регистра.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

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

Общая формула

=ПОДСТАВИТЬ(текстовая_строка,старый_текст,новый_текст)

Аргументы

Text_string: текстовая строка, содержащая определенный текст, который вы хотите заменить.

Old_text: текст, который вы хотите заменить.

Новый_текст: новый текст, которым вы хотите заменить старый текст.

Приведенные выше текстовые аргументы могут быть:

1. Текст заключен в кавычки;
2. Или ссылку на ячейку, содержащую текст.

Как использовать эту формулу?

Как показано на снимке экрана ниже, вы хотите заменить все пробелы в каждой ячейке диапазона B5:B9 дефисами. Это можно сделать следующим образом.

<р>1. Выберите пустую ячейку для вывода результата.

<р>2. Введите в него приведенную ниже формулу и нажмите клавишу Enter. Затем перетащите маркер заполнения, чтобы применить формулу к другим ячейкам.

=ПОДСТАВИТЬ(B3," ","-")

Примечание. Функция ПОДСТАВИТЬ чувствительна к регистру. Если вам нужно заменить текст другим и игнорировать регистр, используйте функцию ПРОПИСНЫЕ внутри функции ПОДСТАВИТЬ.
=ПОДСТАВИТЬ(ПРОПИСНАЯ("текстовая_строка"),ПРОПИСНАЯ("старый_текст"),"новый_текст")

Как работает эта формула?

=ПОДСТАВИТЬ(B3," ","-")

Здесь функция ПОДСТАВИТЬ заменяет все пробелы в ячейке B3 дефисами.

Связанные функции

Функция Excel ПОДСТАВИТЬ
Функция Microsoft Excel ПОДСТАВИТЬ заменяет текст или символы в текстовой строке другим текстом или символами.

Лучшие инструменты для повышения производительности в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите выполнять свою повседневную работу быстро и качественно? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

Рис. 1. Замена одного символа другим в Excel.

Если наша цель состоит в том, чтобы заменить/переключить один символ на другой в Excel, мы должны использовать функцию ПОДСТАВИТЬ.

Общая формула

=ПОДСТАВИТЬ(ссылка,старый,новый)

Мы можем заменить определенный текст в заданной строке, используя формулу ПОДСТАВИТЬ.

Как использовать функцию Excel ПОДСТАВИТЬ.

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

Введите текстовую строку или числовые значения, содержащие старый текст/символ, который мы хотим заменить, в помеченные столбцы нашего рабочего листа.

Рис. 2. Замена одного символа другим в Excel.

Обязательно оставьте пустые ячейки, чтобы Excel возвращал НОВУЮ текстовую строку или числовые значения.

Формула ЗАМЕНЫ, которую мы введем в ячейку B2 выше, выглядит следующим образом:

=ЗАМЕНИТЬ(A2,18,4,2019)

Рис. 3. Замена одного символа другим в Excel.

Наша цель — заменить часть «2012» СТАРОЙ текстовой строки на «2019», которая возвращается в ячейке B2.

Измените формулу ПОДСТАВИТЬ в ячейке B2 выше и введите в ячейку B3;

Рис. 4. Замена одного символа другим в Excel.

Примечание

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

В нашем примере с рабочим листом числа 18,4 соответствуют точному положению «2012» в старой текстовой строке, а числа 4,1 соответствуют точному положению «-» в нашем старом числовом значении.< /p>

Рисунок 5. Окончательный результат.

Мгновенное подключение к эксперту через нашу службу Excelchat:

Наш онлайн-сервис Excelchat всегда к вашим услугам. Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любые ваши вопросы по Excel. Гарантированное подключение в течение 30 секунд и индивидуальное решение для вас в течение 20 минут.

Это сообщение не ответило на ваш вопрос? Получите решение, связавшись с экспертом.

У меня есть два контрольных листа, и данные аналогичны. Можно ли копировать и заменять данные из одного в другой

Мне нужна формула для замены одного значения ячейки другим значением другой ячейки, чтобы сделать два значения ячеек равными. Мне нужно написать это как часть части [value_if_true] в формуле ЕСЛИ.

Читайте также: