Excel vba replace несколько значений по маске

Обновлено: 04.07.2024

Предположим, что нам необходимо заменить все "ул." на листе на "улица ". Или русское слово "дом" на английское "house". Или еще интереснее: все английские буквы на русские. Например, английская "а" должна быть заменена на русскую "a", английская "c" на русскую "с", английская "H" на русскую "Н" и т.д. А такое тоже нередко бывает и доставляет проблемы. Ведь если в одной таблице будут русские буквы, а в другой английские - то применение большинства встроенных функций поиска(та же ВПР) просто не найдут соответствия.
Если подобную замену надо сделать для одного сочетания, то все просто: жмем Ctrl+H и указываем что заменить и на что. Но если таких замен надо сделать 20? Или 120? Это надо будет 120 раз нажать и ввести что заменять и на что. А если это надо сделать еще и не в одном документе - то. Думаю сами справитесь с умножением количества замен на количество файлов, в которых это надо сделать. И вроде бы простая операция превращается в ваш личный ад на работе.
Недавно на форуме участнику потребовалось автоматизировать именно такую штуку. Т.к. код несложный - решил написать и чуть дополнив выложить для всех кому код может потребоваться:

Option Explicit Sub Replace_Mass() Dim s As String Dim lCol As Long Dim avArr, lr As Long Dim lLastR As Long Dim lToFindCol As Long, lToReplaceCol As Long, lLookAt As Long 'запрашиваем направление перевода - с русского на англ. или наоборот lCol = Val(InputBox("Укажите направление перевода:" & vbNewLine & _ " 1 - ru-en" & vbNewLine & _ " 2 - en-ru", "Запрос", 1)) If lCol = 0 Then Exit Sub 'запрашиваем по части ячейки искать или по всему тексту 'по умолчанию - по части lLookAt = Val(InputBox("Искать соответствие по части ячейки или по всему тексту:" & vbNewLine & _ " 1 - по всему тексту" & vbNewLine & _ " 2 - по части ячейки", "Запрос", 2)) If lLookAt = 0 Then Exit Sub Select Case lCol Case 1 lToFindCol = 1 lToReplaceCol = 2 Case 2 lToFindCol = 2 lToReplaceCol = 1 End Select Application.ScreenUpdating = 0 'Получаем с листа Соответствия значения, которые надо заменить в выделенном диапазоне With ThisWorkbook.Sheets("Соответствия") lLastR = .Cells(.Rows.Count, 1).End(xlUp).Row avArr = .Cells(1, 1).Resize(lLastR, 2) End With 'заменяем For lr = 1 To UBound(avArr, 1) s = avArr(lr, lToFindCol) If Len(s) Then 'если значение для замены не пустое Selection.Replace s, avArr(lr, lToReplaceCol), lLookAt End If Next lr Application.ScreenUpdating = 1 End Sub

Как это работает. В книге есть специальный лист с именем "Соответствия". На нем в столбце А записаны слова, которые необходимо заменить, а в столбце В - на что эти слова заменить. Если в столбце А пусто - то замена не будет произведена. Если в столбце В пусто - то значение из столбца А будет просто удалено.
Замены производятся исключительно в выделенных на листе ячейках. Ячейки могут быть несмежными.

Итак, необходимо сделать много замен. Скачиваете файл:

Массовая замена слов (54,5 KiB, 8 923 скачиваний)

Примечание: Я сделал файл как переводчик. Т.е. в первом столбце русские слова, во втором английские. Но в столбцах может быть что угодно - хоть слова, хоть символы, хоть числа.
На лист "Соответствия" записываете в столбец А - что заменять, в столбец В - на что заменять. Переходите на лист книги, в котором необходимо произвести замену. Выделяете ячейки, значения в которых надо найти и заменить. После чего жмете Alt+F8 и выбираете макрос "Tips_Macro_ReplaceMASS.xls!Replace_Mass"(или просто "Replace_Mass", если код в той же книге, что и ячейки для поиска и замены).
Первым появится окно с запросом направления перевода. По умолчанию 1(ru-en). Т.е. будет браться слово из столбца А и заменяться словом из столбца В. Но если указать 2 - то будет браться слово из столбца В и заменяться словом из столбца А. Т.е. аналог переводчика - с рус. на англ. и наоборот. Либо из А в В, либо из В в А.
Вторым появится запрос на метод просмотра данных:

если указать "1 - по всему тексту" - данные из столбца А будут заменять только в том случае, если ячейка в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Соответствия". Например, в любой из выделенных ячеек записано "На столе книга", а на листе "Соответствия" в столбце А есть только слово "книга". Замена не будет произведена, т.к. необходимо, чтобы в столбце А было так же "На столе книга".
если указать "2 - по части ячейки" - данные из столбца А будут заменять в случае, если ячейка в выделенном для замены диапазоне содержит любое слово из столбца А листа "Соответствия". На том же примере - "На столе книга". Если выбрать 2, то в тексте "На столе книга" слово книга будет заменено на слово из столбца В - "book".

Массовая замена слов - статьи.xls (91,5 KiB, 2 320 скачиваний)

и в итоге за пару секунд получим следующий результат:

Достаточно выделить столбец со статьями на листе с реализацией и запустить код(либо нажатием кнопки заменить значения, либо нажав Alt+F8 и выбрав из списка макросов макрос Replace_Mass ).
После нажатия на кнопку будут запрошены следующие параметры:

указать номер столбца значений в листе "Справочник", в котором искать соответствия номерам статей(в нашем случае это столбец 1(А))
указать номер столбца, значениями которого заменять найденные в таблице реализации значения(это может быть один из трех столбцов справочника: Группа затрат, Статья затрат, Подстатьи затрат). Логичнее всего указать столбец 4, т.к. он наиболее детализирован и конкретнее указывает расшифровку статьи
далее будет предложено указать точность поиска:
- если указать " 1 - по всему тексту " - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне полностью совпадает со значением из столбца А листа "Справочник". Т.е. если бы у нас в таблице реализации был бы записан только номер статьи(1.01), тогда можно было бы указать именно 1
- если указать " 2 - по части ячейки " - данные будут заменены только в том случае, если значение ячейки в выделенном для замены диапазоне содержит любое значение из столбца А листа "Справочник". Это больше подходит к описанному случаю, т.к. нам необходимо заменить исключительно номер статьей на их расшифровку, оставив при этом текст "Статья затрат: "

на листе Справочник нет значений
в качестве столбца для поиска значений и для замены значений на листе Справочник указано одно и то же число
в качестве столбца значений для замены указано число, превышающее общее количество столбцов на листе Справочник

Особое внимание хочу уделить случаю, когда выбирается замена по части ячейки. В этом случае лучше список на листе Справочник отсортировать по длине текста по тому столбцу, в котором будут значения для поиска. Зачем это надо: т.к. значение по части ячейки будет заменять не полное соответствие, то есть вероятность неверных замен. Например, есть текст "Статья затрат: 1.011" . В то же время на листе Справочник есть статьи "1.01" и "1.011" . Т.к. "1.01" идет раньше в большинстве случаев, то текст будет заменен некорректно: "Статья затрат: ТВ 1 " .
Чтобы получить длину строки текста можно использовать функцию ДЛСТР(LEN):
=ДЛСТР( A2 )
=LEN(A2)

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

Смотрите также или строки; замена If myArr(j, i) вам нужно из из txt будуSanja переменную "iKey " Not delRng Is For i =lion9 в скобках ToAbsolute нужные участки, а менять. От этого найти слово иливсе, щелкнув заголовок вкладке слова "год" иПримечание: идет по всему Like iText(x) Then текстового файла загрузить

удалять их вашим: Одну строку упустил (с пробелом) потом Nothing Then Set 0 To UBound(iText): Спасибо огромное! Работает.

- константа из если столбцов >26, зависит формула для фразу и заменить столбца.Поиск "город".

Мы стараемся как можно листу или даже

newArr(k, i) = 1.5 млн строк макросом )Между 27 и

задатьНет. delRng = Union(delRng, Set iRange = Буду думать, как перечисления Excel.XlReferenceType: xlAbsolute

то не только замены! их.Чтобы заменить текст или; на вкладке "Вопросительный знак заменяет один оперативнее обеспечивать вас книги. Как настроить myArr(j, i) k и при загрузке

Может кто то 28. Должно бытьMarat_Mamedov cl) Else Set Range("A:B").Find(What:=iText(i), LookIn:=xlFormulas, Lookat:=xlPart)

третьего контрагента добавить = 1, xlAbsRowRelColumn алфавит.DigitalizerНа вкладке числа, введите знакиЗаменить

любой знак. Например, актуальными справочными материалами поиск и замену = k + их отфильтровать по сталкивался с подобной такСкрытый текст Sub

: Что то он delRng = cl If Not iRange таким путём. = 2, xlRelRowAbsColumnManyasha: Добрый день!Главная для замены в" доступны только если ввести на вашем языке. ТОЛЬКО в выделенном

1 End If одному столбцу по задачей - "умный Макрос2() Dim iText, не работает.

End If flag Is Nothing Thenlion9 = 3, xlRelative:В файле приведеннажмите кнопку полеформулыг?д

Эта страница переведена столбце/строке? Например, в Next Next Next небольшому списку ключей? экспорт данных из iKey, iTemp, i&,Нечего не происходит, = False End Do iRange.Delete Shift:=xlShiftUp

: Спасибо огромное! Работает. = 4.Digitalizer перечень дат сЗаменитьЗаменить на., то будут найдены автоматически, поэтому ее OPEN OFFICE Эта 'для замены 'на

Если так, то txt" что бы flag As Boolean прикрепил файл в If Next If Set iRange = Буду думать, какDigitalizer, я правильно поняла, заданной формулой. От.(или оставьте этоДля поиска данных с слова "гад", "гид" текст может содержать

функция есть. месте', замените D1 вот вам вариант перебрать текстовый файл Dim myRng As

котором использовал код Not delRng Is Range("A:B").Find(What:=iText(i), _ LookIn:=xlFormulas, третьего контрагента добавить: Gustav а как что Вам просто столбца к столбцу

Кроме того, можно поле пустым, чтобы учетом регистра установите и "год". неточности и грамматическиеPelena на A1 Range("D1").Resize(UBound(newArr, на Power Query. по условию ( Range, cl As

от "" но Nothing Then delRng.Delete Lookat:=xlPart) Loop Until таким путём. быть если помимо нужно формулы во меняются только ссылки нажать клавиши CTRL+H. ничем не заменять флажокСовет: ошибки. Для нас: По-моему, во всех 1), UBound(newArr, 2))Разархивируйте 2 файла в моем случаи Range, delRng As

после нажатия выполнить Shift:=xlShiftUp MsgBox "ненужные

iRange Is Nothing=ИЛИ(ЕСЛИ(ЕОШ(НАЙТИ("Турция";E7));"Израиль";"Турция");ЕСЛИ(ЕОШ(НАЙТИ("Египет";E7));"Израиль";"Египет")) приведенных в формуле второй строке привязать на ячейки сВ поле

знаки), а затемУчитывать регистр Звездочки, знак вопроса и важно, чтобы эта версиях НАЙТИ/ЗАМЕНИТЬ работает = newArr MsgBox на С:\test строка начинается и Range Dim dic макрос не чего строки удалены!", 64, End If NextС вложением в

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

) статья была вам по выделенному диапазону, "ненужные строки удалены!",На листе Настройки перечень условий ) As Object iText не произошло (ячейки

"конец" End Sub i MsgBox "ненужные функцию тоже не заданы дополнительные параметры, при протягивании вниз и C1; следующая

введите искомые словоНайти далееДля поиска ячеек, содержащих

можно найти в полезна. Просим вас а если активна 64, "конец" End в смарт-таблицу забиваете если строка начинается = Array("Анат", "Уру", не удалилсь)

Советы

Jack Famous строки удалены!", 64, выходит :( Буду и если применить B1 не превращалось C1 и D1; или фразу.или только символы, введенные данных листа перед уделить пару секунд только одна ячейка, Sub ключи, по которым на указанные условия "Инокен") 'список словОжидалось что удалятся: Sanja For i "конец" End SubДанный благодарен тому, кто макрос то выдает в B2? и т.д.).В полеНайти все

в поле их с тильда и сообщить, помогла то по всемуMarat_Mamedov

Поиск и замена текста

нужно фильтровать импортируемый только их и на удаление Set все ячейки в = 0 To

Найти в поле ли она вам, листу

: Проста супеер ! список. На листе забрать из текстовго

dic = CreateObject("Scripting.Dictionary") диапазоне A:B которые UBound(iText) iTemp = ячейки в диапазоне я ошибаюсь?

Пример: так попробуйте: массово знак '$'введите новый текст.Примечание:, установите флажокНайти с помощью кнопок

Гиперссыльный Спасибо всем огромное! Результат щелкаете правой файла For i = не начинаются на dic(iText(i)) Nextобъясните пожалуйста которые начинаются наMCH

200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ((Base!$A$2:$A$999=Лист1!$A$2)*(Base!$M$2:$M$999>=Лист1!L1)*(Base!$M$2:$M$999 - макрос не1. В ячейке перед буквой столбцаНажимайте кнопку Если поле Ячейка целиком

Массовая замена значений в excel по маске

. Например, чтобы найти внизу страницы. Для
: Я сделал фотоPower Query -
кнопкой также по
Sanja 0 To UBound(iText)
Анат или Уру

- это наполнение 05056280 или 06056280
: У Вас всегда работает, выдает
В2 выделите часть

и после нее,Найти далееЗаменить на. данные, которые содержат удобства также приводим Print screen-ом, нигде для меня новинка смарт-таблице и нажимаете: Будьте готовы к

Массовая замена значений в ячейках (Иное/Other)

? (на английском языке). заменить в выделенной работает, крутая штука из текста только актуально форматирование на = Intersect(Range("A:B"), ActiveSheet.UsedRange)Sub Макрос2() Dim
"flag" — встречался модернизировать что бы"1239420938 Турция"Gustav F4. Должно получиться $B$1 и $C$1

вы хотите изменить..

с определенным форматированием,как критерии поиска.Функции поиска и замены области"! (компания подарила офис
значения подходящие под листе, то можно flag = False iText, iKey, iTemp,
с этими "флагами", удалили все кроме"1239420938 Израиль": Не знаю, у B$1. C C1

описанное вами условие. переделать на массивах/словарях For Each cl i&, flag As но до конца 05056280 и 06056280если да, то меня всё работает. - аналогично и $E$1.Заменить
отменить, нажав клавишуФормат
Параметры для поиска в: я еще вЕсли будет образецMarat_Mamedov In myRng For
Boolean Dim myRng не понял)) и и 01056280 (указать
подойдет формула:
Ввожу Ваши формулы,
2. Протяните формулуВторой вопрос: путем
. Чтобы обновить все ESC.и выберите нужные, чтобы определить дополнительные
книге необходимой информации,
Pelena
2007-2010 завис.) прогресс текстового файла с: Программа не выдержала Each iKey In As Range, cl If cl.Value Like в макросе те

=ПСТР(E7;ПОИСК(" ";E7&" ")+1;99) выделяю ячейки, запускаю
в В2 на функцией "замена" - вхождения, не останавливаясьЧтобы заменить одно или
параметры в диалоговом
условия поиска при например определенного числа: Дык, Excel по
не стоит на парой строк -

, вылет. Подскажите dic.Keys If cl.Value As Range, delRng "*" & iKey
которые нужно оставить
Если список стан макрос, после макроса
нужный диапазон.
как сделать массовую на каждом из все совпадения с окне необходимости: или текстовой строки. умолчанию так работает: месте

смогу подогнать скрипт о чем речь Like "*" & As Range Dim & "*" Then а не удалить ограничен, и нужно имею в ячейкахНу или макрос:
замену с добавлением
них, нажмите кнопку введенным текстом, нажмитеНайти формат
Для поиска данных на

На вкладке если выделен диапазон,Макрос автора - под него. не совсем понимаю. iKey & "*" dic As Object - это, чтобы
т.к. оставить нужно
найти название страны, абсолютные формулы:200?'200px':''+(this.scrollHeight+5)+'px');">Sub replaceRef() знака '$'? Т.е.Заменить все кнопку. листе или во

Использование поиска по маске в функции ЕСЛИ

Главная ищет по нему, отработал программа неНу и эта,Jack Famous

можно было переменную

порядка 10 масок находящееся в любом200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ((Base!$A$2:$A$999=Лист1!$A$2)*(Base!$M$2:$M$999>=Лист1!$L$1)*(Base!$M$2:$M$999For i = скажем задаем такой

ЗаменитьСовет: всей книге выберитев группе если не выделен, вылетела. если даже по: тупанул малях))) точно True Exit For "Уру*", "Инокен*") 'список

"iKey " (с а удалить намного

месте текстовой страны,

Код200?'200px':''+(this.scrollHeight+5)+'px');">=СУММПРОИЗВ((Base!$M$2:$M$999>=Лист1!$B$1)*(Base!$M$2:$M$999 Может, в 65 To 90 параметрСовет:или

Чтобы найти ячейки, точно в полеРедактирование то ищет поВ excel в ячейках

форуме по-копаться, уверен,

- это ж End If If слов на удаление пробелом) потом задать больше и количество то можно использовать

обрабатываемых этими формуламиSelection.Replace Chr(i) &% Чтобы найти только вхождения
Заменить все
соответствующие определенному формату,

Искатьнажмите кнопку
всему листу

столбца указаны промежутки что можно найти как flag Then Exit Set dic = - в случае масок вырастает)
формулу:

ячейках какие-то проблемы, 1, "$" &

1 и заменяем на в верхнем или. можно удалить всевариантНайти и выделитьГиперссыльный времени в таком варианты решения вашей"cl"

For Next If CreateObject("Scripting.Dictionary") For i
необходимости?

Удаление ячеек по маске

какие-нибудь "левые" значения? Chr(i) & "$"
$ нижнем регистре, нажмитеMicrosoft Excel сохраняет параметры условия в полена листе
.: Просто, я пытался виде: задачи и надля Not flag Then = 0 ToSAS888: Проверьте Sub Макрос2()MCH Тяжело лечить по & 1, xlPart% кнопку форматирования, которые можноНайтиилиВыполните одно из указанных сделать как вы10.00-12.00 VBA и на"For each cl in If Not delRng UBound(iText) iTemp =: ---------------------------------------------------- Dim iText, iKey,: очепятка: ". текстовой строки" переписке, не видяNext i$1. соответственно ко всемБольше определить. Если вы, а затем выбратьв книге ниже действий. говорите: выделил столбец,11.00-14.00 SQL. Только как rng" Is Nothing Then

Найти заменить, а он формат на следующий: решения, который вам: Можно забирать все Set delRng = False For Each
словаря уникальными ключами, cl As Range, ничего не говорит: Есть таблица соиЭта процедура необходимаСовет. удается найти символы, рядом с кнопкой выберите в поле. падла заменяет вос 10 до 12 кажется правильным, а

данные в память, cl End If

cl In myRng с пустыми значениями,
delRng As Range - никогда ничего списком, содержащим вищет только
для автоматизации иВидео не на которые вы знаетеФорматПросматриватьЧтобы найти и заменить всем листе, игнорируяс 11 до 14 конкретно решение вашей обрабатывать (удалять/добавлять/изменять) их
flag = False For Each iKey без генерации ошибки Dim dic As подобного не видел ячейках номера контейнеровгде вместо знака % ухода от ручного вашем языке? Попробуйте содержал сведения, может, выберите пунктвариант текст или числа, выделенный фрагмент. ЧувствуюПробую сделать это изначальной задачи.

в памяти и End If flag In dic.Keys If
В принципе, в Object iText = - полез по и страну, вида: - буквы от проставления знака '$' выбрать потребоваться снимите нужные
Выбрать формат из ячейкипо строкам выберите пункт себя полным идиотом через поиск иSanja
ЗАМЕНЯТЬ новыми данными
= False Next cl.Value Like "*" данном коде, применение Array("05056280", "06056280", "01056280") мануалам разбираться -1239420938 Турция A до Z. к каждой буквеСкрытые субтитры параметры форматирования из, а затем щелкнитеилиЗаменить ((( замену. Старый формат: Sub Макрос2() Dim старые на листе, If Not delRng & iKey & словаря просто дань 'список слов на как же онаилиDigitalizer столбца.. предыдущего поиска. В ячейку с форматированием,по столбцам.Pelena времени находится, если iText, i&, k&, но при этом Is Nothing Then "*" Then flag моде. Т.к. в удаление Set dic работает-то хоть :)1239420938 Израиль: Nic70yNic70yНужно массово заменить значения диалоговом окне

которое требуется найти..
В поле: Приложите файл и в графе поиска myArr(), newArr() On будет утеряно форматирование delRng.Delete Shift:=xlShiftUp MsgBox = True Exit итоге все равно = CreateObject("Scripting.Dictionary") ForMCHХотелось бы, чтобыда вот и: Ctrl+h не пробовали? в ячейках .Поиск и заменаВыполните одно из указанныхДля поиска данных сНайти поясните что на ввести *.*-*.* (*, Error Resume Next ячеек (цвета шрифтов, "ненужные строки удалены!", For End If перебираем ключи, а i = 0: ЕЧИСЛО - лишнее: в следующем столбце приходилось F4 жатьDigitalizerПример, в ячейкахперейдите на вкладку ниже действий. конкретными свойствами выберитевведите текст или что Вы хотите как я помню, iText = Array("Анат*", курсив и прочее) 64, "конец" End If flag Then их не так To UBound(iText) iTemp=ПРОСМОТР(2;1/ПОИСК(;E8);) по каждой такой по 50 раз: так я и значение :

ПоискЧтобы найти текст или в поле числа, которые нужно поменять в маске - "Уру*", "Инокен*") 'списокMarat_Mamedov Sub Exit For Next уж много, и = dic(iText(i)) Next
Владимир ячейке выводилась соответствующая :) спрашиваю каким образом1805/7957-6890и нажмите кнопку
числа, нажмите кнопкуОбласть поиска искать, или щелкнитеSerge_007 это любой знак слов на НЕудаление: "но при этомMarat_Mamedov If Not flag можно обойтись обычным Set myRng =: =ЗАМЕНИТЬ(A1;1;НАЙТИ(" ";A1);"") страна. Конструкция вида:Manyasha это можно сделать

Нужно заменить наПараметрыНайти всевариант стрелку в поле: Такой "опции" не

или любые несколько myArr = Intersect(Range("A:B"), будет утеряно форматирование: Просто супер! Спасибо Then If Not

массивом. Intersect(Range("A:B"), ActiveSheet.UsedRange) flagMarat_Mamedov=ЕСЛИ(E7="Турция*";"Турция";"Израиль")да да да, в "замене". :, чтобы открыть параметры
или

работать не хочет. об этом икак сделать маску1805-7957/6890 форматирования. Щелкните стрелкуНайти далее,и нажмите кнопку за ненадобностьюКто подскажет, что

To UBound(myArr, 1), курсив и прочее)" на боевых реестрах

Then Set delRng И что такое Each cl InНа форме нашел Как можно решить речь :) на те значенияAbram pupkin рядом с полем.
значения последнего поиска вЦитата
мне вписать в 1 To UBound(myArr,
- такой вариант удалять записи там = Union(delRng, cl) "flag"Если посмотрите выше myRng For Each макрос который удаляет эту задачу? Испасибо за макрос которые не надо: так ?ФорматСовет:
или списке.Pelena, 26.07.2015 в графе "Заменить на"? 2)) k =
подходит там вообще записей под 1,5 Else Set delRng по коду, то iKey In dic.Keys ячейки со сдвигом каким будет решение, ) помогло менять.=ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬи нажмите кнопку При нажатии кнопкипримечанияВ условиях поиска можно

, просто текст. колонки что бы False End If
Boolean. В коде & "*" ThenSub Макрос2() Dim трое? Вообще, поддерживают понял задачу, требовалось,1 в Лист1!$Полосатый жираф аликДля поиска текста или которые вы ищете
(?) и звездочку по выделенному диапазону,или between or

Помогите составить маску для замены в excel

UBound(myArr, 2) kJack Famous поместились ) посмотрю Next If Not
она служит индикатором

iRange As Range

ли функции Excel вероятно, нечто следующее:

чисел на листе указываются и щелкнувФормулы (*). а если активнаГиперссыльный = 1 For: эт не я как себя поведет delRng Is Nothing выполнения условия If

Exit For End Dim iText As поиск по маске

200?'200px':''+(this.scrollHeight+5)+'px');">Sub io()$1 тупых примеров, а
также можно использовать

Как настроить НАЙТИ и ЗАМЕНИТЬ только в выделенной области? (Формулы/Formulas)

нужное вхождение в,Звездочка используется для поиска только одна ячейка,: В Excel 2010 j = 1 писал)) Excel. Then delRng.Delete Shift:=xlShiftUp cl.Value Like "*" If If flag Variant Dim i вообще?Selection.Formula = Application.ConvertFormula(Selection.Formula,

Nic70y описать, что действительно функции ПОИСК и списке сделает ячейкизначения любой строки знаков. то по всему почему-то отсутствует функция

To UBound(myArr, 1)PooHkrdНе смог найти MsgBox "ненужные строки & iKey & Then Exit For

As Long iTextmouse

xlA1, xlA1, xlAbsolute): Этим средством думаю нужно? И по НАЙТИ. active. Можно сортироватьи Например, если ввести листуВсе верно, так

НАЙТИ И ЗАМЕНИТЬ For x =: Я правильно понимаю, как настроить умный удалены!", 64, "конец" "*"ЦитатаJack Famous написал: Next If Not = Array("05056280*","06056280*","01056280*") 'список: может такEnd Sub ни как, перебирайте каким адресам расположено

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

Синтаксис

Замените(выражение, найти, заменить ,[ начало , [ считать , [ сравнить ]])

Синтаксис функции Replace содержит следующие именованные аргументы:

Part	Описание
выражение	Обязательная часть. Строковое выражение, содержащее заменяемую подстроку.
поиск	Обязательный. Искомая подстрока.
замена	Обязательный. Подстрока замены.
начало	Необязательно. Начните позицию для подстройки выражения, которая будет искаться и возвращаться. Если элемент опущен, предполагается, что он равен 1.
count	Необязательное. Число выполняемых замен подстроки. Если значение опущено, значение по умолчанию — -1, что означает, что вы можете сделать все возможные замены.
compare	Необязательно. Представляет собой числовое значение, указывающее вид сравнения, которое выполняется при оценке подстрок. Значения см. в разделе "Параметры".

Параметры

Аргумент compare может принимать следующие значения:

Константа	Значение	Описание
vbUseCompareOption	–1	Выполняет сравнение, используя параметр оператора Option Compare.
vbBinaryCompare	0	Выполняется двоичное сравнение.
vbTextCompare	1	Выполняется текстовое сравнение.
vbDatabaseCompare	2	Только Microsoft Access. Выполняется сравнение на основе сведений из базы данных.

Возвращаемые значения

Функция Replace возвращает следующие значения:

Если	Функция "Replace" возвращает
Элемент expression имеет нулевую длину	Пустая строка ("")
Элемент expression равен Null	Ошибка.
Элемент find имеет нулевую длину	Копия expression.
Элемент replace имеет нулевую длину	Копирование выражений со всеми вхождениями удаленной находки.
начните > Len(выражение)	Строка нулевой длины. Замена строк начинается в позиции, указанной в начале.
Элемент count равен 0	Копия expression.

Примечания

Возвратное значение функции Replace — это строка с заменами, которая начинается с позиции, указанной в начале, и завершается в конце строки выражения. Это не копия исходной строки с начала до конца.

См. также

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

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

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

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

1. Создайте условия, которые вы хотите использовать, которые содержат исходные значения и новые значения. Смотрите скриншот:

2. Затем удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.

3. Нажмите Вставить > Модульи вставьте следующий код в окно модуля.

Код VBA: найти и заменить сразу несколько значений

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

5. Нажмите OK, и отображается другое окно подсказки, напоминающее вам о выборе критериев, созданных на шаге 1. См. снимок экрана:

6. Затем нажмите OK, все конкретные значения были заменены новыми значениями по мере необходимости.

Статьи по теме:

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment. The comment will be refreshed after 00:00 . Is this method can be use if between sentence in a cell? e.g I want to change numbers between the line : abcdefghi 12345 jklmnopqrstu >>> abcdefghi 67891 jklmnopqrstu if no is there any other method can be use? Please help. Thank you! The code in this article can finish your problem, please try, thank you!

How would the code look like if you would check by line?

First line: Original value "Apple" (A1) and replacing value "Green Apple" (B1) and in the same line is
the original range to be replaced by B2 e.g., apple, orange, banana, nuts (D1, E1, F1. )

Second line: Original value "Orange" (A2) and replacing value "Green Orange" (B2) and
in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2. )

Thank you so much!

Second line: Original value "Orange" (A2) and replacing value "Green Orange" (B2) and in the same line is the next original range to be replaced by B2 e.g., apple, orange, banana, nuts (D2, E2, F2. )

Thank you so much!

I tried copying this code into Excel so I can have it saved, but then if I copy from Excel into VBA, it doesn't work. Does anyone know why? As far as I can tell, the code is exactly the same.
My question didn't show completely. I am using Excel in Office 365, when I hit OK, nothing happens. Can anyone help? Sub MultiFindNReplace()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub
The values I need to replace are simple Country names, which need to be replaced by these:
Replacing Value
AD - Andorra
AE - United Arab Emirates
AF - Afghanistan
AG - Antigua and Barbuda
AI - Anguilla
AL - Albania
AM - Armenia
AO - Angola
AQ - Antarctica
AR - Argentina
AT - Austria
AU - Australia
AW - Aruba
AX - Aland Islands
AZ - Azerbaijan
BA - Bosnia and Herzegovina
BB - Barbados
BD - Bangladesh
BE - Belgium
BF - Burkina Faso
BG - Bulgaria
BH - Bahrain
BI - Burundi
BJ - Benin
BL - Saint Barthélemy
BM - Bermuda
BN - Brunei Darussalam
BO - Bolivia, Plurinational State of
BQ - Bonaire, Sint Eustatius and Saba
BR - Brazil
BS - Bahamas
BT - Bhutan
BV - Bouvet Island
BW - Botswana
BY - Belarus
BZ - Belize
CA - Canada
CC - Cocos (Keeling) Islands
CD - Congo, the Democratic Republic of the
CF - Central African Republic
CG - Congo
CH - Switzerland
CI - Cote d'Ivoire
CK - Cook Islands
CL - Chile
CM - Cameroon
CN - China
CO - Colombia
CR - Costa Rica
CU - Cuba
CV - Cape Verde
CW - Curaçao
CX - Christmas Island
CY - Cyprus
CZ - Czech Republic
DE - Germany
DJ - Djibouti
DK - Denmark
DM - Dominica
DO - Dominican Republic
DZ - Algeria
EC - Ecuador
EE - Estonia
EG - Egypt
EH - Western Sahara
ER - Eritrea
ES - Spain
ET - Ethiopia
FI - Finland
FJ - Fiji
FK - Falkland Islands (Malvinas)
FO - Faroe Islands
FR - France
GA - Gabon
GB - United Kingdom
GD - Grenada
GE - Georgia
GF - French Guiana
GG - Guernsey
GH - Ghana
GI - Gibraltar
GL - Greenland
GM - Gambia
GN - Guinea
GP - Guadeloupe
GQ - Equatorial Guinea
GR - Greece
GS - South Georgia and the South Sandwich Islands
GT - Guatemala
GW - Guinea-Bissau
GY - Guyana
HM - Heard Island and McDonald Islands
HN - Honduras
HR - Croatia
HT - Haiti
HU - Hungary
ID - Indonesia
IE - Ireland
IL - Israel
IM - Isle of Man
IN - India
IO - British Indian Ocean Territory
IQ - Iraq
IR - Iran, Islamic Republic of
IS - Iceland
IT - Italy
JE - Jersey
JM - Jamaica
JO - Jordan
JP - Japan
KE - Kenya
KG - Kyrgyzstan
KH - Cambodia
KI - Kiribati
KM - Comoros
KN - Saint Kitts and Nevis
KP - Korea, Democratic People's Republic of
KR - Korea, Republic of
KW - Kuwait
KY - Cayman Islands
KZ - Kazakhstan
LA - Lao People's Democratic Republic
LB - Lebanon
LC - Saint Lucia
LI - Liechtenstein
LK - Sri Lanka
LR - Liberia
LS - Lesotho
LT - Lithuania
LU - Luxembourg
LV - Latvia
LY - Libyan Arab Jamahiriya
MA - Morocco
MC - Monaco
MD - Moldova, Republic of
ME - Montenegro
MF - Saint Martin (French part)
MG - Madagascar
MK - Macedonia, the former Yugoslav Republic of
ML - Mali
MM - Myanmar
MN - Mongolia
MO - Macao
MQ - Martinique
MR - Mauritania
MS - Montserrat
MT - Malta
MU - Mauritius
MV - Maldives
MW - Malawi
MX - Mexico
MY - Malaysia
MZ - Mozambique
NA - Namibia
NC - New Caledonia
NE - Niger
NF - Norfolk Island
NG - Nigeria
NI - Nicaragua
NL - Netherlands
NO - Norway
NP - Nepal
NR - Nauru
NU - Niue
NZ - New Zealand
OM - Oman
PA - Panama
PE - Peru
PF - French Polynesia
PG - Papua New Guinea
PH - Philippines
PK - Pakistan
PL - Poland
PM - Saint Pierre and Miquelon
PN - Pitcairn
PS - Palestinian Territory, Occupied
PT - Portugal
PY - Paraguay
QA - Qatar
RE - Reunion
RO - Romania
RS - Serbia
RU - Russian Federation
RW - Rwanda
SA - Saudi Arabia
SB - Solomon Islands
SC - Seychelles
SD - Sudan
SE - Sweden
SG - Singapore
SH - Saint Helena, Ascension and Tristan da Cunha
SI - Slovenia
SJ - Svalbard and Jan Mayen
SK - Slovakia
SL - Sierra Leone
SM - San Marino
SN - Senegal
SO - Somalia
SR - Suriname
SS - South Sudan
ST - Sao Tome and Principe
SV - El Salvador
SX - Sint Maarten (Dutch part)
SY - Syrian Arab Republic
SZ - Swaziland
TC - Turks and Caicos Islands
TD - Chad
TF - French Southern Territories
TG - Togo
TH - Thailand
TJ - Tajikistan
TK - Tokelau
TL - Timor-Leste
TM - Turkmenistan
TN - Tunisia
TO - Tonga
TR - Turkey
TT - Trinidad and Tobago
TV - Tuvalu
TW - Chinese Taipei
TZ - Tanzania, United Republic of
UA - Ukraine
UG - Uganda
US - United States
UY - Uruguay
UZ - Uzbekistan
VA - Holy See (Vatican City State)
VC - Saint Vincent and the Grenadines
VE - Venezuela, Bolivarian Republic of
VG - Virgin Islands, British
VN - Viet Nam
VU - Vanuatu
WF - Wallis and Futuna
WS - Samoa
YE - Yemen
YT - Mayotte
ZA - South Africa
ZM - Zambia
ZW - Zimbabwe
This is pretty cool. Is there a way that we could change it to work across the whole Workbook instead of a specific range? Running into an issue when an Original Value is a subset of another, is there a way to look for Exact Match or another workaround? See attached image for example, result cell highlighted red is the issue. In case the image doesn't work, here's a typed out modified version: Original Value1: Call Option Risk | Replacing Value1: ^b^Call Option Risk^/b^
Original Value2: Index Call Option Risk | Replacing Value2: ^b^Index Call Option Risk^/b^ [as you can see, OV2 contains OV1 but is not an exact match]
Result of MultiFindNReplace() on OV2: Index ^b^Call Option Risk^/b^ [but I'm expecting ^b^Index Call Option Risk^/b^]

Hello, sameer,
May be the following code can solve your problem, please try it:

Sub MultiFindNReplace()
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, LookAt:=xlWhole, MatchCase:=True
Next
Application.ScreenUpdating = True
End Sub

Hope it can help you!

skyyang - Thanks. This version does not work in my case since it appears to 'match entire cell contents' but I need to replace only part of the string. I realize now that the example I typed out does not communicate this detail; can you see the JPG I attached instead? Original Range:
Product1 = Call Option: risk 1
Product2 = Index Call Option: risk 2

It's the extra text after the colon that breaks your version. And OP's code is able to ignore the extra text, but can't differentiate between "Call Option Risk:" and "Index Call Option Risk:". Again, I think the picture explains this best, if you could please take another look.

How skip cell after first match?

I've sorted translations from longest phrase to shortest.

Hi, ksec,
Could you give more detailed information about your problem? Or you can insert a screenshot here to show your problem.
Thank you!

He means that if a part of the original repeats later there is an issue because the function replace again, sorted or not, if the original word is not unique (even a part of it) this function does not work

Hello, mar_m,
Please apply the below code, try if it can solve your problem, Thank you!

Hi!
i need this macro to work on specific columns without selecting different columns each time manually.
is there any way to mention desired columns and replacing table in the code?
appreciated what should i change to make the replacing range FIXED, i dont want to re-enter every time? Thank YOU So much , it works like a magic . saves my time.. thanks a ton :) Hello. Thank you for the help. But it is not renaming the entire cell if it is a large string. For any cell which has more than 21 letters, it can only replace 21 letters and then else is same. Please help. Hi im TG,Thank you for posting this page, It's very useful and make it very simple of my work and save more time , thank you sir. Макрос не работает корректно . Пытался подобным образом заменить символы, но не различаются маленькие и большие буквы. Hi, I would like to replace whole cells in the entire Excel file (with many sheets). What should be replace in the Original Range to do that? Thanks. What should be replaced in the code to run it on all the Excel file's sheets? Can I ask if I want to replace the entire cell based on partial match, how should I alter the code?
For example: I want to code all cells containing the word apple to 1 (regardless whether it's "green apple" or "red apple"), I want all of them to turn into 1.

El mejor. Mil gracias por compartir estos conocimientos, fue increíble la ayuda que me proporciono este programa en VBasic (nunca lo había usado hasta ahora), lo pensaba realizar en php o similar pero leyendo encontre esta valiosa información.

The VBA method of find and replace is not working. It was working for me till yesterday but today I am not able to do any find and replace using it today. Can you guys please help Hi, Abhishek,
After inserting the code into the workbook, you should save the workbook as Excel Macro-Enabled Workbook format to keep the code without losing it.
Please try it, hope this can help you! This is causing my original values data to also be replaced, in addition to the column of data I want to be replaced. How do I stop this?

I have to replace list of bank with proper name based on key words. For example, if somebody enters Halifax, it should be Halifax Bank of Scotland (HBOS) or if somebody enters LloydsTSB, it should be Lloyds.

Original value Replacing Value
LloydsTSB : Lloyds
Santander : Santander (Abbey)
Argos Card Services : Argos
Halifax : Halifax Bank of Scotland (HBOS)

The down formula works in many cases. But if there is two Halifax bank on my list, I will get Halifax Bank of Scotland (HBOS) (HBOS)
. So, first time it finds Halifax, replace with correct one. Again it finds Halifax and replace. How can I correct this problem ?

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