Analyzujte text pomocí regulárních výrazů (RegExp) v Excelu

Jedním z časově nejnáročnějších a nejvíce frustrujících úkolů při práci s textem v Excelu je parsování – rozebrat alfanumerickou „kaši“ na komponenty a extrahovat z ní fragmenty, které potřebujeme. Například:

• extrahování PSČ z adresy (je dobré, když je PSČ vždy na začátku, ale co když není?)
• zjištění čísla a data faktury z popisu platby ve výpisu z účtu
• extrahování TIN z pestrých popisů společností v seznamu protistran
• vyhledejte v popisu číslo vozu nebo číslo artiklu atd.
  

Obvykle se v takových případech po půl hodině bezútěšného ručního vybírání textu začnou vynořovat myšlenky, jak tento proces zautomatizovat (zvláště pokud je tam hodně dat). Existuje několik řešení a s různou mírou složitosti a účinnosti:

• Použijte vestavěné textové funkce Excelu k vyhledání textu nařezaného lepidla: LEVSIMV (VLEVO, ODJET), PRÁVO (ŽE JO), PSTR (střední), STsEPIT (CONCATENATE) a jeho analogy, KOMBAJN (JOINTEXT), PŘESNÝ (PŘESNÝ) atd. Tento způsob je dobrý, pokud je v textu jasná logika (např. index je vždy na začátku adresy). Jinak se vzorce značně zkomplikují a někdy dojde i na maticové vzorce, což u velkých tabulek značně zpomaluje.
• Použití jako operátor podobnosti textu z jazyka Visual Basic zabalený do funkce vlastního makra. To vám umožní implementovat flexibilnější vyhledávání pomocí zástupných znaků (*, #,?, atd.). Bohužel tento nástroj nedokáže extrahovat požadovaný podřetězec z textu – pouze zkontrolujte, zda je v něm obsažen.

Kromě výše uvedeného existuje ještě jeden přístup, který je velmi dobře známý v úzkých kruzích profesionálních programátorů, webových vývojářů a dalších techniků – to je regulární výrazy (Regulární výrazy = RegExp = „regexps“ = „regulars“). Jednoduše řečeno, RegExp je jazyk, kde se používají speciální znaky a pravidla k hledání potřebných podřetězců v textu, jejich extrahování nebo nahrazení jiným textem.. Regulární výrazy jsou velmi mocným a krásným nástrojem, který řádově předčí všechny ostatní způsoby práce s textem. Mnoho programovacích jazyků (C#, PHP, Perl, JavaScript…) a textových editorů (Word, Notepad++…) podporuje regulární výrazy.

Microsoft Excel bohužel nemá podporu RegExp přímo z krabice, ale to lze snadno opravit pomocí VBA. Otevřete Editor jazyka Visual Basic z karty vývojka (Vývojář) nebo klávesovou zkratkou Další+F11. Poté pomocí nabídky vložte nový modul Vložit – Modul a zkopírujte tam text následující funkce makra:

Veřejná funkce RegExpExtract(Text As String, Pattern As String, Volitelná položka As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Vzor regex.Global = True If regex.Test (Text) Potom Set shody = regex.Execute(Text) RegExpExtract = shody.Item(Položka - 1) Ukončení funkce End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Nyní můžeme zavřít editor jazyka Visual Basic a vrátit se do Excelu a vyzkoušet naši novou funkci. Jeho syntaxe je následující:

=RegExpExtract( Txt ; Vzor ; Položka )

kde

• txt – buňka s textem, který kontrolujeme a ze kterého chceme extrahovat potřebný podřetězec
• vzor – maska ​​(vzor) pro vyhledávání podřetězců
• Položka – pořadové číslo podřetězce, který má být extrahován, pokud jich je několik (pokud není uvedeno, zobrazí se první výskyt)

Nejzajímavější je zde samozřejmě Pattern – šablonový řetězec speciálních znaků „v jazyce“ RegExpu, který specifikuje, co přesně a kde chceme najít. Zde jsou ty nejzákladnější, jak začít:

Pokud hledáme určitý počet znaků, například šestimístné PSČ nebo všechny třípísmenné kódy produktů, přicházíme na pomoc kvantifikátory or kvantifikátory jsou speciální výrazy, které určují počet znaků, které mají být prohledány. Kvantifikátory jsou aplikovány na znak, který je před ním:

Nyní přejděme k tomu nejzajímavějšímu – rozboru aplikace vytvořené funkce a toho, co jsme se o vzorcích dozvěděli na praktických příkladech ze života.

Extrahování čísel z textu

Pro začátek si rozeberme jednoduchý případ – z alfanumerické kaše potřebujete vydolovat první číslo, například výkon nepřerušitelných zdrojů z ceníku:

Logika regulárního výrazu je jednoduchá: d znamená libovolnou číslici a kvantifikátor + říká, že jejich počet by měl být jeden nebo více. Dvojité mínus před funkcí je potřeba k „za běhu“ převodu extrahovaných znaků na celé číslo z čísla jako textu.

PSČ

Na první pohled je zde vše jednoduché – hledáme přesně šest číslic za sebou. Používáme speciální znak d pro číslici a kvantifikátor 6 {} pro počet znaků:

Je však možná situace, kdy nalevo od indexu v řádku je další velká sada čísel v řadě (telefonní číslo, DIČ, bankovní účet atd.) Pak naše pravidelná sezóna vytáhne prvních 6 číslice z něj, tj. nebude fungovat správně:

Abychom tomu zabránili, musíme kolem okrajů našeho regulárního výrazu přidat modifikátor b označující konec slova. Tím bude Excelu jasné, že fragment (index), který potřebujeme, by měl být samostatným slovem a ne součástí jiného fragmentu (telefonního čísla):

Telefon

Problém s hledáním telefonního čísla v textu je v tom, že je tolik možností, jak čísla psát – se spojovníky i bez, přes mezery, s kódem regionu nebo bez něj v závorkách atd. Proto je podle mého názoru jednodušší nejprve vyčistěte všechny tyto znaky ze zdrojového textu pomocí několika vnořených funkcí NÁHRADNÍ (NÁHRADNÍ)tak, aby se to spojilo do jediného celku, a pak s primitivním pravidelným d{11} vytáhněte 11 číslic za sebou:

ITN

Zde je to trochu složitější, protože DIČ (u nás) může být 10místné (u právnických osob) nebo 12místné (u fyzických osob). Pokud nenajdete chybu, pak je docela možné se spokojit s běžným d{10,12}, ale přísně vzato, vytáhne všechna čísla od 10 do 12 znaků, tedy a chybně zadaných 11 číslic. Správnější by bylo použít dva vzory spojené logickým operátorem OR | (svislý pruh):

Upozorňujeme, že v dotazu nejprve hledáme 12bitová čísla a teprve potom 10bitová čísla. Pokud náš regulární výraz napíšeme opačně, pak vytáhne každému, i dlouhým 12bitovým TINům, pouze prvních 10 znaků. To znamená, že po spuštění první podmínky se již další ověřování neprovádí:

To je zásadní rozdíl mezi operátorem | ze standardní logické funkce Excelu OR (OR), kde přeuspořádání argumentů nezmění výsledek.

SKU produktů

V mnoha firmách jsou zboží a službám přidělovány jedinečné identifikátory – články, SAP kódy, SKU atd. Pokud je v jejich zápisu logika, pak je lze snadno vytáhnout z libovolného textu pomocí regulárních výrazů. Pokud například víme, že naše články se vždy skládají ze tří velkých anglických písmen, pomlčky a následného třímístného čísla, pak:

Logika šablony je jednoduchá. [AZ] – znamená jakákoli velká písmena latinské abecedy. Další kvantifikátor 3 {} říká, že je pro nás důležité, aby taková písmena byla přesně tři. Za pomlčkou čekáme na tři číslice, proto přidáváme na konec d{3}

Částky v hotovosti

Obdobně jako v předchozím odstavci můžete také vytáhnout ceny (náklady, DPH …) z popisu zboží. Pokud jsou například peněžní částky označeny pomlčkou, pak:

Vzor d s kvantifikátorem + vyhledá libovolné číslo až do pomlčky a d{2} bude hledat haléře (dvě číslice).

Pokud potřebujete extrahovat nikoli ceny, ale DPH, můžete použít třetí volitelný argument naší funkce RegExpExtract, který udává pořadové číslo prvku, který má být extrahován. A samozřejmě můžete funkci nahradit NÁHRADNÍ (NÁHRADNÍ) ve výsledcích zadejte pomlčku na standardní oddělovač desetinných míst a na začátek přidejte dvojité mínus, aby Excel interpretoval nalezenou DPH jako normální číslo:

SPZ aut

Pokud neberete speciální vozidla, přívěsy a jiné motocykly, pak se standardní číslo vozu analyzuje podle principu „písmeno – tři čísla – dvě písmena – kód regionu“. Kromě toho může být kód regionu 2- nebo 3-místný a jako písmena se používají pouze ta, která mají podobný vzhled jako latinská abeceda. Následující regulární výraz nám tedy pomůže extrahovat čísla z textu:

Čas

Pro extrakci času ve formátu HH:MM je vhodný následující regulární výraz:

Po fragmentu tlustého střeva [0-5]d, jak je snadné zjistit, nastaví libovolné číslo v rozsahu 00-59. Před dvojtečkou v závorce fungují dva vzory oddělené logickým OR (svislou čarou):

• [0-1]d – libovolné číslo v rozsahu 00-19
• 2[0-3] – libovolné číslo v rozsahu 20-23

Na získaný výsledek můžete dodatečně použít standardní funkci Excel TIME (TÝM)převést jej do formátu času, který je pro program srozumitelný a vhodný pro další výpočty.

Kontrola hesla

Předpokládejme, že potřebujeme zkontrolovat správnost seznamu hesel vymyšlených uživateli. Podle našich pravidel mohou hesla obsahovat pouze anglická písmena (malá nebo velká) a čísla. Mezery, podtržítka a jiná interpunkční znaménka nejsou povoleny.

Kontrola může být organizována pomocí následujícího jednoduchého regulárního výrazu:

Ve skutečnosti u takového vzoru vyžadujeme, aby mezi začátkem (^) a konec ($) v našem textu byly pouze znaky z množiny uvedené v hranatých závorkách. Pokud potřebujete zkontrolovat i délku hesla (například alespoň 6 znaků), pak kvantifikátor + lze ve formuláři nahradit intervalem „šest a více“. {6 XNUMX XNUMX,}:

Město z adresy

Řekněme, že potřebujeme stáhnout město z adresního řádku. Pomůže běžný program, který extrahuje text z „g“. na další čárku:

Pojďme se na tento vzor podívat blíže.

Pokud jste četli výše uvedený text, pak jste již pochopili, že některé znaky v regulárních výrazech (tečky, hvězdičky, znaky dolaru atd.) mají zvláštní význam. Pokud potřebujete hledat tyto znaky samotné, pak jim předchází zpětné lomítko (někdy tzv stínění). Proto při hledání fragmentu "g." musíme psát regulárním výrazem Pan. pokud hledáme plus, tak + atd.

Další dva znaky v naší šabloně, tečka a kvantifikační hvězdička, znamenají libovolný počet libovolných znaků, tj. libovolný název města.

Na konci šablony je čárka, protože hledáme text z „g“. na čárku. Ale v textu může být několik čárek, ne? Nejen po městě, ale i po ulici, domech atd. Na kterém z nich se náš požadavek zastaví? Od toho je ten otazník. Bez něj by náš regulární výraz vytáhl nejdelší možný řetězec:

Z hlediska regulárních výrazů je takový vzor „chamtivý“. K nápravě situace je potřeba otazník – díky němu je kvantifikátor, za kterým stojí, „skoupý“ – a náš dotaz zabírá text pouze po první protičárku za „g.“:

Název souboru z celé cesty

Další velmi častou situací je extrahování názvu souboru z úplné cesty. Zde pomůže jednoduchý regulární výraz formuláře:

Trik je v tom, že hledání ve skutečnosti probíhá v opačném směru – od konce k začátku, protože na konci naší šablony je $, a hledáme vše před tím až po první zpětné lomítko zprava. Zpětné lomítko je uvozeno, stejně jako tečka v předchozím příkladu.

PS

„Ke konci“ chci objasnit, že vše výše uvedené je malou částí všech možností, které regulární výrazy poskytují. Speciálních postav a pravidel pro jejich použití je spousta a na toto téma byly napsány celé knihy (pro začátek doporučuji alespoň tuto). Psaní regulárních výrazů je svým způsobem téměř umění. Téměř vždy lze vynalezený regulární výraz vylepšit nebo doplnit a učinit jej elegantnějším nebo schopným pracovat s širší škálou vstupních dat.

Chcete-li analyzovat a analyzovat regulární výrazy jiných lidí nebo ladit své vlastní, existuje několik pohodlných online služeb: RegEx101, RegExr více

Bohužel ne všechny funkce klasických regulárních výrazů jsou ve VBA podporovány (například reverzní vyhledávání nebo třídy POSIX) a umí pracovat s azbukou, ale myslím, že to, co tam je, napoprvé k potěše stačí.

Pokud vám toto téma není nové a máte se o co podělit, zanechte regulární výrazy užitečné při práci v Excelu v komentářích níže. Jedna mysl je dobrá, ale dvě boty jsou pár!

• Nahrazení a vyčištění textu pomocí funkce SUBSTITUTE
• Vyhledávání a zvýrazňování latinských znaků v textu
• Vyhledejte nejbližší podobný text (Ivanov = Ivonov = Ivanof atd.)