Как сделать массовую замену в экселе

Добавил пользователь Валентин П.
Обновлено: 04.10.2024

Naiti i zamenit 1 Как работает инструмент Найти и заменить в Excel?

Добрый день уважаемый читатель!

Ищем в документе

Замена найденной информации

Ищем по форматам данных

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

Kutools for ExcelАвтора Найти и заменить функция может помочь вам найти и заменить значения из открытых книг или конкретных рабочих листов, которые вам нужны.

Найти И Заменить Сразу Несколько Значений Кодом VBA

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


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

5. Щелчок OK, и появится другое окно подсказки, чтобы напомнить вам, выберите критерии, которые вы создали на шаге 1. Смотрите скриншот:

Функция REPLACE (ЗАМЕНИТЬ) в Excel используется для замены части текста одной строки, другим текстом.

Поиск

Чтобы найти что-то, нажмите CTRL+Fили перейдите на главная > редактирования> Найти & Выберите >Найти.

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

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

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

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

Используйте знак вопроса (?), чтобы найти любой отдельный символ. Например, если найти “м?т” и “множество”, то будут отспросюмы.

Чтобы найти любое количество символов, используйте звездку (*). Например, если найти знаки “г*д” и “начало”, они будут отозныны.

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

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

В: Чтобы найти данные на листе или во всей книге, выберите Листили Книга.

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

Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите формулы, значения,заметки или примечания.

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

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

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

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

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

Синтаксис функции

ЗАМЕНИТЬ ( исходный_текст ; нач_поз ; число_знаков ; новый_текст )

Исходный_текст — текст, в котором требуется заменить некоторые знаки. Нач_поз — позиция знака в Исходном_тексте , начиная с которой знаки заменяются текстом новый_текст . Число_знаков — число знаков в Исходном_тексте , которые заменяются текстом новый_текст . Новый_текст — текст, который заменяет знаки в Исходном_тексте .

Что возвращает функция

Возвращает текстовую строку, в которой часть текста заменена на другой текст.

ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)

​Смотрите также​Владимир​ И характеристики а​​ в ячейке д1​​ ” ” &​​ ввиду.​​ 10. Макросом вы​

Описание

​ А сейчас подумалось,​ As Integer Dim​ =”68.”, то заменить​ посредством телепатии об​

​ Но сегодня настоятельно​ старого текста выбрали​ номера не будет​ количество символов, начиная​

​ на пересечении заданных​​ Обязательный. Позиция символа в​

​В этой статье описаны​: Sub Change() Range(“D6”).Activate​ отдельных полях для​

​ должен быть список​ x(3) & “​Dophin​ это не сделаете,​ что и ПОДСТАВИТЬ,​ Price iLastRow =​ её на “аа”​ этом узнать?​ рекомендуется отказываться от​

​ ячейку А2, в​ удалён, лишь добавятся​ с указанной позиции.​ строки и столбца.​ старом тексте, начиная​ синтаксис формулы и​

​ Do If Len(ActiveCell)​ отбора. Тогда точно​ значений соответствующий столбцу​ ” & Target​: тип того?​ только можно формулой.​ и ЗАМЕНИТЬ -​ Cells(Rows.Count, 2).End(xlUp).Row For​Не хватает знания​LSV​ данной функции так​ качестве нач_поз установили​

​ 2 дефиса.​ Чтобы заменить определённую​25-й день марафона мы​ с которого требуется​ использование функций​

Синтаксис

​ не поставишь такого​

​McCinly​​Поиск и замена​ те же текстовые​ i = 2​

​ формул в Excel​​:​ как она имеет​ число 5, так​=REPLACE(REPLACE(B3,4,0,”-“),8,0,”-“)​ строку текста в​

​ посвятим изучению функции​​ выполнить замену новым​ЗАМЕНИТЬ​ ActiveCell = ActiveCell.Offset(,​ крана, какого нет.​

​ диаметра 50 (см.​​ & x(5) &​: Хорошая иллюстрация )))​ работают не только​

​ функции, и какая​​ To iLastRow Price​LSV​and_evg​

Пример

​ свои ограничения и​ как именно с​=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3;4;0;”-“);8;0;”-“)​ любом месте текста​REPLACE​ текстом.​и​ 6) ActiveCell.Offset(1).Activate Loop​ И вводить не​ пример)​ ” ” &​ Два знака меняет,​ с текстом, но​

​ из них быстрее/медленнее​

​ более требовательна к​

​ пятой позиции слова​

​Урок подготовлен для Вас​

​ оригинала, Вы можете​

​(ЗАМЕНИТЬ), которая относится​Число_знаков​ЗАМЕНИТЬБ​ Until IsEmpty(ActiveCell) End​

​При введении числа​

​ x(6)​ ставишь 100, потом​ и с формулой.​

​_Boroda_​ написал справа что​ ресурсам в сравнении​

​ “Молоко” мы символы​

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

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

Примеры

Формула =СОВПАД(“ПЕРВЫЙ”;”Первый”) вернет ЛОЖЬ, т.е. 2 текстовых значения не совпадают с учетом РЕгиСТРа . Формула =СОВПАД(“ПЕРВЫЙ”;”ПЕРВЫЙ”) вернет ИСТИНА.

Пусть в ячейке А2 введена строка Первый канал – лучший , а в ячейке A3 введена строка ПЕРВЫЙ канал – лучший . Для сравнения строк с учетом РЕгиСТРа используйте формулу =СОВПАД(A2;A3) , для сравнения значений без учета РЕгиСТРа используйте формулу =A2=A3 .

Найти точные совпадения

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

Синтаксис

=REPLACE(old_text, start_num, num_chars, new_text) – английская версия

=ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст) – русская версия

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

Массовая замена в Excel — довольно частая потребность. Очень часто нужно массово и при этом быстро заменить несколько символов, слов и т.д. на другие. При этом на текущий момент простого инструмента в стандартном функционале Excel нет.

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

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


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

Но у решения есть и свои недостатки:

  • Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя, в некоторых случаях, как пример на картинке выше, это и преимущество.
  • максимум 64 замены — хоть и много, но все же ограничение.
  • формально процедура замены таким способом будет происходить массово и моментально, однако, длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

Файл доступен по ссылке, но можно и не скачивать его, а просто скопировать текст формулы ниже и вставить ее в любую ячейку, кроме диапазона A1:B64. Формула заменяет в ячейке C1 значения в столбце A стоящими напротив в столбце B.

А вот и она сама (тройной клик по любой части текста = выделить всю формулу). Обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит ее работу.

Заменить все или только выбранные результаты поиска

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

  • символов и их сочетаний
  • паттернов регулярных выражений
  • слов!
  • целых ячеек (В некоторой степени аналог ВПР)

Массовая замена в Excel с !SEMTools

Меню инструментов массовой замены в !SEMTools

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

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:

Хотите так же быстро производить массовую замену в Excel?


Смотрите также по теме поиска и замены данных в Excel:

  • Найти заглавные/строчные буквы в ячейке
  • Найти латиницу или кириллицу в тексте
  • Найти числа в текстовых ячейках
  • Обнаружить текстовые символы
  • Функция НАЙТИ в Excel
  • Функция ПОИСК в Excel
  • Функция ЗАМЕНИТЬ в Excel
  • Найти определенные символы в ячейках Excel

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Функция СЖПРОБЕЛЫ

Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )

Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .

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