Vba excel замена текста

Обновлено: 02.07.2024

Если постоянно приходится выполнять одно и то же действие или появляется потребность автоматизировать некоторые движения, то в Excel есть специальная функция – макросы. Это маленькие (или не очень) скрипты, позволяющие выполнять ряд действий за пользователя. Если правильно использовать макросы, то можно выполнять почти любое действие, даже то, которое не предусмотрено стандартным функционалом.

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

Программирование макросов осуществляется с помощью языка VBA. И в нем есть одна из часто используемых функций – Replace.

Определение функции Replace, описание

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

С помощью этой функции можно осуществить автоматически сразу несколько действий:

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

Если необходимо осуществить замену внутри определенного диапазона, то нужно использовать функцию Replace применительно к каждой ячейке. Но если использовать объект Range, то можно значительно упростить процесс.

Если выражаться более просто, эта функция является заменой окна «Найти и заменить» . Она позволяет автоматизировать этот процесс, например, основываясь на результатах вычислений или действиях, совершенных ранее. Например, можно запрограммировать макрос на то, чтобы он автоматически подгружал оценки учеников, и если новая оценка выше предыдущей, то вместо текста «учится плохо» писать строку «учится лучше». Таким образом, можно значительно превысить те возможности. которые используются в стандартной функции «Условное форматирование», поскольку такое сравнение реализовать стандартными средствами Эксель не так удобно, как макросами (хотя если заморочиться, то можно). Но есть и более сложные задачи, реализовать которые можно исключительно с помощью макросов.

Функцию «Найти и заменить» знают почти все пользователи Эксель. Она позволяет осуществить замену искомой строки один и более раз. Перед тем, как мы начнем рассматривать, как автоматизировать процесс, необходимо сперва разобраться в стандартном алгоритме действий пользователя, если он хочет заменить один текст на другой. Все потому, что нельзя рассматривать более продвинутые инструменты, не разобравшись толком в простых.

Итак, чтобы заменить определенный текст на какую-то строку без макроса, необходимо на вкладке «Главная» найти пункт «Редактирование». Там в группе «Редактирование» нужно найти такую кнопку. После этого откроется небольшое меню, в котором нужно будет выбрать пункт «Заменить».

Можно же значительно упростить себе жизнь, один раз запомнив комбинацию клавиш Ctrl + H.

После этого появится такое окно.

В нем мы видим две строки. В первой нужно указывать ту строку, которую программа должна найти. Во второй записывается тот текст, на который должна описанная выше строка замениться первая строка. Окно предусматривает также возможность настроить формат, если нажать на кнопку «Параметры».

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

Приятный бонус: с помощью комбинации клавиш Ctrl + Enter можно сделать искусственный разрыв строк, чтобы написать несколько строк в рамках одной ячейки.

При чем это к нашей теме? А отношение самое прямое. Как правило, чтобы удалить эти разрывы, пользователь вручную убирает соответствующие непечатаемые символы. Но это можно сделать проще, с помощью окна «Найти/Заменить». После этого в поле «Найти» указывается символ разрыва строки. Его можно ввести нажатием комбинации клавиши Ctrl + J. В соответствующем месте появится точка, которая обозначает этот непечатный символ.

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

Видим, что после того, как мы нажали «ОК», три строки были слиты в одну, и при этом после каждого слова стоит пробел.

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

Также можно настроить отображение значений с учетом регистра (то есть, больших и маленьких букв), а также поставить флажок возле пункта «Ячейка целиком». Что касается формата, то он будет отображаться в специальном поле возле кнопки настройки.

Синтаксис функции Replace и параметры

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

Replace(expression, find, replace, [start], [count], [compare])

Давайте разберем аргументы этой функции более детально:

Expression. Это изначальная строка, замена которой требуется.
Find – это подстрока, входящая в состав строки верхнего уровня, в которой нужно осуществлять замену значения.
Replace – это подстрока, которая заменяет ту подстроку, которая описана в предыдущем аргументе. Проще говоря, тот текст, на который требуется заменить.
Start. Этот параметр необязательный. Характеризует порядковый номер символа строки, с которого макрос будет осуществлять поиск. Та часть строки, которая располагается до этого номера, просто не учитывается при замене.
Count. Это количество итераций, в ходе которых будет осуществляться замена. Этот параметр также является необязательным.
Compare. Это значение в числовом формате, которое используется для указания вида сравнения. Данный параметр также указывать необязательно.

Если опустить все необязательные аргументы, то синтаксис функции Replace будет выглядеть менее устрашающе.

Replace(expression, find, replace)

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

Параметр compare

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

-1. В этом случае в качестве аргумента используется то значение, которое определяется оператором Option Compare.
0. Это двоичное сравнение.
1 – текстовое сравнение.

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

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

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

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

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

Если в качестве аргумента Find вводится строка, длина которой нулевая, то функция вернет то же значение, которое введено в качестве параметра Expression. Технически это означает, что нет особой подстроки, которую нужно искать в рамках строки, которая передается аргументу Expression.
Если длина параметра Replace нулевая, то тогда возвращается строка, соответствующая первому параметру с удаленной частью второго параметра. Технически это означает, что строка текста была заменена на пустое значение.
Если в качестве параметра Start было передано число, превышающее длину искомой строки, то вернется пустая строка. Если же этому аргументу передать число 0, то поиск будет осуществляться после нулевого символа, то есть сразу. Поэтому возвращаться будет то значение, которое указано в качестве параметра Expression.

На эти моменты нужно обратить особое внимание и запомнить их перед тем, как использовать функцию Replace. Это здорово поможет в отладке написанных макросов, если знать эти особенности.

Пример №1

В этом примере имеется единственное вхождение искомой подстроки. И его нужно заменить.

Sub Primer1()

Dim a

MsgBox a

End Sub

Пример №2

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

Sub Primer2()

Dim a

MsgBox a

End Sub

Пример №3

В этом случае осуществляется замена одной подстроки, на поиск начинается только с пятнадцатого символа.

Sub Primer3()

Dim a

MsgBox a

End Sub

Выводы

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

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

Существует еще огромное количество функций, которые позволяют автоматизировать работу с текстом в Excel. Причем есть такие причудливые, например, как функция Space(), которая дает возможность получить строку, основываясь на количестве пробелов. Каждая функция может сочетаться с другими, что дает почти неограниченный простор возможностей.

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

Метод имеет некоторые особенности, которые заключаются в следующем:

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

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

Синтаксис

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

variable = expression.Replace(What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat])

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

expression.Replace What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat]

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. В VBA мы можем создать макрос, который можно использовать для поиска и замены всего, что мы обычно делаем в Excel, путем нажатия сочетаний клавиш Ctrl + H. С помощью VBA Find and Replace мы можем автоматизировать поиск любого слова и заменить его другим замещением. Это помогает, когда нам нужно выполнить одно и то же действие несколько раз.

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

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

Вы можете скачать этот шаблон VBA для поиска и замены Excel здесь - VBA для поиска и замены шаблона Excel

VBA Find and Replace - пример № 1

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

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

Шаг 1: Перейдите в меню « Вставка» в VBA и выберите « Модуль», как показано ниже.

Шаг 2: Как только мы это сделаем, мы откроем новый модуль. В этом модуле напишите подкатегорию от имени VBA Find and Replace, или вы можете выбрать любое имя этой подкатегории.

Код:

Шаг 3: Поскольку у нас есть данные в столбце B от ячейки B2 до B10, мы сначала выберем этот диапазон из B2: B10.

Код:

Шаг 4: Теперь, чтобы заменить что-то, нам нужно искать это слово. Здесь также мы сначала выберем функцию Replace, чтобы посмотреть, что нам нужно заменить.

Код:

Согласно синтаксису функции Replace, мы найдем то, что нам нужно заменить.

Шаг 5: Давайте выберем имя из списка « Бен » как то, что нам нужно заменить.

Код:

Шаг 6: Теперь выберите слово для замены, которым мы должны заменить выбранное слово. Здесь мы решили заменить имя Бен на Сэма, который упоминается как Замена, как показано ниже.

Код:

Шаг 7: Теперь скомпилируйте код и запустите его, нажав на кнопку Play или нажав клавишу F5, как показано ниже.

Мы увидим, что все ячейки, содержащие имя Бена, теперь заменены именем Сэм . И это также выделено желтым цветом.

VBA Find and Replace - пример № 2

Мы все, возможно, столкнулись с ситуацией, когда мы пытались найти и заменить некоторые слова буквами с учетом регистра. Предположим, в списке, если мы хотим заменить только то слово, которое имеет несколько строчных или прописных букв. И если мы используем процесс, который мы видели в примере 1, то мы в конечном итоге заменим все подобные слова в этом списке вместе с требуемым. Этот процесс не может быть выполнен с помощью функции поиска и замены (Ctrl + H) в Excel. Для этого у нас есть положение в VBA.

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

Выполните следующие шаги, чтобы найти и заменить слово BEN.

Шаг 1: Запустите подкатегорию VBA Find and Replace там, как показано ниже.

Код:

Шаг 2: Выберите список как Диапазон от B2 до B10.

Код:

Шаг 3: Теперь выберите точное слово, которое мы хотим заменить. Здесь мы выбрали BEN, который находится в ячейке B2.

Код:

Шаг 4: Опять же, мы выбрали то же слово, которое мы видели в примере 1, в качестве замены BEN, чем Сэм .

Код:

Шаг 5: Теперь для замены точного слова BEN нам нужно использовать MatchCase, если оно становится TRUE, как показано ниже.

Код:

MatchCase поможет нам определить то слово, которое мы хотим заменить точным соответствием. Здесь слово, которое мы выбрали, это BEN в верхнем регистре, а в списке есть и другие подобные слова, но в правильном.

Шаг 6: Теперь запустите код, нажав на кнопку Play или нажав клавишу F5.

Мы увидим, что слово BEN, которое было в ячейке B2, теперь заменено словом « Sam », выделенным желтым цветом. И подобные слова Бен, которые расположены в ячейках B5 и B8 соответственно, все еще остаются неизменными.

Вот как работает точное совпадение.

Шаг 7: Теперь мы удалим добавленный MatchCase из приведенного выше кода и посмотрим, как это будет работать.

Код:

Шаг 8: Снова скомпилируйте и запустите код.

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

Плюсы Excel VBA Найти и заменить

Основное обучение заключается в том, что мы можем заменить регистрозависимые слова и содержимое ячеек с помощью VBA Find and Replace, как показано в примере-2.
Если действие повторяется вручную и несколько раз, то автоматизация будет преимуществом в экономии времени и усилий.
Даже если у нас есть огромный набор данных, в котором мы хотим заменить определенные слова, это можно сделать, не нарушая Excel в громоздких файлах.

То, что нужно запомнить

Мы можем создать макрос функции Find and Replace с помощью опции Record Macro на вкладке Developer. Это самый простой способ создать макрос, если вы новичок в кодировании.
Сохранение файла в формате Macro Enable Excel позволит в будущем использовать код.
Мы можем заменить любой вид текста, слова, числа или символа VBA Find and Replace.

Решение

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

Замена текста по условию
Добрый день. Есть задача удалить все ссылки с файла excel Удаляю заменой. Подскажите, как можно.

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

Excel Vba - замена текста по условию
Задача: в строке, где встречается слово "ПЕНИЕ" заменить пустые ячейки на другой текст, например.

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

Заполнение ячеек по определенному условию
Добрый вечер! Помогите пожалуйста. Не могу решить задачу. Необходимо заполнить ячейки по.

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

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

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

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

3. Щелчок Вставить > модуль, и вставьте следующий код в окно модуля.

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

Set InputRng = Application.InputBox( "Original Range " , xTitleId, InputRng.Address, Type:=8)

Set ReplaceRng = Application.InputBox( "Replace Range :" , xTitleId, Type:=8)

InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value

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

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