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

Една от най-отнемащите време и разочароващи задачи при работа с текст в Excel е морфологичен разбор – анализиране на буквено-цифровата „каша“ на компоненти и извличане на необходимите фрагменти от нея. Например:

• извличане на пощенския код от адреса (добре е пощенският код винаги да е в началото, но какво ще стане, ако не е?)
• намиране на номера и датата на фактурата от описанието на плащането в банковото извлечение
• извличане на TIN от пъстри описания на фирми в списъка на контрагентите
• търсене на номер на кола или номер на артикул в описанието и др.
  

Обикновено в такива случаи, след половин час мрачно ръчно ръчкане в текста, започват да идват на ум някак си мисли за автоматизиране на този процес (особено ако има много данни). Има няколко решения с различна степен на сложност-ефективност:

• употреба вградени текстови функции на Excel за търсене-изрязване-лепене на текст: ЛЕВСИМВ (НАЛЯВО), НАДЯСНО (НАДЯСНО), PSTR (среда), СЦЕПИТ (СЪЕДИНЯВАНЕ) и неговите аналози, COMBINE (СЪВМЕСТЕН ​​ТЕКСТ), ТОЧНО (ТОЧНО) и т.н. Този метод е добър, ако има ясна логика в текста (например индексът винаги е в началото на адреса). В противен случай формулите стават много по-сложни и понякога дори се стига до формули за масиви, което значително забавя работата при големи маси.
• Използването на като оператор за сходство на текст от Visual Basic, обвит в персонализирана макро функция. Това ви позволява да приложите по-гъвкаво търсене с помощта на заместващи знаци (*, #,? и т.н.). За съжаление този инструмент не може да извлече желания подниз от текста – проверете само дали се съдържа в него.

В допълнение към горното, има още един подход, който е много добре познат в тесните кръгове от професионални програмисти, уеб разработчици и други технари – това е редовни изрази (Регулярни изрази = RegExp = „regexps“ = „regulars“). Просто казано, RegExp е език, в който се използват специални символи и правила за търсене на необходимите поднизове в текста, извличането им или замяната им с друг текст. Регулярните изрази са много мощен и красив инструмент, който превъзхожда всички други начини за работа с текст с порядък. Много езици за програмиране (C#, PHP, Perl, JavaScript…) и текстови редактори (Word, Notepad++…) поддържат регулярни изрази.

Microsoft Excel, за съжаление, няма поддръжка на RegExp, но това може лесно да се коригира с VBA. Отворете редактора на Visual Basic от раздела предприемач (Разработчик) или клавишна комбинация Друг+F11. След това поставете новия модул през менюто Вмъкване – Модул и копирайте текста на следната макро функция там:

Публична функция 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 (Текст) След това Задайте съвпадения = regex.Execute(Текст) RegExpExtract = matches.Item(Елемент - 1) Изход от функцията Край Ако ErrHandl: RegExpExtract = CVErr(xlErrValue) Край на функцията  

Вече можем да затворим редактора на Visual Basic и да се върнем към Excel, за да изпробваме нашата нова функция. Синтаксисът му е следният:

=RegExpExtract(Txt; Шаблон; Елемент)

където

• TXT – клетка с текста, който проверяваме и от който искаме да извлечем необходимия ни подниз
• модел – маска (модел) за търсене на подниз
• Точка – поредният номер на подниза, който трябва да бъде извлечен, ако има няколко от тях (ако не е посочен, тогава се показва първото срещане)

Най-интересното тук, разбира се, е Pattern – шаблонен низ от специални символи „на езика“ на RegExp, който указва какво точно и къде искаме да намерим. Ето най-основните, за да започнете:

Ако търсим определен брой знаци, например шестцифрен пощенски код или всички трибуквени продуктови кодове, тогава идваме на помощ квантори or квантори са специални изрази, които указват броя знаци за търсене. Кванторите се прилагат към знака, който идва преди него:

Сега да преминем към най-интересната част – анализ на приложението на създадената функция и какво научихме за моделите на практически примери от живота.

Извличане на числа от текст

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

Логиката зад регулярния израз е проста: d означава всяка цифра и кванторът + казва, че техният брой трябва да бъде един или повече. Двойният минус пред функцията е необходим за „в движение“ преобразуване на извлечените символи в пълно число от числото като текст.

Пощенски код

На пръв поглед тук всичко е просто – търсим точно шест цифри подред. Използваме специален знак d за цифра и квантор 6 {} за броя знаци:

Въпреки това е възможна ситуация, когато вляво от индекса в реда има друг голям набор от числа в ред (телефонен номер, TIN, банкова сметка и т.н.). Тогава нашият редовен сезон ще извади първите 6 цифри от него, т.е. няма да работи правилно:

За да предотвратим това да се случи, трябва да добавим модификатор около краищата на нашия регулярен израз b означаващ края на думата. Това ще изясни на Excel, че фрагментът (индексът), от който се нуждаем, трябва да бъде отделна дума, а не част от друг фрагмент (телефонен номер):

Телефон

Проблемът с намирането на телефонен номер в текста е, че има толкова много възможности за изписване на числата – с и без тирета, през интервали, с или без регионален код в скоби и т.н. Затова според мен е по-лесно първо изчистете всички тези знаци от изходния текст, като използвате няколко вложени функции ЗАМЕСТИТЕЛ (ЗАМЕСТВАНЕ)така че да се слепи в едно цяло, а след това с примитивен редовен d{11} извадете 11 цифри подред:

ИТН

Тук е малко по-сложно, защото TIN (у нас) може да бъде 10-цифрен (за юридически лица) или 12-цифрен (за физически лица). Ако не намирате вина специално, тогава е напълно възможно да сте доволни от редовното d{10,12}, но, строго погледнато, ще извади всички числа от 10 до 12 знака, т.е. и погрешно въведени 11 цифри. Би било по-правилно да се използват два модела, свързани с логически оператор ИЛИ | (вертикална лента):

Моля, обърнете внимание, че в заявката първо търсим 12-битови числа и едва след това 10-битови числа. Ако напишем нашия регулярен израз обратното, тогава той ще извади за всички, дори за дълги 12-битови TIN, само първите 10 знака. Тоест, след като първото условие е задействано, допълнителна проверка вече не се извършва:

Това е основната разлика между оператора | от стандартна логическа функция на Excel OR (OR), където пренареждането на аргументите не променя резултата.

Каталожен номер на продукта

В много компании уникалните идентификатори се присвояват на стоки и услуги – артикули, SAP кодове, SKU и т.н. Ако има логика в тяхното обозначение, те могат лесно да бъдат извадени от всеки текст с помощта на регулярни изрази. Например, ако знаем, че нашите статии винаги се състоят от три главни английски букви, тире и последващо трицифрено число, тогава:

Логиката зад шаблона е проста. [AZ] – означава всички главни букви от латинската азбука. Следващият квантификатор 3 {} казва, че за нас е важно да има точно три такива букви. След тирето чакаме три цифри, затова добавяме накрая d{3}

Парични суми

По начин, подобен на предишния параграф, можете също да извадите цени (разходи, ДДС ...) от описанието на стоките. Ако паричните суми, например, са посочени с тире, тогава:

Модел d с квантор + търси произволно число до тире и d{2} ще търси стотинки (две цифри) след това.

Ако трябва да извлечете не цени, а ДДС, тогава можете да използвате третия незадължителен аргумент на нашата функция RegExpExtract, който указва поредния номер на елемента, който трябва да бъде извлечен. И, разбира се, можете да замените функцията ЗАМЕСТИТЕЛ (ЗАМЕСТВАНЕ) в резултатите поставете тире към стандартния десетичен разделител и добавете двоен минус в началото, така че Excel да интерпретира намереното ДДС като нормално число:

Номера на автомобили

Ако не вземете специални превозни средства, ремаркета и други мотоциклети, тогава стандартният номер на автомобила се анализира според принципа „буква – три цифри – две букви – код на региона“. Освен това кодът на региона може да бъде 2- или 3-цифрен и само тези, които на външен вид са подобни на латинската азбука, се използват като букви. Така следният регулярен израз ще ни помогне да извлечем числа от текста:

Време

За да извлечете времето във формат HH:MM, е подходящ следният регулярен израз:

След фрагмент на дебелото черво [0-5]d, както е лесно да разберете, задава произволно число в диапазона 00-59. Преди двоеточието в скоби работят два модела, разделени с логическо ИЛИ (тръбна черта):

• [0-1]d – всяко число в диапазона 00-19
• 2[0-3] – всяко число в диапазона 20-23

Към получения резултат можете допълнително да приложите стандартната функция на Excel ВРЕМЕ (ЕКИП)за да го конвертирате във формат на времето, който е разбираем за програмата и е подходящ за по-нататъшни изчисления.

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

Да предположим, че трябва да проверим правилността на списъка с пароли, измислени от потребителите. Според нашите правила паролите могат да съдържат само английски букви (малки или главни) и цифри. Не се допускат интервали, долна черта и други препинателни знаци.

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

Всъщност с такъв модел изискваме между началото (^) и край ($) в нашия текст имаше само символи от набора, даден в квадратни скоби. Ако трябва да проверите и дължината на паролата (например поне 6 знака), тогава кванторът + може да се замени с интервала „шест или повече“ във формуляра {6,}:

Град от адрес

Да кажем, че трябва да изтеглим града от адресната лента. Редовната програма ще помогне, извличайки текста от "g." до следващата запетая:

Нека разгледаме по-отблизо този модел.

Ако сте прочели текста по-горе, значи вече сте разбрали, че някои знаци в регулярните изрази (точка, звездички, знаци за долар и т.н.) имат специално значение. Ако трябва да потърсите самите тези знаци, тогава те се предхождат от обратна наклонена черта (понякога наричана екраниране). Следователно, когато търсите фрагмента "g." трябва да пишем в регулярен израз Г-н. ако търсим плюс, тогава + и т.н.

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

В края на шаблона има запетая, защото търсим текст от „g.“ до запетая. Но в текста може да има няколко запетаи, нали? Не само след града, но и след улицата, къщите и пр. На кое от тях ще спре искането ни? За това е въпросителният знак. Без него нашият регулярен израз би извадил възможно най-дългия низ:

По отношение на регулярните изрази, такъв модел е „алчен“. За да коригираме ситуацията, е необходим въпросителен знак – той прави квантификатора, след който стои „скъперник“ – и нашата заявка взема текста само до първата контра запетая след „g.“:

Име на файл от пълния път

Друга много често срещана ситуация е да извлечете името на файла от пълния път. Прост регулярен израз на формуляра ще помогне тук:

Номерът тук е, че всъщност търсенето става в обратната посока – от края към началото, защото в края на нашия шаблон е $, и търсим всичко преди него до първата обратна наклонена черта отдясно. Обратната наклонена черта се екранира, както точката в предишния пример.

PS

„Към края“ искам да поясня, че всичко по-горе е малка част от всички възможности, които предоставят регулярните изрази. Има много специални символи и правила за тяхното използване, а по тази тема са написани цели книги (препоръчвам поне тази за начало). В известен смисъл писането на регулярни изрази е почти изкуство. Почти винаги изобретеният регулярен израз може да бъде подобрен или допълнен, което го прави по-елегантен или способен да работи с по-широк набор от входни данни.

Има няколко удобни онлайн услуги, за да анализирате и анализирате регулярните изрази на други хора или да отстранявате грешки в собствените си: RegEx101, RegExr и още

За съжаление, не всички функции на класическите регулярни изрази се поддържат във VBA (например обратно търсене или POSIX класове) и могат да работят с кирилица, но мисля, че това, което е там, е достатъчно за първи път, за да ви зарадва.

Ако не сте нов в темата и имате какво да споделите, оставете полезни регулярни изрази при работа в Excel в коментарите по-долу. Едно наум е добре, но две ботуши са чифт!

• Замяна и почистване на текст с функцията ЗАМЕНЯНЕ
• Търсене и маркиране на латински букви в текст
• Търсете най-близкия подобен текст (Иванов = Ивонов = Иваноф и т.н.)