Регулярные выражения Vba Excel

Обновлено: 02.12.2024

Механизм регулярных выражений

Вы можете использовать механизм регулярных выражений одним из двух способов:

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

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

Вы можете вызывать методы класса Regex для выполнения следующих операций:

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

Извлечь одно совпадение или первое совпадение.

Извлечь все совпадения.

Заменить совпадающую подстроку.

Разбить одну строку на массив строк.

Эти операции описаны в следующих разделах.

Сопоставление с шаблоном регулярного выражения

Метод Regex.IsMatch возвращает true, если строка соответствует шаблону, или false, если нет. Метод IsMatch часто используется для проверки ввода строки. Например, следующий код гарантирует, что строка соответствует действительному номеру социального страхования в США.

Шаблон регулярного выражения ^\d-\d-\d$ интерпретируется, как показано в следующей таблице.

Шаблон Описание
^ Совпадение с началом входной строки.
\d Совпадение с тремя десятичными цифрами.
- Соответствует дефису.
\d Соответствует двум десятичным цифрам.
- Соответствует дефису.
\d Соответствует четырем десятичным цифрам.
$ Соответствует концу входной строки.

Извлечение одного совпадения или первого совпадения

Метод Regex.Match возвращает объект Match, содержащий информацию о первой подстроке, соответствующей шаблону регулярного выражения. Если свойство Match.Success возвращает значение true , указывающее, что совпадение найдено, вы можете получить информацию о последующих совпадениях, вызвав метод Match.NextMatch. Вызовы этих методов могут продолжаться до тех пор, пока свойство Match.Success не вернет значение false . Например, в следующем коде метод Regex.Match(String, String) используется для поиска первого вхождения повторяющегося слова в строке. Затем он вызывает метод Match.NextMatch для поиска дополнительных вхождений. В примере проверяется свойство Match.Success после каждого вызова метода, чтобы определить, было ли текущее совпадение успешным и следует ли последующий вызов метода Match.NextMatch.

Шаблон регулярного выражения \b(\w+)\W+(\1)\b интерпретируется, как показано в следующей таблице.

Извлечение всех совпадений

Метод Regex.Matches возвращает объект MatchCollection, содержащий информацию обо всех совпадениях, найденных обработчиком регулярных выражений во входной строке. Например, предыдущий пример можно переписать так, чтобы вместо методов Match и NextMatch вызывался метод Matches.

Замена совпадающей подстроки

Метод Regex.Replace заменяет каждую подстроку, соответствующую шаблону регулярного выражения, на указанную строку или шаблон регулярного выражения и возвращает всю входную строку с заменами. Например, следующий код добавляет символ валюты США перед десятичным числом в строке.

Шаблон регулярного выражения \b\d+\.\d\b интерпретируется, как показано в следующей таблице.

Шаблон Описание
\b Начинайте сопоставление с границы слова.
(\w+) Соответствует одному или нескольким символам слова. Это первая захваченная группа.
\W+ Соответствует одному или нескольким символам, не являющимся словами.
(\1) Соответствует первой захваченной строке. Это вторая группа захвата.
\b Завершить совпадение на границе слова.
Шаблон Описание
\b Начинайте сопоставление с границы слова.
\d+ Соответствует одной или нескольким десятичным цифрам.
\. Соответствует точке.
\d Соответствует двум десятичным цифрам.
\b Конец совпадения на границе слова.

Шаблон замены $$$& интерпретируется, как показано в следующей таблице.

< td>Символ знака доллара ($).

Разбиение одной строки на массив строк

Метод Regex.Split разбивает входную строку в позициях, определенных совпадением регулярного выражения. Например, следующий код помещает элементы нумерованного списка в массив строк.

Шаблон регулярного выражения \b\d\.\s интерпретируется, как показано в следующей таблице.

Шаблон Строка замены
$$
$& Вся совпавшая подстрока.
Шаблон Описание
\b Начинайте сопоставление с границы слова.
\d Соответствует одной или двум десятичным цифрам.
\ . Соответствует точке.
\s Соответствует пробелу.

Объекты MatchCollection и Match

Методы Regex возвращают два объекта, которые являются частью объектной модели регулярного выражения: объект MatchCollection и объект Match.

Коллекция спичек

В следующем примере используется метод Regex.Matches(String) для заполнения объекта MatchCollection всеми совпадениями, найденными во входной строке. В примере перечисляется коллекция, совпадения копируются в массив строк и записываются позиции символов в массиве целых чисел.

Матч

Класс Match представляет результат совпадения одного регулярного выражения. Вы можете получить доступ к объектам Match двумя способами:

В следующем примере отдельные объекты Match извлекаются из объекта MatchCollection путем повторения коллекции с помощью foreach или For Each . Следующая конструкция. Регулярное выражение просто соответствует строке "abc" во входной строке.

Вызывая метод Regex.Match, который возвращает объект Match, представляющий первое совпадение в строке или части строки. Вы можете определить, было ли найдено совпадение, извлекая значение свойства Match.Success. Чтобы получить объекты Match, представляющие последующие совпадения, многократно вызывайте метод Match.NextMatch, пока свойство Success возвращенного объекта Match не станет равным false .

В следующем примере методы Regex.Match(String, String) и Match.NextMatch используются для сопоставления строки "abc" во входной строке.

Два свойства класса Match возвращают объекты коллекции:

Свойство Match.Groups возвращает объект GroupCollection, содержащий информацию о подстроках, соответствующих захватываемым группам в шаблоне регулярного выражения.

Свойство Match.Captures возвращает объект CaptureCollection, который имеет ограниченное использование. Коллекция не заполняется для объекта Match, свойство Success которого имеет значение false . В противном случае он содержит один объект Capture с той же информацией, что и объект Match.

Дополнительную информацию об этих объектах см. в разделах "Групповая коллекция" и "Коллекция захвата" далее в этой теме.

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

Класс Match также имеет два метода сопоставления с образцом:

Метод Match.NextMatch находит совпадение после совпадения, представленного текущим объектом Match, и возвращает объект Match, представляющий это совпадение.

Метод Match.Result выполняет указанную операцию замены совпадающей строки и возвращает результат.

В следующем примере метод Match.Result используется для добавления символа $ и пробела перед каждым числом, состоящим из двух цифр дробной части.

Шаблон регулярного выражения \b\d+(,\d)*\.\d\b определяется, как показано в следующей таблице.

Шаблон Описание
\b Начинайте сопоставление с границы слова.
\d+ Соответствует одной или нескольким десятичным цифрам.
(,\d)* Соответствует нулю или более вхождениям запятой, за которой следуют три десятичных цифры.
\. Соответствует символу десятичной точки.
\d Соответствует двум десятичным цифрам.
\b< /td> Завершить совпадение на границе слова.

Шаблон замены $$ $& указывает, что совпадающая подстрока должна быть заменена символом доллара ($) (шаблон $$), пробелом и значением совпадения (шаблон $&).< /p>

Групповая коллекция

Свойство Match.Groups возвращает объект GroupCollection, содержащий объекты Group, представляющие захваченные группы в одном совпадении. Первый объект Group в коллекции (с индексом 0) представляет полное совпадение.Каждый следующий объект представляет результаты одной группы захвата.

Вы можете получить отдельные объекты Group в коллекции с помощью свойства GroupCollection.Item[]. Вы можете извлекать неименованные группы по их порядковому положению в коллекции, а именованные группы — либо по имени, либо по порядковому положению. Безымянные захваты появляются первыми в коллекции и индексируются слева направо в том порядке, в котором они появляются в шаблоне регулярного выражения. Именованные захваты индексируются после безымянных захватов слева направо в том порядке, в котором они появляются в шаблоне регулярного выражения. Чтобы определить, какие пронумерованные группы доступны в коллекции, возвращаемой для определенного метода сопоставления регулярных выражений, можно вызвать метод экземпляра Regex.GetGroupNumbers. Чтобы определить, какие именованные группы доступны в коллекции, можно вызвать метод экземпляра Regex.GetGroupNames. Оба метода особенно полезны в подпрограммах общего назначения, которые анализируют совпадения, найденные любым регулярным выражением.

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

Шаблон регулярного выражения \b(\w+)\s(\d),\s(\d)\b определяется, как показано в следующей таблице.

Захваченная группа

В следующем примере используются вложенные конструкции группирования для захвата подстрок в группы. Шаблон регулярного выражения (a(b))c соответствует строке «abc». Он назначает подстроку "ab" первой группе захвата, а подстроку "b" - второй группе захвата.

В следующем примере конструкции именованной группировки используются для захвата подстрок из строки, содержащей данные в формате "ИМЯДАННЫХ:ЗНАЧЕНИЕ", которые регулярное выражение разбивает двоеточием (:).

Шаблон регулярного выражения ^(? \w+):(? \w+) определяется, как показано в следующей таблице.

Шаблон Описание
\b Начинайте сопоставление с границы слова.
(\w+) Соответствует одному или нескольким символам слова. Это первая захватываемая группа.
\s Соответствует пробелу.
(\d) Соответствует одной или двум десятичным цифрам. Это вторая группа захвата.
, Соответствует запятой.
\s< /td> Совпадение с пробельным символом.
(\d) Совпадение с четырьмя десятичными цифрами. Это третья группа захвата.
\b Завершить совпадение на границе слова.
Шаблон Описание
^ Начинайте сопоставление с начала входной строки.
(? \w+) Соответствует одному или нескольким символам слова. Имя этой группы захвата — name .
: Соответствует двоеточию.
( ? \w+) Соответствует одному или нескольким символам слова. Имя этой группы захвата — value .

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

Применение квантификаторов к группе (дополнительную информацию см. в разделе Квантификаторы) изменяет отношение одного захвата к группе захвата двумя способами:

Если * или *? квантификатор (указывающий ноль или более совпадений) применяется к группе, захватываемая группа может не иметь совпадения во входной строке. Если захваченного текста нет, свойства объекта «Группа» устанавливаются, как показано в следующей таблице.

Свойство группы Значение
Успех false
Value String.Empty
Длина 0

Следующий пример служит иллюстрацией. В шаблоне регулярного выражения aaa(bbb)*ccc первая захватываемая группа (подстрока "bbb") может совпадать ноль или более раз. Поскольку входная строка "aaaccc" соответствует шаблону, группа захвата не имеет соответствия.

Квантификаторы могут соответствовать множеству вхождений шаблона, определенного захватываемой группой. В этом случае свойства Value и Length объекта Group содержат информацию только о последней захваченной подстроке. Например, следующее регулярное выражение соответствует одному предложению, заканчивающемуся точкой. Он использует две конструкции группировки: первая захватывает отдельные слова вместе с символом пробела; второй фиксирует отдельные слова.Как видно из выходных данных примера, хотя регулярному выражению удается захватить все предложение целиком, вторая захватываемая группа захватывает только последнее слово.

Коллекция Capture

Объект «Группа» содержит информацию только о последнем захвате. Однако весь набор захватов, сделанных захватываемой группой, по-прежнему доступен из объекта CaptureCollection, возвращаемого свойством Group.Captures. Каждый элемент коллекции представляет собой объект Capture, представляющий захват, сделанный этой группой захвата, в том порядке, в котором они были захвачены (и, следовательно, в порядке, в котором захваченные строки сопоставлялись слева направо во входной строке). ). Вы можете получить отдельные объекты Capture из коллекции одним из двух способов:

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

Например, если вы используете шаблон регулярного выражения ((a(b))c)+ (где квантификатор + указывает одно или несколько совпадений) для захвата совпадений из строки "abcabcabc", объект CaptureCollection для каждой группы объект содержит три члена.

В следующем примере регулярное выражение (Abc)+ используется для поиска одного или нескольких последовательных запусков строки "Abc" в строке "XYZAbcAbcAbcXYZAbcAb". В примере показано использование свойства Group.Captures для возврата нескольких групп захваченных подстрок.

Индивидуальный захват

Класс Capture содержит результаты захвата одного подвыражения. Свойство Capture.Value содержит совпадающий текст, а свойство Capture.Index указывает отсчитываемую от нуля позицию во входной строке, с которой начинается совпадающая подстрока.

В следующем примере выполняется синтаксический анализ входной строки для определения температуры в выбранных городах. Запятая (",") используется для разделения города и его температуры, а точка с запятой (";") используется для разделения данных каждого города. Вся входная строка представляет собой одно совпадение. В шаблоне регулярного выражения ((\w+(\s\w+)*),(\d+);)+ , который используется для разбора строки, название города присваивается второй группе захвата, а температура присваивается к четвертой группе захвата.

Regex означает регулярное выражение. Регулярное выражение — это шаблон, состоящий из последовательности символов, которые можно использовать для поиска соответствующего шаблона в другой строке. Чтобы использовать Regex в VBA, вы должны использовать объект RegExp.

Шаблон, такой как [A-C], можно использовать для поиска и сопоставления прописных букв от A до C в последовательности. Шаблоны регулярных выражений имеют собственный синтаксис и могут быть созданы с использованием символа или последовательности символов.

Соответствие символов

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

Синтаксис шаблона Описание Пример Найдены совпадения
. Соответствует любому одиночному символу, кроме vbNewLine f.n фан, фон, f@n, fwn
[символы] Соответствует любому одиночному символу в квадратных скобках[] [fn] Соответствует только "f" или "n" в веере
[^characters] Соответствует любому одиночному символу, не заключенному в квадратные скобки[] [^fn] Так будет соответствовать "j" в "fjn"
[начало-конец] Соответствует любому символу, входящему в диапазон скобок[] 5 Соответствует "4" и "5" в "45"
\w Соответствует буквенно-цифровым символам и символу подчеркивания, но не пробелу \w Соответствует "c" в ",c.”
\W Соответствует любым небуквенно-цифровым символам и знаку подчеркивания \W Соответствует "@" в "bb@bb"
\s Соответствует любому пробелу, такому как пробелы и табуляции \s Соответствует ” ” в “Это”
\S Соответствует любому символу, отличному от пробела \S Соответствует "T" и "h" в "T h"
\d Соответствует любой одиночной десятичной цифре \d Соответствует "7" в "a7h"
\D Соответствует любой одиночной недесятичной цифре \D Соответствует j в "47j"
\ Экранирует специальные символы, что позволяет вам искать их \. Соответствует “.” в «59.pQ»
\t Вкладка \t Соответствует символу табуляции
\r Возврат каретки \r Соответствует возврату каретки (vbCr)
\n vbNewLine(vbTab) \n Соответствует новой строке

Квантификаторы

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

Квантификатор Описание Пример Найдены совпадения
* Соответствует нулю или более вхождений fn*a фна, фа, фнна, фннна, фнннна
+ Соответствует одному или нескольким вхождениям fn+a fna, fnna, fnfnna
? Соответствует нулю или единице fn?a fa, fna
Соответствует "n" много раз d\W Соответствует "d..." в «d….&5hi»
Соответствует не менее "n" раз d\W Соответствует «d….&» в «d….&5hi»
Соответствует числу от n до m d\W Соответствует «d….&&&&» в «d….&&&&5hi»

Группировка

Группировка или захват позволяет использовать шаблон для захвата и извлечения части строки. Таким образом, не только сопоставляется шаблон, но и захватывается та часть строки, которая соответствует шаблону.

Шаблон Описание Пример Найдены и зафиксированы совпадения
(выражение) Группирует и фиксирует шаблон в скобках (\W) Группирует и захватывает "@@@@" из "1@@@@1jlmba"

Как использовать регулярное выражение в VBA

Чтобы использовать Regex в VBA, сначала необходимо установить ссылку в редакторе VBE. В редакторе VBE выберите Инструменты > Ссылки > Регулярные выражения Microsoft VBScript.

Это свойства объекта RegExp:

  • Шаблон — шаблон, который вы собираетесь использовать для сопоставления со строкой.
  • IgnoreCase – если установлено значение True, регистр игнорируется при сопоставлении.
  • Global — если True, то будут найдены все совпадения шаблона в строке. Если False, будет найдено только первое совпадение.
  • MultiLine — если выбрано значение True, сопоставление с образцом происходит через разрывы строк.

Это методы объекта RegExp:

  • Тест — поиск шаблона в строке и возврат значения True, если совпадение найдено.
  • Заменить — замена вхождений шаблона строкой замены.
  • Выполнить — возвращает совпадения шаблона со строкой.

Проверка шаблона на соответствие строке

Метод Test можно использовать для проверки соответствия шаблона последовательности во входной строке. Результат True, если совпадение найдено. Следующий код покажет вам, как проверить шаблон на соответствие строке:

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

  • Функция в ячейке для возврата совпадающего шаблона или замененного значения в строке.
  • Sub для циклического просмотра столбца данных и извлечения совпадений с соседними ячейками.
  • Какая настройка необходима?
  • Что такое специальные символы Excel для регулярных выражений?

Я понимаю, что Regex не идеален для многих ситуаций (использовать или не использовать регулярные выражения?), поскольку Excel может использовать команды типа Left , Mid , Right , Instr для подобных манипуляций.

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

9 ответов 9

Регулярные выражения используются для сопоставления с образцом.

Для использования в Excel выполните следующие действия:

Шаг 1. Добавьте ссылку VBA в «Регулярные выражения Microsoft VBScript 5.5»

  • Выберите вкладку "Разработчик" (у меня нет этой вкладки, что мне делать?)
  • Выберите значок "Visual Basic" в разделе ленты "Код".
  • В окне "Microsoft Visual Basic для приложений" выберите "Инструменты" в верхнем меню.
  • Выберите "Ссылки".
  • Установите флажок "Регулярные выражения Microsoft VBScript 5.5", чтобы включить их в книгу.
  • Нажмите "ОК".

Шаг 2. Определите шаблон

  • Например. a-z соответствует строчным буквам от a до z
  • Например. 0-5 соответствует любому числу от 0 до 5

[] Соответствует ровно одному из объектов внутри этих скобок.

  • Например. [а] соответствует букве а
  • Например. [abc] соответствует одной букве, которая может быть a, b или c
  • Например. [a–z] соответствует любой строчной букве алфавита.

() Группирует разные совпадения для целей возврата. См. примеры ниже.

<> Множитель для повторяющихся копий шаблона, определенного перед ним.

  • Например. [a] соответствует двум последовательным строчным буквам a: aa
  • Например. [a] соответствует как минимум одной и максимум трем строчным буквам a , aa , aaa

+ Совпадение хотя бы с одним или несколькими шаблонами, определенными перед ним.

  • Например. a+ будет соответствовать последовательным a , aa , aaa и т. д.
<р>? Соответствует нулю или одному из шаблонов, определенных перед ним.

  • Например. Шаблон может присутствовать или отсутствовать, но может быть сопоставлен только один раз.
  • Например. [а-я]? соответствует пустой строке или любой отдельной строчной букве.

* Совпадение с нулевым или более шаблоном, определенным перед ним.

  • Например. Подстановочный знак для шаблона, который может присутствовать или отсутствовать.
  • Например.[a-z]* соответствует пустой строке или строке строчных букв.
<р>. Соответствует любому символу, кроме символа новой строки \n

  • Например. а. Соответствует строке из двух символов, начинающейся с a и заканчивающейся чем угодно, кроме \n
  • Например. a|b означает, что может быть сопоставлено либо a, либо b.
  • Например. красный|белый|оранжевый соответствует ровно одному из цветов.
  • Например. [^0-9] символ не может содержать число
  • Например. Символ [^aA] не может быть буквой a в нижнем регистре или буквой A в верхнем регистре.

\ Экранирует следующий за ним специальный символ (отменяет описанное выше поведение)

^ Совпадение должно происходить в начале строки

  • Например. ^a Первый символ должен быть строчной буквой a
  • Например. ^1 Первый символ должен быть числом.

Совпадение $ должно находиться в конце строки

  • Например. a$ Последним символом должна быть строчная буква a

Предопределенные сокращения символов:

Пример 1. Запустить как макрос

В следующем примере макроса просматривается значение в ячейке A1, чтобы определить, являются ли первые 1 или 2 символа цифрами. Если это так, они удаляются, а остальная часть строки отображается. Если нет, то появится окно с сообщением о том, что совпадений не найдено. Значения ячейки A1 12abc вернут abc , значение 1abc вернет abc , значение abc123 вернет «Не совпало», поскольку цифры не были в начале строки.

Пример 2. Выполнить как встроенную функцию

Этот пример аналогичен примеру 1, но настроен для работы в качестве функции в ячейке. Чтобы использовать, измените код на этот:

Поместите строки ("12abc") в ячейку A1. Введите эту формулу =simpleCellRegex(A1) в ячейку B1, и результатом будет "abc".

Пример 3. Цикл через диапазон

Этот пример аналогичен примеру 1, но циклически перебирает диапазон ячеек.

Пример 4. Разделение разных шаблонов

В этом примере выполняется циклический просмотр диапазона ( A1 , A2 и A3 ) и поиск строки, начинающейся с трех цифр, за которой следует один буквенный символ, а затем 4 цифры. Вывод разделяет совпадения шаблона на соседние ячейки с помощью () . $1 представляет первый шаблон, соответствующий первому набору () .

Дополнительные примеры шаблонов

Не забудьте установить regEx = Nothing . Вы получите исключения Out Of Memory, когда этот Sub выполняется достаточно часто.

Хорошо, я почти уверен, что это потому, что код находится в ThisWorkbook . Попробуйте переместить код в отдельный модуль .

@PortlandRunner в «Проводнике проекта» (?) В этом файле Excel отсутствовала подпапка «Модули», хотя в другом файле она была показана. Щелкните файл правой кнопкой мыши и выберите «вставить модуль», затем дважды щелкните «Модуль 1» и вставьте код. Сохранено. Вернуться к книге и снова ввести функцию - это сработало. Может стоит отметить ответ, ради неопытных вроде меня? Спасибо за помощь.

Нереально. простые инди-инструменты, такие как Notepad ++, имеют параметр «регулярное выражение» в разделе «Найти и заменить». но в инструменте мирового класса, таком как Excel, вы должны быть программистом, чтобы сделать это самым непонятным и сложным способом..

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

Как это работает

Для этого требуется 2–3 параметра.

  1. Текст для использования регулярного выражения.
  2. Регулярное выражение.
  3. Строка формата, указывающая, как должен выглядеть результат. Он может содержать $0, $1, $2 и так далее. $0 — это полное совпадение, $1 и выше соответствуют соответствующим группам совпадений в регулярном выражении. По умолчанию $0 .

Несколько примеров

Извлечение адреса электронной почты:

Извлечение нескольких подстрок:

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

Как использовать

Чтобы использовать эту UDF, сделайте следующее (примерно на основе этой страницы Microsoft. Там есть полезная дополнительная информация!):

  1. В Excel в файле с поддержкой макросов ('.xlsm') нажмите клавиши ALT+F11, чтобы открыть редактор Microsoft Visual Basic для приложений.
  2. Добавить ссылку VBA на библиотеку регулярных выражений (бессовестно скопированную из ответа Portland Runners++):
    1. Нажмите Инструменты –> Ссылки (извините за скриншот на немецком языке)
    2. Найти регулярные выражения Microsoft VBScript 5.5 в списке и установите рядом с ним флажок.
    3. Нажмите ОК.

      4. В большое текстовое окно посередине вставьте следующее:

      Сохраните и закройте окно редактора Microsoft Visual Basic for Applications.

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

      @Enissay: Попробуйте создать минимальную функцию foo() As Variant \n foo="Hello World" \n End Function UDF, чтобы проверить, работает ли это. Если да, доработайте все вышеперечисленное, если ничего базового не сломано (макросы отключены?).

      Расширение ответа Пацима для тех, кто спешит.

      добавьте следующий код:

      Шаблон регулярного выражения помещается в одну из ячеек и используется абсолютная ссылка. Функция будет привязана к книге, в которой она создана.
      Если есть необходимость использовать ее в разных книгах, сохраните функцию в Personal.XLSB

      Спасибо, что упомянули, что он должен быть в Personal.xlsb, чтобы быть доступным во всех документах Excel, над которыми вы работаете. Большинство (?) Другие ответы не проясняют этого. Personal.XLSB будет находиться в папке (возможно, потребуется создать папку) C:\Users\имя пользователя\AppData\Local\Microsoft\Excel\XLStart folder

      Я отказался от попыток заставить работать файл personal.xlsb. Вместо этого я помещаю эту функцию в постоянную коллекцию моего буфера обмена (arsclip) и просто создаю новый модуль всякий раз, когда он мне нужен. Смешно, насколько это сложно для функции, которая к 2021 году должна стать родной для Excel. PS: Прямо посреди этого Стек попросил меня заплатить за вставку. Все, сегодня 2 апреля. ^april\x20?0?1$' сегодня не работает. Я понял.

      Для некоторых людей из неанглоязычных стран это может быть интересно: вы должны использовать точку с запятой ";" вместо запятой "," в RegxFunc(B5,$C$2)

      Вот моя попытка:

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

      REGEXEXTRACT: извлекает совпадающие подстроки в соответствии с регулярным выражением.

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

      SUBSTITUTE: заменяет существующий текст новым текстом в строке.

      REPLACE: Заменяет часть текстовой строки другой текстовой строкой.

      Вы можете ввести их прямо в ячейку, и вы получите все, что захотите

      Они также хорошо работают в сочетании с другими функциями, такими как операторы IF, например так:

      Надеюсь, это поможет тем пользователям, которых не устраивает VBS-компонент Excel, найти простой обходной путь.

      Спасибо, что поделились, Алекс. Это полезно для тех, кто ищет версию Google. Вы можете написать и ответить на другой вопрос, относящийся к Google Sheets Regex, поскольку он имеет свою собственную номенклатуру и будет очень полезен для других. Несмотря ни на что, мой голос за вас!

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

      Хотя мы можем найти некоторые источники о том, что поддерживается, было бы полезно знать, какие метасимволы и т. д. не поддерживаются. Более подробное объяснение можно найти здесь. Упоминается в этом источнике:

      "Хотя регулярное выражение VBScript . версия 5.5 реализует довольно много важных функций регулярных выражений, которые отсутствовали в предыдущих версиях VBScript. . JavaScript и VBScript реализуют регулярные выражения в стиле Perl. Однако им не хватает целого ряда расширенных функций, доступных в Perl и других современных разновидностях регулярных выражений:"

      • Начало строки с символом \A , в качестве альтернативы используйте знак вставки ^, чтобы сопоставить позицию перед 1-м символом в строке
      • Конец строки с символом \Z . В качестве альтернативы используйте знак доллара $, чтобы соответствовать позиции после последнего символа в строке.
      • Положительный LookAhead, например: (? (пока поддерживается положительный LookAhead)
      • Отрицательный LookAhead, например: (? (пока поддерживается отрицательный LookAhead)
      • Юникод, например: \ . В качестве альтернативы используйте пронумерованные группы захвата.
      • Встроенные модификаторы, например: /i (чувствительность к регистру) или /g (глобальный) и т. д. Задайте их в свойствах объекта RegExp > RegExp.Global = True и RegExp.IgnoreCase = True, если они доступны. . Добавьте их с обычными комментариями ' в скрипте

      Я уже несколько раз упирался в стену, используя регулярные выражения в VBA. Обычно с LookBehind, но иногда я даже забываю модификаторы. Я сам не испытал все эти вышеупомянутые фоны, но подумал, что постараюсь быть обширным, ссылаясь на более подробную информацию. Не стесняйтесь комментировать/исправлять/дополнять. Большой привет регулярному-выражениям.info для большого количества информации.

      P.S. Вы упомянули обычные методы и функции VBA, и я могу подтвердить, что они (по крайней мере, для себя) по-своему полезны там, где RegEx не работает.

      Мне нужно было использовать это как функцию ячейки (например, СУММ или ВПР ), и я обнаружил, что это легко:

      1. Убедитесь, что вы находитесь в файле Excel с поддержкой макросов (сохраните как xlsm).
      2. Открыть инструменты разработчика Alt + F11.
      3. Добавить Регулярные выражения Microsoft VBScript 5.5, как и в других ответах

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

      Затем вы можете использовать в ячейке =REGPLACE(B1, "(\w) (\d+)", "$1$2") (например: "A 243" на "A243")

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

      Вот функция regex_subst(). Примеры:

      Вот упрощенный код (по крайней мере, для меня проще). Я не мог понять, как создать подходящий шаблон вывода, используя приведенное выше, чтобы он работал, как мои примеры:

      Я не хочу включать справочную библиотеку, так как мне нужно, чтобы мои скрипты были переносимыми. Строка Dim foo As New VBScript_RegExp_55.RegExp вызывала ошибки User Defined Type Not Defined, но я нашел решение, которое сработало для меня.

      Обновить комментарии RE с @chrisneilsen :

      У меня сложилось впечатление, что включение справочной библиотеки было привязано к настройкам локального компьютера, но на самом деле это связано непосредственно с рабочей книгой. Таким образом, вы можете включить справочную библиотеку, поделиться рабочей книгой с поддержкой макросов, и конечному пользователю также не придется включать библиотеку. Предупреждение: Преимущество позднего связывания заключается в том, что разработчику не нужно беспокоиться о неправильной версии библиотеки объектов, установленной на компьютере пользователя. Скорее всего, это не будет проблемой при использовании VBScript_RegExp_55. Библиотека RegExp, но я не уверен, что преимущество «производительности» того стоит для меня в настоящее время, поскольку мы говорим о незаметных миллисекундах в моем коде. Я чувствовал, что это заслуживает обновления, чтобы помочь другим понять. Если вы включите справочную библиотеку, вы можете использовать «раннее связывание», но если вы этого не сделаете, насколько я могу судить, код будет работать нормально, но вам нужно «позднее связывание» и потерять некоторую производительность/отладку функции.

      Что вам нужно сделать, так это поместить примерную строку в ячейку A1, а затем проверить свой strPattern. Как только это сработает, отрегулируйте, а затем rng по желанию.

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

      VBA RegEx — это объектная модель. Я знаю, это пугает, глядя на объяснение, но дело в том, что природа объекта. Здесь вам нужно помнить одну вещь: VBA RegEx (Regular Expression) — это объект текстовой функции, как и другие наши текстовые функции. Текстовые функции Функция TEXT в Excel — это строковая функция, используемая для изменения заданного ввода на текст, предоставленный в указанном числовом формате. Он используется, когда мы получаем большие наборы данных от нескольких пользователей и форматы разные. подробнее , «СЛЕВА, СПРАВА, СРЕДНЯЯ».

      Как включить регулярное выражение в Excel VBA?

      Как я уже говорил VBA, RegEx — это объектная модель в VBA, как и наше внешнее программное обеспечение, такое как «MS Word» и «MS PowerPoint». Точно так же RegEx также является объектной моделью компонентов (COM), на которую нам нужно ссылаться в редакторе VBA. Чтобы включить RegEx, выполните следующие действия.

        Перейдите в редактор Visual Basic (Alt + F11)

      Пример. Сейчас я покажу вам один простой пример. Предположим, у вас есть слова «Продажи 2019, Продажи 2018 и Продажи 2017». Если вы определите шаблон как [0–7], он будет соответствовать всем числам от 0 до 7, поэтому в каждой строке будут найдены совпадения 201, 201 и 2017.

      Шаблон регулярных выражений VBA

      Шаблон функции VBA RegEx выглядит устрашающе, и требуется некоторое время, чтобы понять его. Здесь мы видим два типа последовательности символов: один — «буквенные символы», а другой — «метасимволы».

      • Литеральные символы ищут точное совпадение с предоставленной строкой. Например, буквенная последовательность символов «EFG» просто ищет все совпадения «EFG» в предоставленном тексте.
      • Метасимволы — это не что иное, как комбинация символов с точным значением в шаблоне RegEx. Это полностью отличается от буквальных символов. Это огромная тема для освещения. Ниже приведены некоторые из важных синтаксисов.

      Рекомендуемые статьи

      Это руководство по VBA RegEx. Здесь мы обсудим, как использовать RegEx (регулярное выражение) в Excel VBA вместе с его свойствами, шаблоном и примером. Вы можете узнать больше об Excel VBA из следующих статей —

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