Анализ текста регулярными выражениями (RegExp) в Excel

4146 11.02.2018 Скачать пример

Разбор текста регулярными выражениямиОдной из самых трудоемких и неприятных задач при работе с текстом в Excel является парсинг - разбор буквенно-цифровой "каши" на составляющие и извлечение из нее нужных нам фрагментов. Например:

  • извлечение почтового индекса из адреса (хорошо, если индекс всегда в начале, а если нет?)
  • нахождение номера и даты счета из описания платежа в банковской выписке
  • извлечение ИНН из разношерстных описаний компаний в списке контрагентов
  • поиск номера автомобиля или артикула товара в описании и т.д.

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

  • Использовать встроенные текстовые функции Excel для поиска-нарезки-склейки текста: ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), СЦЕПИТЬ (CONCATENATE) и ее аналоги, ОБЪЕДИНИТЬ (JOINTEXT), СОВПАД (EXACT) и т.д. Этот способ хорош, если в тексте есть четкая логика (например, индекс всегда в начале адреса). В противном случае формулы существенно усложняются и, порой, дело доходит даже до формул массива, что сильно тормозит на больших таблицах.
  • Использование оператора проверки текстового подобия Like из Visual Basic, обернутого в пользовательскую макро-функцию. Это позволяет реализовать более гибкий поиск с использованием символов подстановки (*,#,? и т.д.) К сожалению, этот инструмент не умеет извлекать нужную подстроку из текста - только проверять, содержится ли она в нем.

Кроме вышеперечисленного, есть еще один подход, очень известный в узких кругах профессиональных программистов, веб-разработчиков и прочих технарей - это регулярные выражения (Regular Expressions = RegExp = "регэкспы" = "регулярки"). Упрощенно говоря, RegExp - это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Регулярные выражения - это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом. Многие языки программирования (C#, PHP, Perl, JavaScript...) и текстовые редакторы (Word, Notepad++...) поддерживают регулярные выражения.

Microsoft Excel, к сожалению, не имеет поддержки RegExp по-умолчанию "из коробки", но это легко исправить с помощью VBA. Откройте редактор Visual Basic с вкладки Разработчик (Developer) или сочетанием клавиш Alt+F11. Затем вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такой макрофункции: 

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = Pattern
    regex.Global = True
    If regex.Test(Text) Then
        Set matches = regex.Execute(Text)
        RegExpExtract = matches.Item(Item - 1)
        Exit Function
    End If
ErrHandl:
    RegExpExtract = CVErr(xlErrValue)
End Function

Теперь можно закрыть редактор Visual Basic и, вернувшись в Excel, опробовать нашу новую функцию. Синтаксис у нее следующий:

=RegExpExtract( Txt ; Pattern ; Item )

где

  • Txt - ячейка с текстом, который мы проверяем и из которого хотим извлечь нужную нам подстроку
  • Pattern - маска (шаблон) для поиска подстроки
  • Item - порядковый номер подстроки, которую надо извлечь, если их несколько (если не указан, то выводится первое вхождение)

Самое интересное тут, конечно, это Pattern - строка-шаблон из спецсимволов "на языке" RegExp, которая и задает, что именно и где мы хотим найти. Вот самые основные из них - для начала:

 Паттерн  Описание
 . Самое простое - это точка. Она обозначает любой символ в шаблоне на указанной позиции.
 \s Любой символ, выглядящий как пробел (пробел, табуляция или перенос строки).
 \S
 Анти-вариант предыдущего шаблона, т.е. любой НЕпробельный символ.
 \d
 Любая цифра
 \D
 Анти-вариант предыдущего, т.е. любая НЕ цифра
 \w Любой символ латиницы (A-Z), цифра или знак подчеркивания
 \W Анти-вариант предыдущего, т.е. не латиница, не цифра и не подчеркивание.
[символы] В квадратных скобках можно указать один или несколько символов, разрешенных на указанной позиции в тексте. Например ст[уо]л будет соответствовать любому из слов: стол или стул.
Также можно не перечислять символы, а задать их диапазоном через дефис, т.е. вместо [ABDCDEF] написать [A-F]. или вместо [4567] ввести [4-7]. Например, для обозначения всех символов кириллицы можно использовать шаблон [а-яА-ЯёЁ].
[^символы] Если после открывающей квадратной скобки добавить символ "крышки" ^, то набор приобретет обратный смысл - на указанной позиции в тексте будут разрешены все символы, кроме перечисленных. Так, шаблон [^ЖМ]уть найдет Путь или Суть или Забудь, но не Жуть или Муть, например.
 | Логический оператор ИЛИ (OR) для проверки по любому из указанных критериев. Например (счет|счёт|invoice) будет искать в тексте любое из указанных слов. Обычно набор вариантов заключается в скобки.
 ^ Начало строки
 $ Конец строки
 \b Край слова

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

  Квантор  Описание
 ? Ноль или одно вхождение. Например .? будет означать один любой символ или его отсутствие.
 + Одно или более вхождений. Например \d+ означает любое количество цифр (т.е. любое число от 0 до бесконечности).
 * Ноль или более вхождений, т.е. любое количество. Так \s* означает любое количество пробелов или их отсутствие.
{число} или
{число1,число2}
 Если нужно задать строго определенное количество вхождений, то оно задается в фигурных скобках. Например \d{6} означает строго шесть цифр, а шаблон \s{2,5} - от двух до пяти пробелов

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

Извлекаем числа из текста

Для начала разберем простой случай - нужно извлечь из буквенно-цифровой каши первое число, например мощность источников бесперебойного питания из прайс-листа:

Извлекаем первое число из текста

Логика работы регулярного выражения тут простая: \d - означает любую цифру, а квантор + говорит о том, что их количество должно быть одна или больше. Двойной минус перед функцией нужен, чтобы "на лету" преобразовать извлеченные символы в полноценное число из числа-как-текст.

Почтовый индекс

На первый взгляд, тут все просто - ищем ровно шесть цифр подряд. Используем спецсимвол \d для цифры и квантор {6} для количества знаков:

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

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

Ошибочное извлечение индекса

Чтобы этого не происходило, необходимо добавить в наше регулярное выражение по краям модификатор \b означающий конец слова. Это даст понять Excel, что нужный нам фрагмент (индекс) должен быть отдельным словом, а не частью другого фрагмента (номера телефона):

Извлекаем ровно 6-разрядное число

Телефон

Проблема с нахождением телефонного номера среди текста состоит в том, что существует очень много вариантов записи номеров - с дефисами и без, через пробелы, с кодом региона в скобках или без и т.д. Поэтому, на мой взгляд, проще сначала вычистить из исходного текста все эти символы с помощью нескольких вложенных друг в друга функций ПОДСТАВИТЬ (SUBSTITUTE), чтобы он склеился в единое целое, а потом уже примитивной регуляркой \d{11} вытаскивать 11 цифр подряд:

Вытаскиваем номер телефона из текста

ИНН

Тут чуть сложнее, т.к. ИНН (в России) бывает 10-значный (у юрлиц) или 12-значный (у физлиц). Если не придираться особо, то вполне можно удовлетвориться регуляркой \d{10,12}, но она, строго говоря, будет вытаскивать все числа от 10 до 12 знаков, т.е. и ошибочно введенные 11-значные. Правильнее будет использовать два шаблона, связанных логическим ИЛИ оператором | (вертикальная черта):

Извлечение ИНН из текстовой строки

Обратите внимание, что в запросе мы сначала ищем 12-разрядные, и только потом 10-разрядные числа. Если же записать нашу регулярку наоборот, то она будет вытаскивать для всех, даже длинных 12-разрядных ИНН, только первые 10 символов. То есть после срабатывания первого условия дальнейшая проверка уже не производится:

Некорректное извлечение ИНН

Это принципиальное отличие оператора | от стандартной экселевской логической функции ИЛИ (OR), где от перестановки аргументов результат не меняется.

Артикулы товаров

Во многих компаниях товарам и услугам присваиваются уникальные идентификаторы - артикулы, SAP-коды, SKU и т.д. Если в их обозначениях есть логика, то их можно легко вытаскивать из любого текста с помощью регулярных выражений. Например, если мы знаем, что наши артикулы всегда состоят из трех заглавных английских букв, дефиса и последующего трехразрядного числа, то:

Артикулы

Логика работы шаблона тут проста. [A-Z] - означает любые заглавные буквы латиницы. Следующий за ним квантор {3} говорит о том, что нам важно, чтобы таких букв было именно три. После дефиса мы ждем три цифровых разряда, поэтому добавляем на конце \d{3}

Денежные суммы

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

Извлечь стоимость (цену) из текста

Паттерн \d с квантором + ищет любое число до дефиса, а \d{2} будет искать копейки (два разряда) после.

Если нужно вытащить не цены, а НДС, то можно воспользоваться третьим необязательным аргументом нашей функции RegExpExtract, задающим порядковый номер извлекаемого элемента. И, само-собой, можно заменить функцией ПОДСТАВИТЬ (SUBSTITUTE) в результатах дефис на стандартный десятичный разделитель и добавить двойной минус в начале, чтобы Excel интерпретировал найденный НДС как нормальное число:

Извлекаем суммы и НДС из текста

Автомобильные номера

Если не брать спецтранспорт, прицепы и прочие мотоциклы, то стандартный российский автомобильный номер разбирается по принципу "буква - три цифры - две буквы - код региона". Причем код региона может быть 2- или 3-значным, а в качестве букв применяются только те, что похожи внешне на латиницу. Таким образом, для извлечения номеров из текста нам поможет следующая регулярка:

Извлекаем автомобильный номер из текста

Время

Для извлечения времени в формате ЧЧ:ММ подойдет такое регулярное выражение:

Извлекаем время из текста

После двоеточия фрагмент [0-5]\d, как легко сообразить, задает любое число в интервале 00-59. Перед двоеточием в скобках работают два шаблона, разделенных логическим ИЛИ (вертикальной чертой):

  • [0-1]\d - любое число в интервале 00-19
  • 2[0-3] - любое число в интервале 20-23

К полученному результату можно применить дополнительно еще и стандартную Excel'евскую функцию ВРЕМЯ (TIME), чтобы преобразовать его в понятный программе и пригодный для дальнейших расчетов формат времени.

Проверка пароля

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

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

Проверка пароля регулярным выражением

По сути, таким шаблоном мы требуем, чтобы между началом (^) и концом ($) в нашем тексте находились только символы из заданного в квадратных скобках набора. Если нужно проверить еще и длину пароля (например, не меньше 6 символов), то квантор + можно заменить на интервал "шесть и более" в виде {6,}:

Проверка длины пароля

Город из адреса

Допустим, нам нужно вытащить город из строки адреса. Поможет регулярка, извлекающая текст от "г." до следующей запятой:

Извлечь город из адреса

Давайте разберем этот шаблон поподробнее.

Если вы прочитали текст выше, то уже поняли, что некоторые символы в регулярных выражениях (точки, звездочки, знаки доллара и т.д.) несут особый смысл. Если же нужно искать сами эти символы, то перед ними ставится обратная косая черта (иногда это называют экранированием). Поэтому при поиске фрагмента "г." мы должны написать в регулярке г\. если ищем плюсик, то \+ и т.д.

Следующих два символа в нашем шаблоне - точка и звездочка-квантор - обозначают любое количество любых символов, т.е. любое название города.

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

Извлекаем город из адреса - жадный квантор

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

Скупой квантор

Имя файла из полного пути

Еще одна весьма распространенная ситуация - вытащить имя файла из полного пути. Тут поможет простая регулярка вида:

Извлечь имя файла из полного пути

Тут фишка в том, что поиск, по сути, происходит в обратном направлении - от конца к началу, т.к. в конце нашего шаблона стоит $, и мы ищем все, что перед ним до первого справа обратного слэша. Бэкслэш заэкранирован, как и точка в предыдущем примере.

P.S.

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

Для анализа и разбора чужих регулярок или отладки своих собственных есть несколько удобных онлайн-сервисов: RegEx101, RegExr и др.

К сожалению, не все возможности классических регулярных выражений поддерживаются в VBA (например, обратный поиск или POSIX-классы) и умеют работать с кириллицей, но и того, что есть, думаю, хватит на первое время, чтобы вас порадовать.

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

Ссылки по теме



Страницы: 1  2  
InExSu
18.02.2018 16:31:28
Привет! Спасибо!
Знать - хорошо..
Уметь донести = бесценно. 8)
Ссылка
Николай Павлов
18.02.2018 19:13:07
Спасибо! Долго вынашивал эту статью, пытаясь простыми словами и на понятных примерах донести всю мощь и красоту регэкспов. Тема бездонная, конечно :)
Родитель Ссылка
InExSu
03.04.2018 13:44:29
Привет!
RegExpExtract у Вас возвращает String, а CVErr возвращает Variant.
Может сделать:

RegExpExtract = Cstr(CVErr(xlErrValue))


?
Родитель Ссылка
Бахтиёр
19.02.2018 07:33:39
Николай, спасибо, очень полезная статья.
пытаясь простыми словами и на понятных примерах донести всю мощь и красоту регэкспов
Вам это удалось.
Ссылка
Сергей Трекин
19.02.2018 08:55:30
Спасибо. А можно добавить такую возможность в PLEX. Для наиболее ходовых случаев. Буду благодарен. Успехов и удачи в Вашем  нужном деле просвещения масс.
Ссылка
Николай Павлов
19.02.2018 21:28:49
Уже :) В следующем обновлении будет парочка макрофункций для работы с регулярками. Более продвинутый вариант той, что упоминается в статье.
Родитель Ссылка
Эльбрус
22.02.2018 02:45:55
Урррааааа!!!:like:
Родитель Ссылка
Дмитрий Гиенко
05.04.2018 14:27:21
Великолепно! :D
Родитель Ссылка
Александр Иванов
22.04.2020 16:03:25
Николай, добрый день. Не могу найти в PLEX такие макрофункции. Не добавляли?
Родитель Ссылка
Роман Емельянов
19.02.2018 11:26:08
Очень здорово!
Когда-то давно нужно было и до сих пор ежегодно пользуюсь вытаскиваем индексов (6 подряд идущих цифр). Один наш контрагент имеет привычку скидывать Word файл с более чем 200 адресами типа "Организация, Индекс, Адрес, Телефон", а нужно было всё это расцепить.

Потом уже с установкой 2013 Excel вытаскивать индекс стало можно делать встроенной функцией "Мгновенное заполнение" (FlashFill).

Еще на форуме просил дать почитать об этом где-нибудь, вот такую дали ссылку:
http://www.script-coding.com/WSH/RegExp.html

Также с такого рода задачами по вытаскиваю какого-то шаблонного текста помогает PowerQuery функцией "Column from Examples".
Ссылка
Nego Sergey
19.02.2018 11:51:42
Николай, спасибо! Очень круто!!!

Логично поддержка регулярных выражений могла быть встроена в Power Query ;), но я так и не нашёл встроенной функции «RegExp» в языке «М»..
Ссылка
Николай Павлов
19.02.2018 21:30:11
И я не нашел :( Грусть.
Родитель Ссылка
Алексей Семилетов
22.02.2018 17:27:51
Ну, как бы есть вариант через костыль с R scripts в PBI, но этой штуки нет в Экселе, к сожалению.
Родитель Ссылка
Роман Емельянов
01.03.2018 09:52:54
Вот спасибо! Очень полезное знание. Попробовал получилось.
Родитель Ссылка
Александр
21.10.2021 02:29:24
Регулярные выражения (RegExp) в Power Query тут www.planetaexcel.ru/techniques/7/13310/
Родитель Ссылка
Светлана Агибаева
19.02.2018 14:59:55
Спасибо, Николай. Сложный материал разъяснен очень понятно.
Ссылка
Ivan M.
19.02.2018 16:42:34
Спасибо за статью, Николай. Очень полезная информация
Ссылка
VOLODIMIR BEBIAKIN
19.02.2018 18:50:10
Соглашусь со всеми, очень доходчиво.
Ссылка
Кирилл Макаров
20.02.2018 15:23:55
Шикарно!!!! Спасибо!!!
Ссылка
Антон О
20.02.2018 21:10:03
Интересненько )Спасибо, Николай!
Подскажите, а если, например, такой вариант "Текст-первый ТП-1 5900*2400" то как должно выглядеть регулярное выражение если нужно вытащить только 5900*2400? У меня только 5900 получилось (вот так в формуле ;2 а в устойчивом выражении /d{4}). Спасибо
Ссылка
Николай Павлов
21.02.2018 08:56:49
Антон, попробуйте [0-9\*] или \d{4}\*\d{4}
Родитель Ссылка
Антон О
21.02.2018 09:28:23
Спасибо!
\d{4}\*\d{4} - я, до того как вопрос задать, сидел и думал как самому сделать формулу. Строил такую же конструкцию, но она почему-то не работала ((
Удивительное совпадение, эта новость очень вовремя! Только вчера ломал голову как с помощью прав и левсимв вытаскивать габариты:like:
Родитель Ссылка
Сергей Евдокимов
03.09.2020 06:54:24
[^ ]*$
Но важно поймать системность в наборе данных. От этого зависит какой будет регулярка.
Родитель Ссылка
Александр
21.02.2018 08:04:22
Спасибо, очень интересно.
Только не пойму как написать регулярку, для того чтобы извлечь из строки типа: "что-то_там" : [ 1, 0, 3, 2, 5, 4, 6 ]
только цифры??
Ссылка
Дмитрий Побежимов
21.02.2018 08:50:54
Попробуйте сперва убрать символы множественной функцией "Подставить", как описывал Николай в примере, а потом извлечь цифры.
Родитель Ссылка
Сергей Евдокимов
03.09.2020 07:01:24
Что надо извлечь ? Цифры ?
\d*
Родитель Ссылка
chobo
21.02.2018 14:00:45
Очень здорово, спасибо, как раз под мою текущую задачу.
Есть еще один вопросик, если не сложно.
У меня задача выдергивать куски текста между заданными словами, причем эти слова исключая, ну, например:
Текст
Record number:    105473783

Record type:    Noncompliance Issue

Status:    Closed

Originator:    Volodymyr
Вытащить текст между Status: и Originator:
Должно получиться Closed
При чем в тексте присутствуют как знаки табуляции так и перевода строки.
И в общем случае возможно, что нужный фрагмент состоит из нескольких строк.
Ссылка
ruiz
21.02.2018 17:34:06
А почему в файле примера на листе Числа второй и последний пример срабатывает не корректно (2 и 50)?
Ссылка
Николай Павлов
21.02.2018 19:56:58
Там функция RegExpExtract используется с третьим аргументом 2, т.е. извлекает второе число, а не первое - так что все ОК :)
Родитель Ссылка
Эльбрус
22.02.2018 02:43:10
Ой, а у меня почему-то не работает, в ячейке глазеет на меня #ЗНАЧ и всё.
Что я опять не так сделал?:facepalm:
Excel 2016.
Ссылка
Николай Павлов
22.02.2018 08:58:16
Скачайте пример по ссылке из заголовка статьи - там работает?
Родитель Ссылка
Эльбрус
22.02.2018 09:05:34
Спасибо за отклик, Николай!
Нет и в примере не работает.

(P.S. Ха-ха, что называется зарапортовался. Назвал Вас Павлом.))) Работая в эксель, параллельно слушал Ю.Болдырева, его агитацию за Павла, который Грудинин, ну, вот и ... Короче, исправил.:D)
Родитель Ссылка
Александр Иванов
24.04.2020 13:15:03
Николай, здравствуйте.

Почему в вашем примере все работает, а создаю новую книгу, заношу в редактор VBA тот же самый текст. Делаю все тоже в книге в тех же ячейках. Результат #ЗНАЧ. Что только не пробовал. Не могу понять. Поясните, пожалуйста.
Родитель Ссылка
Dmitry Pasmor
05.12.2019 16:16:35
Удалите в module    " Option Explicit "  и все будет Ok
Родитель Ссылка
Дмитрий Плотников
01.06.2023 17:11:23
я слэш не в ту сторону ставил
Родитель Ссылка
Alex Grr
22.02.2018 05:36:23
оставляйте полезные при работе в Excel регулярки
Вот так проверяю корректность введенного e-mail

.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
Ссылка
Алексей Семилетов
22.02.2018 18:13:03
Есть еще такой шаблон 

"^[[:alnum:].-_]+@[[:alnum:].-]+$"
Почерпнул отсюда.
Родитель Ссылка
Dain
25.10.2018 00:09:21
первое выражение не найдет чего-то такого:
username@site.somelongzone

, второе - такого:
user-name@site.ru

А ведь и то, и другое встречается в реальном мире.
Вот ещё, на десерт:
username@я-вообще-то-тоже-сайт.рф

p.s.: по всем смыслам кошерная регулярка на валидность email'а выглядит как длинющиий паровоз. Оставьте
Родитель Ссылка
Эльбрус
23.02.2018 01:23:28
Так всё-таки кто-нибудь может подсказать почему у меня regexp не работает?:cry:
Ссылка
Николай Павлов
23.02.2018 07:23:16
Вы бы хоть скриншот сюда приложили :)
Родитель Ссылка
Эльбрус
23.02.2018 14:35:56
что-то скрин не вставляется.(
Родитель Ссылка
Эльбрус
25.02.2018 01:33:01
Николай, я так и не смог вставить скриншот. Да там и смотреть-то не на что,)) в смысле глазеет на тебя #ЗНАЧ в ячейках и всё.
Все разрешения даны, всё включено, но никак не хочет работать. Есть какая-нибудь подсказка?
Родитель Ссылка
Ольга Скрипченко
23.02.2018 20:21:52
Я добавила:

Dim regex As RegExp
Dim matches As Object
После этого заработало.
Родитель Ссылка
azma azma
24.02.2018 12:21:33
Николай, спасибо! Ooooчень круто!!!
Ссылка
Иван Бездомный
26.02.2018 08:07:41
Здравствуйте! Скажите какая должна быть маска, чтобы из массива извлечь ФИО, например Иванов Иван Иванович, Петров Сергей Петрович и т.п.?
Ссылка
Николай Павлов
05.03.2018 18:10:04
Иван, любые регулярки - вещь относительная. В том смысле, что важно не только то, что мы хотим извлечь, но и то, что вокруг. Если у вас в строке одни цифры и в середине ФИО - одно дело. Если в строке одна латиница, а в середине ФИО на кириллице - другое и т.д. Без полной картины никто хорошую регулярку не придумает :)
Родитель Ссылка
Максим
26.02.2018 18:31:00
Подскажите, как сделать так что бы эта функция работала в других книгах excel. а не только в той в которой ее создаешь?
Ссылка
Николай Павлов
05.03.2018 18:07:28
Скопировать ее в Личную книгу макросов Personal.xlsb - все макросы и функции из нее доступны в любой книге на данном компьютере.
Родитель Ссылка
Анна Коновальцева
06.03.2018 10:26:19
Очень полезная информация, спасибо вам, Николай, огромное.:like:
Но есть проблема: модуль с функцией находится в Personal, первый раз всё сработало на ура, но при создании новой книги функцию excel не распознаёт.
Подскажите, пожалуйста, как сделать, чтобы всё работало?:)
Ссылку на скрин экрана прилагаю:
cloud.mail.ru/public/JYbd/R1suJRZde


Спасибо!
Ссылка
Игорь Островитянин
09.03.2018 14:56:23
Желательно бы добавить к книге с примерами лист "Отладка" и написать функцию отладки рег. выражения. Иначе сложно понять в полном объеме, что происходит (#ЗНАЧ!). Функция, по-моему, должна перебирать исходное выражение, последовательно добавляя по символу, начиная с первого, и возвращать последнее РАБОЧЕЕ состояние шаблона, т.е. такой шаблон, который возвращает что-то (синтаксически верное выражение может не работать). Тогда будет видно, где начались проблемы. Также надо выдавать отчет в окне сообщения со статусом выражения (ОК, ОШИБКА, ЧАСТИЧНО ПРАВИЛЬНО), рабочим состоянием шаблона, если он усечен, и найденными совпадениями (ТОП N) в формате: номер, значение, позиция, длина. И всё это в Excel... чтобы не обращаться, например, сюда:

www.regex101.com
Ссылка
Олег Нанов
10.03.2018 23:18:02
как удалить лишние запятые с такого текста в одной ячейке
Шевченко Максим Леонардович 4567896
123456, СК обл., г. Ставрополь, село Приморское, хутор. Волянский,,,,,
ул. Малиновая, д.81 кв.21, проезд 4, стр. 7, квартал 554, корпус 11, а/я 78
Ссылка
Настя
14.04.2018 10:51:08
Предлагаю сделать в 2 этапа. На первом найти все лишние запятые регулярным выражением таким: ,,+
На втором заменить результат, найденный предыдущей операцией на одну запятую формулой "подставить": =ПОДСТАВИТЬ(A1;B1;","), где вместо А1 написать адрес исходный текст, а вместо B1 - ячейку с формулой RegExpExtract, в которой использовалось указанное регулярное выражение..
Родитель Ссылка
Олег Нанов
14.04.2018 11:53:53
спасибо получилось 
 =ПОДСТАВИТЬ(B3;RegExpExtract(B3;",,+");",")
Родитель Ссылка
Настя
16.04.2018 10:10:50
дада, конечно, можно всё в одну формулу поместить, правильно))
Родитель Ссылка
Dain
25.10.2018 00:15:32
а обычной кнопкой экселя "заменить" - не?)))
Родитель Ссылка
Сергей Евдокимов
03.09.2020 07:06:32
Не приумножайте сущностей (Окама).
Ctrl+H вам в помощь.
Родитель Ссылка
hima_xej
03.04.2018 16:26:42
Есть запись "K1-0101, K1-0102". Как записать два выражения, чтобы одно искало сначала первую часть (до запятой), т.е. K1-0101, а второе чтобы вторую часть, т.е. K1-0102?
Ссылка
Настя
14.04.2018 10:43:27
Может быть, так? Для первого случая регулярное выражение: [^,]+
Для второго: ,[^,]+
Правда, во втором случае в ответ войдёт запятая...
Вообще же можно использовать функции ПРАВСИМВ и ЛЕВСИМВ. Для первого случая формула: ЛЕВСИМВ(A1;ПОИСК(",";A1)-1) для второго: ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(",";A1)-1) (соответственно понятно, вместо A1 написать адрес ячейки, в которой расположено выражение "K1-0101, K1-0102").

А, вот сообразила как без ПРАВСИМВ и ЛЕВСИМВ исключить запятую во втором случае: [^, ]+$
Родитель Ссылка
Настя
16.04.2018 10:19:14
Вот так проверяю корректность введенного e-mail
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
а ведь так лучше, чем у автора в файле, я извиняюсь я что имею в виду - у автора не проходят проверку адреса с доменами второго и так далее порядка, а с этой формулой - проходят.
заодно узнаём о полезных свойствах круглых скобочек))
Ссылка
Дмитрий Дудков
17.04.2018 17:10:08
Доброго дня!
Вот пример текста:
CM Е. Иван Иванов 29 103 Г 131 - 1/1 1 6.7 64 84
RM Петр Петров 26 89 Ск3 Г 158 - 1/1 - 5.4 67 106
как выдернуть из него 6.7 и 5.4. Зацепка только одна, число после второго пробела с конца
Ссылка
Игорь Островитянин
29.04.2018 14:24:03
=RegExpExtract("Text";"\d+\.\d+")
Родитель Ссылка
Остап Малашенко
27.04.2018 12:52:31
Как раз сейчас занимаюсь регулярными выражениями, но больше в "R". Сегодня открыл безумно красивый способ отображения регулярного выражения:
regexper.com/

Пишете в текстовом поле регулярку, жмете кнопку "Display" - регулярка отображается в виде блок-схемы. Чудеса!
Ссылка
Николай Павлов
21.05.2018 09:13:04
Вот это красиво, да :)
Родитель Ссылка
excel-alex
04.05.2018 14:32:50
Спасибо за статью и плагин - очень выручили.
Однако есть вопрос по отрицанию, как вырезать все от слова до конца, либо до стоп-слова, если есть.
Например, ячейки с текстом:
Кресло AB1-34-5 ABCD  цвет: ABC (АБВ) (по 2 шт./1 кор.)
Кресло AB2-34-6 ABCD  цвет: ABCD (АБВГ)
надо превратить в :
цвет: ABC (АБВ)
цвет: ABCD (АБВГ)

Похоже решили:
цвет.*(?=\(по.*) - так он вытаскивает из первой строки
Ссылка
Сергей Евдокимов
03.09.2020 07:26:40
цвет: [A-ZА-Я( )]*
В конце пробел.
Родитель Ссылка
ConfusedMango
17.05.2018 13:18:09
Ура! Всё заработало, есть функция =RegExpExtract(B1;"\d+/\d+";1) когда указываю на конкретную ячейку В1, он извлекает как надо, но когда я пытаюсь применить её не на одну ячейку, а на столбец =RegExpExtract(B1:B10;"\d+/\d+";1) , то он выдаёт мне #Знач!. Я так понимаю, где-то с синтаксисом не правильно, где я свернул не туда?
Ссылка
Николай Павлов
21.05.2018 09:11:43
Потому что первый аргумент должен быть одной ячейкой по определению.
Если надо применить к целому столбцу, то копируйте потом вниз на 10 ячеек - и все.
Родитель Ссылка
Светлана Яковлева
22.05.2018 09:51:54
Николай, добрый день! А как в вашем примере с адресами получить название улицы. Там где "ул....." понятно, что также как и с городом, а если наоборот Сначала идет название, а потом ул., например вот так: "г. Орехово-Зуево, 798856, Цветочная ул., д. 35  
Ссылка
tsap
06.06.2018 02:54:43
Попробуйте так:
 [^,]+(?= ул\.)
Единственное что придётся убрать дополнительно - пробел в начале названия
Родитель Ссылка
Сергей Евдокимов
03.09.2020 07:34:29
[^,]*ул.
[А-Я а-я]*ул.
Родитель Ссылка
tsap
06.09.2020 15:24:35
Сергей, в Вашем примере также будет захватываться "ул." вместе с наименованием улиц, плюс точка в ReGex говорит о каком-либо произвольном знаке. Если подразумевается именно знак точки, то его необходимо экранировать обратным слешем, иначе регулярные выражения могут понять запрос немного иначе ;-)
Родитель Ссылка
Сергей Евдокимов
09.09.2020 04:25:07
Да. Вы правы. А можете пояснить, что в вашем примере означает "=" (знак равно) ?
И зачем последний пробел? Вроде и без него работает.
Родитель Ссылка
Сергей Евдокимов
09.09.2020 05:17:01
Разобрался. Опережающие и ретроспективные проверки.
Спасибо ))
Родитель Ссылка
Сергей Евдокимов
09.09.2020 05:38:57
А вот такая конструкция решает проблему наличия и первого пробела (т.е устраняя и его):
[^ ]+(?= ул\.)
Родитель Ссылка
Сергей Евдокимов
09.09.2020 05:37:50
<Не знаю как удалить сообщение >
Родитель Ссылка
Денис Михайлов
18.07.2018 16:09:06
Подскажите, как из этого:

Re: ЭДО проблемы_ АЙСБИТ ЗАО (РЦ) YB03412300

Вывести только АЙСБИТ ЗАО ?

Спасибо
Ссылка
Николай Рязанов
27.08.2018 20:37:40
шаблон [^ЖМ]уть найдет Путь или Суть или Забудь, но не Жуть или Муть, например
Николай, опечатка в примере.
Шаблон [^ЖМ]уть никак не найдёт слово Забудь (если только его не написать как "забуть";).
Ссылка
Павел Рубцов
30.08.2018 10:41:28
Добрый день! Просьба помочь с вопросом.
С помощью RegExp и функции подставить получил ячейку со значениями в виде 316;78;230;750;302;100. Как теперь Эти значения можно просуммировать? Если ставлю функцию сумм в начале формулы, то excel выдает ошибку, так как эти числа получаются как текст.
Ссылка
Игорь Островитянин
26.09.2018 10:46:42
Действительно неудобно! Ибо надо работать с массивом значений, а функция RegExpExtract() извлекает только одно значение зараз.
Требуется организация цикла, надо знать общее количество значений...
Предлагаю Вам самостоятельно реализовать другую функцию (на базе упомянутой), назвав ее, например, так: RegExpExtractAll().
Эта функция должна возвращать массив значений (результат поиска).

Как-то так:

Function RegExpExtractAll(Text As String, Pattern As String)
   Dim arr() As String
   Dim re As Object
   Dim matches As Object
   Dim i&
   
   Set re = CreateObject("VBScript.RegExp")
   re.Pattern = Pattern
   If re.Test(Text) Then
       Set matches = re.Execute(Text)
       ReDim arr(matches.Count - 1)
       For i = 0 To UBound(arr)
           arr(i) = matches(i)
       Next
       
       If UBound(arr) = 0 Then
           RegExpExtractAll = arr(0)
       Else
           RegExpExtractAll = arr()
       End if

       Exit Function
   End If

Failed:
   RegExpExtractAll = CVErr(xlErrValue)
End function

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

Введите формулу: =СУММПРОИЗВ(--RegExpExtractAll(Text;"\d+"))
ИЛИ
Введите формулу массива: {=СУММ(RegExpExtractAll(Text;"\d+")*1)}
где Text – это ваша строка "316;78;230;750;302;100" или ссылка на ячейку, содержащую строку данных.


Примечания:
1. Использование двойного унарного оператора (двух знаков минус вместе) приводит к преобразованию текста в число без изменения знака числа.
С таким же успехом вместо этого можно вставить формулу ЦЕЛОЕ(), просто будет длиннее.
Введите формулу: =СУММПРОИЗВ(ЦЕЛОЕ(RegExpExtractAll(Text;"\d+")))
2. Умножение на единицу также приводит к преобразованию текста в число.
3. Формула СУММПРОИЗВ() здесь оперирует только одним множителем (массивом), значения которого преобразуются к численному типу данных, т.е. ничего не перемножается (единственный аргумент), а фактически выполняется только сложение преобразованных значений массива.
Родитель Ссылка
Игорь Островитянин
26.09.2018 13:19:20
Если вы, Павел Рубцов, помимо обработки значений массива (как в вашем случае, см. выше), захотите разместить этот массив на листе, то надо учитывать направление вектора. Это диапазон ячеек в форме вертикального столбца или горизонтальной строки? Если не предпринять никаких действий, то возвращаемый массив будет ориентирован горизонтально. Для заполнения столбца ячеек (Range.Rows.Count > 1) результат надо предварительно транспонировать.

' После присвоения значения функции:
           RegExpExtractAll = arr()
         
' Вставьте следующий блок, ориентирующий массив в зависимости от выбранного вами диапазона ячеек для приема значений:
           With Application
               If TypeName(.Caller) = "Range" Then
                   ' Test if it is being called from a row vector.
                   If .Caller.Rows.Count > 1 Then
                       RegExpExtractAll = .Transpose(arr)
                   End If
               End If
           End With

где Caller в данном случае и есть тот самый многоячеечный диапазон, из которого осуществлен вызов функции RegExpExtractAll(). Очевидно, что формулы могут быть введены заранее, только если вы работаете с каким-то фиксированным массивом, размер которого вам известен.

Примечание. Формула будет одна, вводится она в диапазон ячеек как формула массива (заключается в фигурные скобки).
Ссылка
Павел Рубцов
28.09.2018 12:26:14
Ого! Оказывается не всё так просто как могло бы быть. Спасибо за ответ, попробую так сделать.
Родитель Ссылка
Игорь Островитянин
26.09.2018 15:10:15
VBScript.RegExp содержит известную ошибку, которую в других библиотеках уже исправили.
Попробуйте извлечь:

Text:="two words"
Pattern:="^|\w+"  'Пустая строка в начале строки или слово (лат.)
Item:=2

=RegExpExtract("two words";"^|\w+";2)
Результат: wo
MatchCollection: {"";"wo";"words"}  <-- Обратите внимание на пропущенную букву t
Ссылка
Евгений Леденев
28.09.2018 09:22:47
1/2/3/выв
мне нужно получить 1/2/3
регуляркой (.*)/ получаю 1/2/3/
хотя по идее должен отдавать то что в скобках, то есть 1/2/3
Можно как-то получать то что в скобках через $1 $2  и т.п.?
Ссылка
Игорь Островитянин
29.09.2018 19:10:14
То же самое:
(.*)\/
Зд. я экранировал слэш (в Excel не обязательно, хотя это хорошая практика),
regex101 показывает, что всё решается за 13 шагов (42 ms).
Match 1
Full match0-6`1/2/3/`
Group 1.n/a`1/2/3`
Ссылка на группу выполняется по ее порядковому номеру: $1
И выше, и у вас всё правильно! То, что в скобках отдает при ссылке по $1, а полный результат включает слэш, т.к. он присутствует в вашем шаблоне.
Для анализа выражения использован ресурс www.regex101.com
Родитель Ссылка
Евгений Леденев
29.09.2018 20:45:39
>Ссылка на группу выполняется по ее порядковому номеру: $1
Проблема в том, что через эту функцию(для экселя) не могу получить значение группы. Только полное соответствие Full match.

А последний параметр функции позволяет выбрать какой из Full mutch брать, например:

Данные: 1/2/3/выв

регулярка: \/

Match 1
Full match 1-2 `/`
Match 2
Full match 3-4 `/`
Match 3
Full match 5-6 `/`

Хочется возможность выбора группы в функции.
Родитель Ссылка
Игорь Островитянин
29.09.2018 21:30:39
Мы, вообще, о чем?..
Последний параметр позволяет выбрать один из результатов поиска -- это первый уровень иерархии (Match Object). Группы, только если они выделены, вложены в каждый из таких результатов -- второй уровень иерархии (Matches Collection). У вас все "мэчи" полные (full match), потому что они другими не бывают. Но даже если выделить группу, используя круглые скобки (происходит захват), то всё равно вы не сможете к ней обратиться. В самом шаблоне сослаться на группу вы однако можете через синтаксис \1 (именно так: через бэкслэш), но это не то, что вам надо. К вашей задаче это не имеет никакого отношения.

Результатов поиска может быть много, как слов в предложении. Но можно разбить каждое слово, например на буквы, и тогда в каждом слове-поиске будет по несколько выделенных букв-групп. Как-то так. Но зачем вам это? Автор одной функцией вон сколько задач решил!

Попробуйте вот такой шаблон (regex101 не находит ничего, но VBScript справляется):

^.*(?=/[^/]*)$

Пояснение. В пределах строки от начала ^ до конца $ найти последовательность любых символов .* перед набором символов, состоящим из одного слэша / и последовательности, наоборот, из любых символов, кроме слэша [^/]*. Всё, что в круглых скобках есть группа (look ahead -- вперед смотрящий), читайте руководство.


Функция автора RegExpExtract() не дает возможности ссылаться на группы. То, что в круглых скобках в шаблоне, есть действительно группа, но возможность работать с такой группой предоставляет метод RegExp.Replace(), который в функции автора не используется.

Реализуйте свою функцию RegExpReplace(Text, Pattern, Replace), в тело которой вставьте метод RegExp.Replace(Text, Replace).
Функция будет не длиннее, чем первая. Всё просто.
Text -- это исходный текст,
Replace -- строка (с использованием шаблонов ссылок типа $1), которой надо заменить в исходной строке Text результат поиска по шаблону Pattern.
Для того, чтобы было удобно производить замены, желательно найти всю строку целиком и выделить в ней требуемые группы.
В наше случае вот так:
(.*)/.*
Match 1
Full match0-9`1/2/3/выв`
Group 1.n/a`1/2/3`
А теперь, чтобы манипулировать частями строки, надо вызывать с заданными аргументами новою функцию. Скажем, нужно добавить знак номера к значению группы 1, тогда это будет выглядеть так:
RegExpReplace("1/2/3/выв";"(.*)/.*";"№$1")
Возвращаемый результат будет таким:
№1/2/3

Но если вы хотите заменить подстроку или, еще лучше, удалить ее совсем в исходном тексте, то тогда так:
RegExpReplace("1/2/3/выв";"(.*)/";"")
Возвращаемый результат будет таким (результатом поиска по шаблону будет "1/2/3/"):
выв

Сослаться на группу при манипуляции со строками (не в шаблоне) вы можете через синтаксис $1 (именно так: через знак доллара).
Если я вас правильно понял.

Если вам нужны не манипуляции с подстроками путем замены, то следуйте правилу:
пишите шаблоны (рег. выражение) так жестко, чтобы находилось (извлекалось) только что-то одно или же делайте несколько вызовов по более общему шаблону, но указывайте, какой результат по порядку (параметр Item) надо вернуть. Результаты самостоятельных вызовов функции объединяйте, обрабатывайте и пр. По большому счету, можно оперировать результатами поиска как вашими группами (только без ссылок типа $).
Родитель Ссылка
Игорь Островитянин
29.09.2018 23:08:50
Моя ошибка (знак доллара надо включить в группу, и всё везде работает):

^.*(?=/[^/]*$)

Интересный прием выше хорошо описан на англ. языке по ссылке ниже (см. раздел "Condition 2"):
www.rexegg.com/regex-lookarounds.html
Шаблон
/[^/]*
является примером принципа контраста, рекомендованного руководством по регулярным выражениям (contrast).
Родитель Ссылка
Сергей Багузин
02.09.2021 15:32:20
Добрый день. Игорь, а можете дать пример кода со ссылкой на группу типа $1, $2?
У меня редактор VBA ругается на $1...
Родитель Ссылка
Алексей Потапов
03.10.2018 21:32:25
Возникает ошибка #ЗНАЧ
Ссылка
Игорь Островитянин
04.10.2018 00:14:51
Да нет, всё работает:

=RegExpExtract("1/2/3/выв";"^.*(?=/[^/]*$)")
Возвращает: 1/2/3
Родитель Ссылка
Артем
14.10.2018 13:07:11
Николай, я уже видел эту статью на страницах ВК, вы ее просто скопировали?;)
Ссылка
Николай Павлов
15.10.2018 08:26:31
Шутите, я надеюсь? :)
На этом сайте нет ни одной чужой статьи.
Осмелюсь предположить, что скопировали у меня - это часто бывает и не всегда с указанием авторства, к сожалению.
Родитель Ссылка
Артем
19.10.2018 01:02:02
Извините, просто она первая попалась на глаза.
Пробежал по контенту, а там тупо все посты с Вашего сайта. Олень какой-то.
Еще раз приношу глубочайшие.
А за статью спасибо огромное!
Родитель Ссылка
Николай Павлов
19.10.2018 10:39:29
Бросьте, Артем - не за что тут извиняться. Таких "оленей" в интернетах полно.

И спасибо на добром слове :)
Родитель Ссылка
Артем
19.10.2018 17:58:22
вот главный похоже https://vk.com/akademia_excel
Родитель Ссылка
Сергей Корулин
18.10.2018 00:25:40
Здравствуйте!
У меня вопрос, похоже в тему. Прошу совет, как реализовать.

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

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

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

Реализуемо ли? и если да. то как? опять же учитывая, что люди пишут номера кто как.

Спасибо!
Ссылка
Евгений
26.04.2019 09:48:15
В тексте статьи есть пример для извлечения номеров телефонов ;)
Родитель Ссылка
Виктор
24.10.2018 11:36:25
Здравствуйте Уважаемые, помогите разобраться 

Function Reg$(t$)
         Dim i&
 With CreateObject("VBScript.RegExp"): .Global = True: .Pattern = "[#N№мтуч,\.](\s\d{3,4}|\d{3,4})(?=\D)"
    For i = 0 To .Execute(t).Count - 1: Reg= Reg+ "," + .Execute(t)(i).Submatches(0): Next
       Reg= Mid(Reg, 2)   
End With 
End Function
Воссоздал этакий пример всех ошибок и проблем, в котором надо вытащить счета с разным мусором:

"КОМПАНИЯ ООО / В том числе НДС 18%, 9599.00 руб. №1111 №2222, 3333 от 11.11.2018 N4444 N 5555,6666 сч7777 от 11.11.2018 сч 8888 сч.9999 сч. 1010 счетам 1100 и счету 1200 НДС 18%, 99999.99 руб"

Имея такую регулярку [#N№мтуч\.,](\s\d{3,4}|\d{3,4})(?=\D) мне выдает следующее:
"9599,1111,2222, 3333,2018,4444, 5555,6666,7777,2018, 8888,9999, 1010, 1100, 1200"
Вопрос если мне нужно захватывать такие вот выражения "сч.9999 сч. 1010" с точкой, как избежать попадания дат и сумм? если я уберу из регулярки "\." то меня спасает от даты и сумм "(?=\D)", но тогда под маску не попадают нужные мне данные.
Ссылка
Василий Анохин
26.11.2018 16:58:29
Здравствуйте! Очень крутая и легко воспринимающаяся статья! Но мне не очень понятно, как действовать, если надо вытащить несколько значений из строки.
Составил список с названиями файлов,

369 1234  RP - 2001.02.11 15-55 (2500).xlsx   из этой ячейки мне надо вытащить 1234 и (2500).

Из примера понятно что 1234 можно взять этим способом \d{4}. А как соединить 1234 и (2500) ???
Ссылка
Василий Анохин
26.11.2018 16:58:58
Здравствуйте! Очень крутая и легко воспринимающаяся статья! Но мне не очень понятно, как действовать, если надо вытащить несколько значений из строки.
Составил список с названиями файлов,

369 1234  RP - 2001.02.11 15-55 (2500).xlsx   из этой ячейки мне надо вытащить 1234 и (2500).

Из примера понятно что 1234 можно взять этим способом \d{4}. А как соединить 1234 и (2500) ???
Ссылка
Игорь Островитянин
10.02.2019 13:05:39
Вызовите функцию RegExpExtract() дважды с разными паттернами:
=RegExpExtract(Text;"\d{4}") & " " & RegExpExtract(Text;"\(\d{4}\)").


Примечание. Вы можете объединить паттерн и вызывать функцию с указанием, какой элемент извлечь:
Pattern = "\d{4}|\(\d{4}\)" <- Так будет удобнее редактировать паттерн (при необходимости)
=RegExpExtract(Text;Pattern;1) & " " & RegExpExtract(Text;Pattern;2)

Но: всё работает при условии, что первой части критерия ("\d{4}") отвечает один результат в исходном тексте.
Не тестировал.
Родитель Ссылка
vlp810@mail.ru
31.01.2019 15:47:27
Николай, добрый день! спасибо большое за функцию, есть вопрос...

попытался в лоб использовать ее в своем макросе, но отладчик ругается, если задаю параметры переменными... Compile error: ByRef argument type mismatch!  переменные строковые, объявленные...можно тут что-то сделать? Спасибо
Ссылка
Татьяна Львова
12.04.2019 10:57:04
Добрый день, подскажите как вытащить номер договора (цифры). К каждому задать разные параметры?

(Договор № 5000/316 от 01.11.2016 г.) 5000/316
2413 от 01.08.2007
от 01.08.2013 № 9
Ссылка
Игорь Островитянин
02.05.2019 17:21:07
Как-то так...

RegExp шаблон: \d+/?\d*(?= от)|\d+$
\d+/?\d*(?= от)   это цифры (\d+), после которых может идти символ "/", а может и не идти (/?),
                      далее могут быть ещё цифры, а могут и не быть (\d*) -
                      и всё это перед символами " от" (случай 1 и 2)
\d+$              это цифры в конце строки (случай 3)

=RegExpExtract(Text;"\d+/?\d*(?= от)|\d+$")

P.S. При слабой стандартизации входных данных будет работать всё равно не надёжно. Но для приведенных Вами примеров подойдёт. Очень жёстко!

RegExp шаблон: \d+/?\d*(?= *от)|\d+ *$   <-- Здесь добавлена звёздочка после пробела перед "от)" и звёздочка с пробелом в конце строки перед "$", это делает схему более "мягкой" при наличии "лишних" пробелов в тексте.
Родитель Ссылка
Сергей Матвеев
03.06.2019 10:40:00
Добрый день!
Павел Рубцов 30.08.2018 10:41:28
Добрый день! Просьба помочь с вопросом.
С помощью RegExp и функции подставить получил ячейку со значениями в виде 316;78;230;750;302;100. Как теперь Эти значения можно просуммировать? Если ставлю функцию сумм в начале формулы, то excel выдает ошибку, так как эти числа получаются как текст.
Можно вот так:
В этом варианте нужно вводить маску в ячейку
    Public Function RegExpSum(Text As String, Pattern As String, Optional Item As Integer = 1) As String
    
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = Pattern
    regex.Global = True
    If regex.Test(Text) Then
        Set matches = regex.Execute(Text)
        zzz = 0
            For i = 0 To matches.Count - 1
                Set Match = matches.Item(i)
                zzz = zzz + Match.Value
            Next
        RegExpSum = zzz
        Exit Function
    End If
ErrHandl:
    RegExpSum = CVErr(xlErrValue)
End Function


В этом варианте маска по умолчанию  "\d+"
  Public Function RegExpSum1(Text As String, Optional Item As Integer = 1) As String
    
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\d+"
    regex.Global = True
    If regex.Test(Text) Then
        Set matches = regex.Execute(Text)
        zzz = 0
            For i = 0 To matches.Count - 1
                Set Match = matches.Item(i)
                zzz = zzz + Match.Value
            Next
        RegExpSum1 = zzz
        Exit Function
    End If
ErrHandl:
    RegExpSum1 = CVErr(xlErrValue)
End Function
Может кому-то ещё пригодится.
Ссылка
Сергей Матвеев
03.06.2019 11:09:18
А если во втором примере маску "\d+" заменить на "\d+\,\d+|\d+", то будет понимать десятичные дроби
Ссылка
Сергей Матвеев
04.06.2019 10:43:51
Дело было вечером, делать было нечего
Немного ещё поковырялся и вот, что получилось

'''''''''''''https://www.planetaexcel.ru/techniques/7/4844/

Public Function RegExpSum(Text As String, Optional Pattern As String, Optional Razdel As String, Optional Deistvie As Integer) As String
    
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = Pattern
    regex.Global = True
    
    If Razdel = "" Then
        Razdel = "; "
    ElseIf IsNumeric(Razdel) = True Then
        Razdel = " " & Razdel & " "
    End If
    If Pattern = "" Then
        regex.Pattern = "\d+\,\d+|\d+"
    End If
    
    If regex.Test(Text) Then
        Set matches = regex.Execute(Text)
'        zzz = 0
            For i = 0 To matches.Count - 1
                Set Match = matches.Item(i)
                zzz = zzz & Razdel & Match.Value
            Next
    zzz = Right(zzz, Len(zzz) - Len(Razdel))
    
    If Deistvie = 1 Then
    a = Split(zzz, Razdel)
    zzz = 0
    ccc = ""
    
    For i = LBound(a) To UBound(a)
        If IsNumeric(a(i)) = True Then
            zzz = zzz + a(i)
        Else
            ccc = ccc & Razdel & a(i)
        End If
    Next i
        If ccc <> "" Then
            zzz = zzz & ccc
        Else
            zzz = zzz
        End If
    End If
        RegExpSum = zzz
        
'        RegExpSum1 = Right(zzz, Len(zzz) - 2)
        Exit Function
    End If
ErrHandl:
    RegExpSum = CVErr(xlErrValue)
End Function
Теперь если условие (Pattern) опущено, то по умолчанию выбирает по маске: "\d+\,\d+|\d+" (все числовые значения).
Если разделитель (Razdel) опущен то "; ". Если разделитель числовое значение, то в начале и конце добавляется пробел.
Если Deistvie = 1 то суммирует все числовые значения, что не распознал как число дописывает в конце через разделитель.
Если Deistvie <> 1 или опущен то собирает все значения через разделитель.

Сохранил как ".xlam" с пояснениями к аргументам, но к сожалению здесь нельзя его выложить
Ссылка
Страницы: 1  2  
Наверх