Как сделать массовую замену в экселе
Добрый день уважаемый читатель!
Ищем в документе
Замена найденной информации
Ищем по форматам данных
Окромя данных и любых прочих символов, Excel также, может найти все ячейки по указанным форматам и как следствие заменить найденный формат на необходимый.
Kutools for ExcelАвтора Найти и заменить функция может помочь вам найти и заменить значения из открытых книг или конкретных рабочих листов, которые вам нужны.
Найти И Заменить Сразу Несколько Значений Кодом VBA
Если вы устали от поиска и замены значений снова и снова, следующий код VBA может помочь вам сразу заменить несколько значений вашими необходимыми текстами.
4, Затем нажмите F5 для запуска этого кода, в раскрывающемся окне подсказки укажите диапазон данных, который вы хотите заменить значениями новыми значениями.
5. Щелчок OK, и появится другое окно подсказки, чтобы напомнить вам, выберите критерии, которые вы создали на шаге 1. Смотрите скриншот:
Функция REPLACE (ЗАМЕНИТЬ) в Excel используется для замены части текста одной строки, другим текстом.
Поиск
Чтобы найти что-то, нажмите 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 PriceLSVand_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 позволяет в пару кликов производить замены на всех уровнях:
- символов и их сочетаний
- паттернов регулярных выражений
- слов!
- целых ячеек (В некоторой степени аналог ВПР)
Меню инструментов массовой замены в !SEMTools
При этом процедуры изменяют исходный диапазон, что экономит время. Все что нужно — предварительно выделить его, определиться с задачей, вызвать нужную процедуру и выделить 2 столбца сопоставления заменяемых и замещающих значений (предполагается, что если вы знаете, что на что менять, то и такие списки есть).
Пример: замена символов по вхождению
Аналог обычной процедуры замены без учета регистра заменяемых символов, по вхождению. С одним отличием — здесь замена массовая и можно выбрать сколько угодно строк с парами заменяемое-заменяющее значение.
Ниже пример с единичными символами, но паттерны могут быть какими угодно в зависимости от вашей задачи.
Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)
Пример: замена списка слов на другой список слов
На этом примере — замена списка слов на другой список, в данном случае на одно и то же слово. Здесь решается задача типизации разнородных фраз путем замены слов, содержащих латиницу и цифры, на одно слово. Далее после этой операции можно будет посчитать уникальные значения в столбце, чтобы выявить наиболее популярные сочетания.
С версии !SEMTools 9.18.18 появилась опция — при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется:
Хотите так же быстро производить массовую замену в Excel?
Смотрите также по теме поиска и замены данных в Excel:
- Найти заглавные/строчные буквы в ячейке
- Найти латиницу или кириллицу в тексте
- Найти числа в текстовых ячейках
- Обнаружить текстовые символы
- Функция НАЙТИ в Excel
- Функция ПОИСК в Excel
- Функция ЗАМЕНИТЬ в Excel
- Найти определенные символы в ячейках Excel
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция СЖПРОБЕЛЫ
Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.
Синтаксис: =СЖПРОБЕЛЫ(текст)
Пример использования:
=СЖПРОБЕЛЫ( ” Текст с лишними пробелами между словами и по краям “ )
Результатом выполнения функции будет строка: “Текст с лишними пробелами между словами и по краям” .
Читайте также: