Поиск и замена в Excel с помощью регулярных выражений

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

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

Вот о чем мы будем говорить в этой статье:

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

Функция замены при помощи регулярных выражений Excel VBA

Как известно, встроенные функции Excel не поддерживают регулярные выражения. Чтобы иметь возможность использовать регулярные выражения в своих формулах, вам нужно создать собственную функцию. К счастью, объект RegExp уже существует в VBA, и мы используем этот объект в приведенном ниже коде:

Public Function RegExpReplace(text As String, pattern As String, text_replace As String, Optional instance_num As Integer = 0, Optional match_case As Boolean = True) As String
    Dim text_result, text_find As String
    Dim matches_index, pos_start As Integer
 
    On Error GoTo ErrHandle
    text_result = text
    Set regex = CreateObject("VBScript.RegExp")
 
    regex.pattern = pattern
    regex.Global = True
    regex.MultiLine = True
 
    If True = match_case Then
        regex.ignorecase = False
    Else
        regex.ignorecase = True
    End If
 
    Set matches = regex.Execute(text)
 
    If 0 < matches.Count Then
        If (0 = instance_num) Then
            text_result = regex.Replace(text, text_replace)
        Else
            If instance_num <= matches.Count Then
                pos_start = 1
                For matches_index = 0 To instance_num - 2
                    pos_start = InStr(pos_start, text, matches.item(matches_index), vbBinaryCompare) + Len(matches.item(matches_index))
                Next matches_index
 
                text_find = matches.item(instance_num - 1)
                text_result = Left(text, pos_start - 1) & Replace(text, text_find, text_replace, pos_start, 1, vbBinaryCompare)
            End If
        End If
    End If
 
    RegExpReplace = text_result
    Exit Function
 
ErrHandle:
    RegExpReplace = CVErr(xlErrValue)
End Function

Добавьте этот код VBA в вашу рабочую книгу.

 Советы:

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

Синтаксис RegExpReplace

Функция RegExpReplace ищет во входной строке значения, соответствующие регулярному выражению, и заменяет найденные совпадения указанным вами текстом.

Функция принимает 5 аргументов, но обязательными являются только первые три.

RegExpReplace(текст; шаблон; text_replace; [instance_num]; [match_case])

Где:

• Текст (обязательно) — текстовая строка для поиска.
• Шаблон (обязательно) — регулярное выражение для сопоставления.
• Text_replace (обязательно) — текст для замены найденных совпадающих подстрок.
• Instance_num (необязательно) — порядковый номер, указывающий, какой из найденных экземпляров заменить. Если его не указать, функция заменит все найденные совпадения (по умолчанию).
• Match_case (необязательный) — определяет, следует ли учитывать или же игнорировать регистр букв. Если значение ИСТИНА или опущено (по умолчанию), при поиске учитывается регистр; если ЛОЖЬ – то поиск совпадений происходит без учета регистра.

Функция работает во всех версиях Excel от 365 до 2010.

Полезные советы по использованию

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

1. По умолчанию функция работает в режиме Заменить все. Чтобы заменить конкретное вхождение, поместите соответствующий номер в аргумент instance_num.
2. По умолчанию функция чувствительна к регистру. Для поиска без учета регистра задайте для аргумента match_case значение ЛОЖЬ.
3. Когда вы указываете регулярное выражение непосредственно в формуле, не забудьте заключить его в двойные кавычки (так как это текст).
4. Если допустимый шаблон не найден, функция вернет исходное содержимое без изменений.
5. Если регулярное выражение записано вами неверно, произойдет ошибка  #ЗНАЧ!.

Примеры регулярных выражений для поиска и замены в Excel

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

Замена строки, соответствующей шаблону

В приведенном ниже примере набора данных предположим, что вы хотите скрыть некоторые личные данные, такие как номер телефона. Учитывая, что это семизначное число в формате «000-00-00», для его нахождения мы используем следующее регулярное выражение.

Шаблон: \d{3}-\d{2}-\d{4}

Для замены используется эта строка:

Текст замены : ХХХ-ХХ-ХХ

С исходной строкой в ​​A5 полная формула принимает следующий вид:

=RegExpReplace(A5; "\d{3}-\d{2}-\d{2}"; "XXX-XX-XX")

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

=RegExpReplace(A5; $A$2; $B$2)

Замена чисел в тексте

Чтобы найти любую цифру от 0 до 9, используйте \d в своем регулярном выражении. Чтобы найти определенные цифры, используйте соответствующий квантификатор или создайте более сложное регулярное выражение, как показано в примерах ниже.

Заменить все числа

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

Шаблон : \d+

Например, чтобы заменить все числа в ячейке A5 звездочкой, используйте следующую формулу:

=RegExpReplace(A5; "\d+"; "*")

Заменить суммы денег

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

Шаблон : '(\$\d+\.?\d{0,2}\b)|(\₽\d+\.?\d{0,2}\b)|(\d+\.?\d{0,2}\₽)

Это регулярное выражение ищет и заменяет денежные величины вида $100, ₽100 и 100₽. Естественно, вы можете использовать и другие обозначения валюты платежа.

Вы ищете знак доллара или рубля, за которым следует одна или несколько цифр \$\d+ — эта часть соответствует единице доллара, \₽\d+ означает соответственно рубли. После основной единицы может быть или не быть дробной единицы. Чтобы соответствовать ему, вы ищете ноль или один знак десятичного разряда (точку), после которого идут от 0 до 2 цифр \.?\d{0,2}. Граница слова \b в конце гарантирует, что совпадающее значение не является частью большего числа.

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

Подставьте это регулярное выражение в нашу пользовательскую функцию, и вы получите следующий результат:

=RegExpReplace(A5; "'(\$\d+\.?\d{0,2}\b)|(\₽\d+\.?\d{0,2}\b)|(\d+\.?\d{0,2}\₽)"; "*")

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

Поиск и замена всех совпадений

В классических регулярных выражениях есть глобальный флаг поиска /g, который заставляет регулярное выражение находить все возможные совпадения в строке. В VBA этот флаг не поддерживается. Вместо этого объект VBA RegExp предоставляет свойство Global, которое определяет, следует ли искать все вхождения или только первое. 

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

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

Замена определенного по счёту экземпляра

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

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

=RegExpReplace(A5; $A$2; $B$2; 2)

Где $A$2 — шаблон регулярного выражения, а $B$2 — замещающий текст.

Как видно на скриншоте выше, второй телефон успешно заменён.

Поиск и замена в Excel с помощью Regex Tools

Вы слышали о недавнем дополнении к Ultimate Suite? Я имею в виду четыре мощных инструмента регулярных выражений , представленных в последней версии. По сравнению со своими аналогами VBA функции Ablebits Regex имеют два основных преимущества:

• Вы можете использовать регулярные выражения в обычных книгах .xlsx без необходимости вставлять какой-либо код или включать макросы в Excel.
• Обрабатываемые стандартным механизмом .NET RegEx, эти функции поддерживают полнофункциональные классические регулярные выражения и преодолевают многие ограничения VBA RegExp.

Функция AblebitsRegexReplace ищет совпадение с указанным регулярным выражением в ячейке и заменяет его указанными символами.

Синтаксис ее совершенно такой же, как у функции RegExpReplace, а вот возможности немного другие.

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

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

Как правило, чтобы найти символ, который используется как часть шаблона в регулярных выражениях, вам нужно поставить перед ним обратную косую черту. В регулярных выражениях \ действует как escape-символ, который отменяет «особое» назначение следующего символа и превращает его в обычный символ. Таким образом, чтобы найти квадратную скобку, вы ставите перед ней обратную косую черту: \[ для соответствия открывающей скобке и \] для соответствия закрывающей скобке. Между скобками поместите группу символов для поиска. В зависимости от того, какой квантификатор вы используете в группе, будут производиться разные замены.

Жадный шаблон: \[(.*)\]

Этот шаблон выполняет жадный поиск — захватывает как можно больше знаков. В начале ищем открывающую скобку [. Выражение .* соответствует как можно большему числу символов. Итак, этот шаблон захватывает все от первой открывающей скобки до последней закрывающей скобки ].

Ленивый шаблон: \[(.*?)\]

Этот шаблон выполняет ленивый поиск — захватывает как можно меньше. Ищем открывающую скобку ]. Знак вопроса ? заставляет .* искать совпадение с как можно меньшим количеством символов, пока не найдется следующее совпадение в шаблоне, то есть закрывающая скобка ]. Таким образом, этот шаблон захватывает все, от первой открывающей скобки до первой закрывающей скобки.

Отрицание: \[([^\]]*)\]

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

Как только вы определились с регулярным выражением, «скормите» его функции замены. Вот как:

1. На вкладке Ablebits Data в группе Text щелкните Regex Tools .
2. На панели Regex Tools настройте параметры
3. Выберите исходные строки.
4. Введите свой шаблон.
5. Выберите параметр Replace with (Заменить на) и вставьте замещающий текст в поле.
6. Чтобы результаты отображались в виде формул, а не значений, установите флажок Insert as a formula (Вставить как формулу) .
7. Нажмите кнопку Replace (Заменить) .

Чтобы результаты отображались в виде формул, а не значений, установите флажок Insert as a formula (Вставить как формулу) .

Нажмите кнопку Replace (Заменить) .

После этого функция AblebitsRegexReplace вставляется в новый столбец справа от исходных данных.

=AblebitsRegexReplace(A2; "\[(.*)\]"; "*")

Шаблон поиска и замещающий текст вы можете сохранить в отдельных ячейках и указывать соответствующие ссылки на них. В нашем случае мы вводим шаблоны в ячейки B2 и C2, вводим значение замены в A2, затем сравниваем результаты и выбираем желаемый результат:

Советы и примечания:

• Функция AblebitsRegexReplace находит и заменяет все совпадения.
• Функцию можно вставить непосредственно в ячейку через стандартное диалоговое окно «Вставить функцию», где она относится к категории AblebitsUDFs .

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

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

RegEx в Excel: шпаргалка по синтаксису регулярных выражений

Как создать пользовательскую функцию?

Почему пользовательская функция не работает: проблемы и решения

Пользовательская функция и макрос VBA: преимущества и недостатки

4 способа отладки пользовательской функции

Как правильно сохранить и применять пользовательскую функцию Excel

Как извлечь строку из текста при помощи регулярных выражений