Автоматическая замена текста в excel

Как найти и заменить цвет заливки в Excel?

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

Найти и заменить цвет заливки с помощью функции поиска и замены

Чтобы найти ячейки с одинаковым цветом заливки и заменить их другим цветом, который вам нравится, функция «Найти и заменить» в Excel может помочь вам завершить эту работу. Пожалуйста, сделайте следующее:

1. Выберите ячейки диапазона, которые вы хотите найти, и замените цвет заливки.

2, Нажмите Ctrl + H для открытия Найти и заменить диалоговое окно, см. снимок экрана:

3. Затем нажмите Опции >> кнопку, чтобы развернуть Найти и заменить диалоговое окно, в развернутом диалоговом окне щелкните Формат Кроме того, Найти то, что раздел и выберите Выбрать формат из ячейки из выпадающего списка см. снимок экрана:

4. Затем щелкните, чтобы выбрать одну ячейку, содержащую цвет, который вы хотите заменить, после выбора цвета ячейки, затем щелкните Формат Кроме того, Заменить , и выберите Формат из выпадающего списка, см. снимок экрана:

5. В выскочившем Заменить формат диалоговом окне выберите один понравившийся цвет под Заполнять вкладку, см. снимок экрана:

6. Затем нажмите OK вернуться к Найти и заменить диалоговое окно, нажмите Заменить все кнопку, чтобы заменить цвет сразу, и появится окно с подсказкой, чтобы напомнить вам количество замен, см. снимок экрана:

7. Затем нажмите OK и закрыть Найти и заменить вы увидите, что ячейки с указанным вами цветом заливки были заменены другим цветом.

Найти и заменить цвет заливки с помощью Kutools for Excel

Если вас интересуют другие решения для этой задачи, я могу представить вам удобный инструмент — Kutools for Excel, С его Выбрать ячейки с форматом Утилита, вы можете быстро выделить все ячейки с одинаковым форматированием с определенной ячейкой.

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Kutools > Выберите > Выбрать ячейки с форматом, см. снимок экрана:

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

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

5. Затем нажмите OK, все атрибуты выбранной ячейки были перечислены в диалоговом окне, и только отметьте Цвет фона под Заполнять вариант, см. снимок экрана:

6, Затем нажмите Ok, сразу же выбираются все ячейки, заполненные этим цветом, затем щелкните Главная > Цвет заливкии выберите один цвет, который вы хотите, и цвет выбранных ячеек был заменен, как показано на следующем снимке экрана:

Демо: найти и заменить цвет заливки с помощью Kutools for Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Замена по списку регулярных выражений

В SEO-Excel уже есть инструмент который использует регулярные выражение, он называется РегВыр. Он вытаскивает из строки подстроку которая соответствует паттерну регулярного выражения. В отличие от него, Замена по регулярному выражению позволяет найти подстроку которая соответствует паттерну и заменить на то что вам необходимо. Чаще всего необходимо просто удалить, то есть заменить на “ничего”.

На гиф выше демонстрируется 2 примера: 1) С помощью регулярного выражение производится поиск английских слов и затем удаление их из строки 2) С помощью регулярного выражения d.*d производится поиск цифр в строке и затем их удаление. Причем вся соль в том что цифра может быть любая и в любом месте строки.

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

Замена части строки в – частая задача при обработке текстовых данных. Конечно, вы можете воспользоваться окном «Найти и заменить», но что если нужно сохранить исходный текст? В Excel есть две функции, позволяющие сделать замену, они достаточно гибкие и, в комбинации с другими текстовыми функциями, позволяют решить большинство задач:

1. Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;).

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

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

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

Примеры применения функции ПОДСТАВИТЬ

Эту функцию применяют, когда вы знаете какой текст нужно заменить, но не знаете, где он расположен.

1. Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

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

• Исходный текст – строка или ссылка, в которой нужно произвести замену
• Начальная позиция – порядковый номер первого символа для замены
• Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
• Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

Комбинируйте функции замены с функциями ДЛСТР, ПОИСК, НАЙТИ и другими, чтобы добиться наилучших результатов. Изучите правила написания формул, использования функций, чтобы правильно применять описанные функции.

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ»

При замене нескольких значений на одно и то же механика работы формул на основе нескольких уровней вложенности не будет отличаться от замены нескольких на несколько. Просто третий аргумент (на что заменить) на всех уровнях вложенности будет один и тот же. Кстати, если оставить его пустым (кавычки без символов между ними), то это позволит удалить определенные символы. Пример — удалить цифры из ячейки путем замены на пустоту:

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel «из коробки», но формулы ниже доступны бесплатно, если установить надстройку !SEMTools

Регулярные выражения (RegEx, регулярки) — наиболее удобное решение, когда нужно заменить несколько символов на один. Все эти несколько символов обычным способом безо всяких разделителей нужно перечислить внутри квадратных скобок. Примеры формул:

Первая заменяет на символ «#» все цифры, вторая — все английские буквы, а третья — все кириллические символы в верхнем и нижнем регистре. Четвертая заменяет любые пробелы, в т.ч. табуляцию и переносы строк, на нижнее подчеркивание.

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

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

Синтаксис и параметры метода

Синтаксис

Синтаксис при замене подстроки и присвоении переменной возвращаемого значения типа Boolean:

Синтаксис при замене подстроки без присвоения переменной возвращаемого значения:

• variable – переменная (тип данных – Boolean);
• expression – выражение, возвращающее объект Range.

Параметры

Параметр	Описание
What	Искомая подстрока или шаблон*, по которому ищется подстрока в диапазоне ячеек. Обязательный параметр.
Replacement	Подстрока, заменяющая искомую подстроку. Обязательный параметр.
LookAt	Указывает правило поиска по полному или частичному вхождению искомой подстроки в текст ячейки:1 (xlWhole) – поиск полного вхождения искомого текста;2 (xlPart) – поиск частичного вхождения искомого текста. Необязательный параметр.
SearchOrder	Задает построчный или постолбцовый поиск:1 (xlByRows) – построчный поиск;2 (xlByColumns) – постолбцовый поиск. Необязательный параметр.
MatchCase	Поиск с учетом или без учета регистра:0 (False) – поиск без учета регистра;1 (True) – поиск с учетом регистра. Необязательный параметр.
MatchByte	Способы сравнения двухбайтовых символов:0 (False) – двухбайтовые символы сопоставляются с однобайтовыми эквивалентами;1 (True) – двухбайтовые символы сопоставляются только с двухбайтовым символами. Необязательный параметр.
SearchFormat	Формат поиска. Необязательный параметр.
ReplaceFormat	Формат замены. Необязательный параметр.

* Смотрите знаки подстановки для шаблонов, которые можно использовать в параметре What.

Замена содержимого ячейки в Excel

Бывают случаи, когда допущена ошибка, повторяющаяся во всей книге Excel. Например, неправильно написано чье-то имя или необходимо изменить определенное слово или фразу на другую. Вы можете воспользоваться инструментом Найти и заменить, чтобы быстро внести исправления. В нашем примере мы воспользуемся командой Заменить, чтобы исправить список адресов электронной почты.

1. На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
2. Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
3. Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
4. Если значение будет найдено, то содержащая его ячейка будет выделена.
5. Посмотрите на текст и убедитесь, что Вы согласны заменить его.
6. Если согласны, тогда выберите одну из опций замены:
   • Заменить: исправляет по одному значению зараз.
   • Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.
7. Появится диалоговое окно, подтверждающее количество замен, которые будут сделаны. Нажмите ОК для продолжения.
8. Содержимое ячеек будет заменено.
9. Закончив, нажмите Закрыть, чтобы выйти из диалогового окна Найти и заменить.

7696013.04.2017 Скачать пример

Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ(Ячейка; Старый_текст; Новый_текст; Номер_вхождения)

где

• Ячейка — ячейка с текстом, где производится замена
• Старый_текст— текст, который надо найти и заменить
• Новый_текст— текст, на который заменяем
• Номер_вхождения— необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

• Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
• Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
• Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

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

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl+H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM). Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Ссылки по теме

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

Как работает инструмент замены символов в Excel

Конечно, если вам нужно заменить один-два символа, то это можно сделать и вручную. В остальных случаях подобный подход оправдан не будет. У Excel есть встроенный поиск, благодаря которому вы можете найти и заменить необходимые комбинации символов. Также есть отдельный инструмент для этого предназначенный.

Рассмотрим оба варианта.

Вариант 1: Поиск и замена

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

1. Во вкладке с инструментами “Главная” нажмите по инструменту “Заменить”. Он находится в группе инструментов “Найти и выделить”.

Запустится окошко “Найти и заменить”. Там переключитесь во вкладку “Заменить”. Введите в поле “Найти” нужный символ или их комбинацию. В поле “Заменить на” укажите символы или комбинацию, на которые нужно выполнить замену. Нажмите кнопку “Найти далее”. Можете также нажать кнопку “Заменить все” в том случае, если точно уверены, что не сделаете лишних замен.</li>Если вы нажали “Найти далее” у вас будет показан первый же найденный результат в документе. Если его требуется заменить, то нажмите соответствующую кнопку, если же нет, то жмите “Найти далее” для перехода к следующему результату.</li>

Проделывайте эти действия, пока не замените все, что необходимо.</li></ol>

Также этот вариант можно реализовать немного иначе:

1. Введите искомую комбинацию символов и комбинацию для замены. Нажмите “Найти все”.
2. Все найденные символы будут отображены в виде перечня в нижней части окошка. Там будет указана на каком листе они встречаются и в какой ячейке. Выделите ту комбинацию, которую нужно заменить. Нажмите кнопку “Заменить” для замены на ранее указанную комбинацию.

</ol>

Вариант 2: Автоматическая замена

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

1. Вызовите окошко поиска и замены символов по аналогии с предыдущей инструкцией.
2. Пропишите в поля “Найти” и “Заменить на” соответствующие значения.
3. Нажмите кнопку “Заменить все”.
4. Обычно процедура выполняется моментально, но если в документе много заменяемых элементов, то процесс может занять пару секунд. По завершении вы получите оповещение об успешной замене.

Настройка дополнительных параметров замены

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

1. Находясь в окошке поиска и замены нажмите по кнопке “Параметры” для перехода к расширенным настройкам.
2. У вас появятся дополнительные настраиваемые параметры, которые должны учитываться при поиске и замене. Например, можно учитывать регистр и/или проводить замены всех символов на ячейке целиком, а не только тех, что были указаны в “Найти”. Для этого установите галочки напротив соответствующих параметров.
3. Дополнительно можно задать формат искомых символов. Например, сделать так, чтобы программа искала совпадения только в ячейках с денежным форматом, а остальные игнорировала. Для указания формата нажмите по соответствующей кнопки напротив строки ввода.

Также можно настроить форматирование ячеек после замены. Для этого тоже нажмите на кнопку “Формат”, но только на ту, что расположена напротив строки “Заменить на”.</li>Настройки формата для изменяемых ячеек можно экспортировать из уже имеющихся. Для этого кликните правой кнопкой мыши по кнопке “Формат” и выберите в контекстном меню вариант “Выбрать формат из ячейки”.</li>Укажите ячейку, из которой будут выбраны настройки форматирования.</li></ol>

Выполнить автоматическую замену символов в табличном редакторе Excel совершенно несложно. Это можно сделать двумя представленными способами, а если предложенного функционала недостаточно, то его можно немного расширить, нажав по кнопке “Параметры”.

• https://exceltable.com/funkcii-excel/primer-funkcii-zamenit
• https://ru.extendoffice.com/excel/functions/excel-substitute-function.html
• https://office-guru.ru/excel/poisk-i-zamena-v-excel-15.html
• https://www.planetaexcel.ru/techniques/7/2775/
• https://public-pc.com/instrument-zameny-simvolov-v-tablichnom-redaktore-excel/

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки – весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

• Ячейка – ячейка с текстом, где производится замена
• Старый_текст – текст, который надо найти и заменить
• Новый_текст – текст, на который заменяем
• Номер_вхождения – необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

• Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 – обе “Маши” заменены на “Олю”).
• Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая “Маша” заменена на “Олю”).
• Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. “маша” написана с маленькой буквы)

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

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом – спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами – заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея – та же.

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

1. заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
2. ищем позицию символа # функцией НАЙТИ (FIND)
3. вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст. Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст. Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст

*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды. Производя замену

?» (для тильды —

») мы также без проблем сможем заменить или удалить спецсимвол.

14 комментариев

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое! Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке? Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель: 1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные. 2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456. Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

Здравствуйте, Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

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

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо! 262-о на О-262 и.тд. 263-о 264-о 265-о 266-о 267-о 268-о 269-о 270-о 271-о 271-о-а 272-о 273-о 274-о 275-о 276-о 277-о 278-о 279-о 280-о 281-о 282-о 283-о 284-о 285-о 286-о 287-о 288-о 289-о 290-о 291-о 291-о-а 292/1-о 292-о 293-о 294-о 295-о 296-о 297-о 297/1-о 298-о 298-а-о 299-о 300-о 301-о 302-о 303-о 304-о 305-о 306-о 307-о 308-о 309-о 310-о 311-о 312-о 313-о 314-о 315-о 316-о 317-о 318-о 319-о 320-о 321-о 322-о 323-о 324-о 326-о 325-о 327-о 329-о 330-о 331-о 332-о 333-о 334-о 335-о 336-о 337-о 338-о 339-о 340-о 342-о 343-о 344-о 345-о 347-о 348-о 349-о