Raščlanite tekst regularnim izrazima (RegExp) u Excelu

Jedan od najdugotrajnijih i frustrirajućih zadataka pri radu s tekstom u Excelu je raščlanjivanje – rastavljanje alfanumeričke "kaše" na komponente i izdvajanje fragmenata koji su nam potrebni iz nje. Na primjer:

• izdvajanje poštanskog broja iz adrese (dobro je da je poštanski broj uvijek na početku, ali što ako nije?)
• pronalaženje broja i datuma računa iz opisa plaćanja u bankovnom izvodu
• izdvajanje PIB-a iz šarolikih opisa tvrtki u popisu protustranaka
• potražite broj automobila ili broj artikla u opisu itd.
  

Obično u takvim slučajevima, nakon pola sata mučnog ručnog prebiranja teksta, počnu padati na pamet misli da nekako automatiziramo ovaj proces (osobito ako ima puno podataka). Postoji nekoliko rješenja s različitim stupnjevima složenosti i učinkovitosti:

• Koristiti ugrađene tekstualne funkcije programa Excel za pretraživanje-izrezivanje-lijepljenje teksta: LEVSIMV (LIJEVO), PRAVO (PRAVO), PSTR (sredina), STsEPIT (SPAJANJE) i njegovi analozi, KOMBINIRATI (ZAJEDNIČKI TEKST), TOČNO (TOČNO) itd. Ova metoda je dobra ako u tekstu postoji jasna logika (npr. indeks je uvijek na početku adrese). U suprotnom, formule postaju znatno kompliciranije, a ponekad čak dolazi i do formula nizova, što jako usporava na velikim tablicama.
• Korištenje poput operatora sličnosti teksta iz Visual Basica umotan u prilagođenu makro funkciju. To vam omogućuje implementaciju fleksibilnijeg pretraživanja korištenjem zamjenskih znakova (*, #,?, itd.). Nažalost, ovaj alat ne može izdvojiti željeni podniz iz teksta – samo provjerite nalazi li se u njemu.

Osim navedenog, postoji još jedan pristup koji je vrlo dobro poznat u uskim krugovima profesionalnih programera, web developera i ostalih tehničara – to je pravilni izrazi (Regularni izrazi = RegExp = “regexps” = “regulars”). Jednostavno rečeno, RegExp je jezik u kojem se posebni znakovi i pravila koriste za traženje potrebnih podstringova u tekstu, njihovo izdvajanje ili zamjenu drugim tekstom. Regularni izrazi vrlo su moćan i lijep alat koji nadmašuje sve druge načine rada s tekstom za red veličine. Mnogi programski jezici (C#, PHP, Perl, JavaScript…) i uređivači teksta (Word, Notepad++…) podržavaju regularne izraze.

Microsoft Excel nažalost nema podršku za RegExp odmah, no to se može lako popraviti pomoću VBA. Otvorite Visual Basic Editor s kartice razvijač (Programer) ili tipkovni prečac drugo+F11. Zatim umetnite novi modul kroz izbornik Umetak – Modul i tamo kopirajte tekst sljedeće makro funkcije:

Javna funkcija 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 (Tekst) Zatim postavite podudaranja = regex.Execute(Tekst) RegExpExtract = podudaranja.Item(Item - 1) Izlaz iz funkcije End If ErrHandl: RegExpExtract = CVErr(xlErrValue) Kraj funkcije  

Sada možemo zatvoriti Visual Basic Editor i vratiti se u Excel kako bismo isprobali našu novu značajku. Njegova sintaksa je sljedeća:

=RegExpExtract( Txt ; uzorak ; stavka )

gdje

• txt – ćelija s tekstom koji provjeravamo i iz kojeg želimo izdvojiti podniz koji nam treba
• uzorak – maska ​​(uzorak) za pretragu podniza
• Stavka – redni broj podniza koji se izdvaja, ako ih ima više (ako nije naveden, prikazuje se prvo pojavljivanje)

Najzanimljivija stvar ovdje je naravno Pattern – predložak niza posebnih znakova “na jeziku” RegExp-a, koji specificira što točno i gdje želimo pronaći. Evo najosnovnijih za početak:

Ako tražimo određeni broj znakova, na primjer šesteroznamenkasti poštanski broj ili sve troslovne šifre proizvoda, tada priskačemo u pomoć kvantifikatori or kvantifikatori su posebni izrazi koji određuju broj znakova koji se traže. Kvantifikatori se primjenjuju na znak koji dolazi prije njega:

Sada prijeđimo na najzanimljiviji dio – analizu primjene izrađene funkcije i onoga što smo naučili o uzorcima na praktičnim primjerima iz života.

Izdvajanje brojeva iz teksta

Za početak, analizirajmo jednostavan slučaj - iz cjenika trebate izdvojiti prvi broj iz alfanumeričke kaše, na primjer, snagu besprekidnog napajanja:

Logika iza regularnog izraza je jednostavna: d znači bilo koju znamenku, a kvantifikator + kaže da njihov broj treba biti jedan ili više. Dvostruki minus ispred funkcije potreban je za pretvaranje izdvojenih znakova u cijeli broj iz teksta kao "u hodu".

Poštanski

Na prvi pogled ovdje je sve jednostavno – tražimo točno šest znamenki u nizu. Koristimo poseban znak d za znamenku i kvantifikator 6 {} za broj znakova:

Međutim, moguća je situacija kada se lijevo od indeksa u retku nalazi još jedan veliki skup brojeva u nizu (telefonski broj, PIB, bankovni račun itd.) Tada će naša regularna sezona izvući prvih 6 znamenki iz njega, tj. neće raditi ispravno:

Kako bismo spriječili da se to dogodi, moramo dodati modifikator oko rubova našeg regularnog izraza b označavajući kraj riječi. To će Excelu jasno dati do znanja da bi fragment (indeks) koji trebamo trebao biti zasebna riječ, a ne dio drugog fragmenta (telefonskog broja):

Telefon

Problem s pronalaženjem telefonskog broja u tekstu je to što postoji toliko mnogo opcija za pisanje brojeva – sa i bez crtica, kroz razmake, sa ili bez regionalnog koda u zagradi, itd. Stoga je, po mom mišljenju, lakše prvo očistite sve te znakove iz izvornog teksta pomoću nekoliko ugniježđenih funkcija ZAMJENA (ZAMJENA)tako da se slijepi u jedinstvenu cjelinu, a zatim s primitivnim regularnim d{11} izvucite 11 znamenki u nizu:

ITN

Ovdje je malo kompliciranije jer PIB (kod nas) može biti 10-znamenkasti (za pravne osobe) ili 12-znamenkasti (za fizičke osobe). Ako ne nalazite posebno zamjerku, onda je sasvim moguće biti zadovoljan redovitim d{10,12}, ali će, strogo govoreći, izvući sve brojeve od 10 do 12 znakova, odnosno i pogrešno unesenih 11 znamenki. Ispravnije bi bilo koristiti dva uzorka povezana logičkim OR operatorom | (okomita crta):

Imajte na umu da u upitu prvo tražimo 12-bitne brojeve, a tek onda 10-bitne brojeve. Ako naš regularni izraz napišemo obrnuto, tada će za sve, čak i za dugačke 12-bitne TIN-ove, izvući samo prvih 10 znakova. Odnosno, nakon pokretanja prvog uvjeta, daljnja se provjera više ne provodi:

To je temeljna razlika između operatera | iz standardne excel logičke funkcije OR (ILI), gdje preuređivanje argumenata ne mijenja rezultat.

Šifre proizvoda

U mnogim se tvrtkama jedinstveni identifikatori dodjeljuju robi i uslugama – artiklima, SAP kodovima, SKU-ovima, itd. Ako postoji logika u njihovoj notaciji, tada se mogu lako izvući iz bilo kojeg teksta pomoću regularnih izraza. Na primjer, ako znamo da se naši članci uvijek sastoje od tri velika engleska slova, crtice i sljedećeg troznamenkastog broja, tada:

Logika iza predloška je jednostavna. [AZ] – označava bilo koja velika slova latinične abecede. Sljedeći kvantifikator 3 {} kaže da je za nas važno da postoje točno tri takva slova. Nakon crtice čekaju se tri znamenke pa dodajemo na kraju d{3}

Iznosi gotovine

Na sličan način kao u prethodnom stavku iz opisa robe možete izvući i cijene (troškovi, PDV...). Ako su novčani iznosi, na primjer, označeni s crticom, tada:

Uzorak d s kvantifikatorom + traži bilo koji broj do crtice, i d{2} tražit će penije (dvije znamenke) nakon.

Ako ne trebate izdvojiti cijene, već PDV, tada možete upotrijebiti treći izborni argument naše funkcije RegExpExtract, koji navodi redni broj elementa koji se izdvaja. I, naravno, možete zamijeniti funkciju ZAMJENA (ZAMJENA) u rezultatima stavite crticu na standardni decimalni razdjelnik i dodajte dvostruki minus na početku kako bi Excel protumačio pronađeni PDV kao normalan broj:

Brojevi automobila

Ako ne uzimate specijalna vozila, prikolice i druge motocikle, tada se standardni broj automobila raščlanjuje prema načelu "slovo - tri brojke - dva slova - regionalni kod". Štoviše, kod regije može biti 2 ili 3 znamenke, a kao slova koriste se samo ona koja su izgledom slična latiničnoj abecedi. Stoga će nam sljedeći regularni izraz pomoći da izdvojimo brojeve iz teksta:

Vrijeme

Za izdvajanje vremena u formatu HH:MM prikladan je sljedeći regularni izraz:

Nakon fragmenta debelog crijeva [0-5]d, kao što je lako shvatiti, postavlja bilo koji broj u rasponu 00-59. Prije dvotočke u zagradama rade dva uzorka odvojena logičkim ILI (crtom):

• [0-1]d – bilo koji broj u rasponu 00-19
• 2 [0-3] – bilo koji broj u rasponu 20-23

Na dobiveni rezultat možete dodatno primijeniti standardnu ​​Excel funkciju VRIJEME (TIM)pretvoriti u format vremena koji je razumljiv programu i prikladan za daljnje izračune.

Provjera lozinke

Pretpostavimo da moramo provjeriti točnost popisa lozinki koje su izmislili korisnici. Prema našim pravilima, lozinke mogu sadržavati samo engleska slova (mala ili velika) i brojeve. Razmaci, podvlake i drugi interpunkcijski znakovi nisu dopušteni.

Provjera se može organizirati pomoću sljedećeg jednostavnog regularnog izraza:

Zapravo, s takvim uzorkom zahtijevamo da između početka (^) i kraj ($) u našem tekstu bili su samo znakovi iz skupa navedenog u uglatim zagradama. Ako trebate provjeriti i duljinu lozinke (na primjer, najmanje 6 znakova), kvantifikator + može se zamijeniti intervalom "šest ili više" u obrascu {6,}:

Grad s adrese

Recimo da trebamo izvući grad iz adresne trake. Uobičajeni program će vam pomoći, izdvajajući tekst iz "g." do sljedećeg zareza:

Pogledajmo pobliže ovaj uzorak.

Ako ste pročitali gornji tekst, onda ste već shvatili da neki znakovi u regularnim izrazima (točke, zvjezdice, znakovi dolara itd.) imaju posebno značenje. Ako trebate potražiti same ove znakove, ispred njih stoji obrnuta kosa crta (ponekad se naziva oklop). Stoga, kada tražite fragment "g." moramo pisati regularnim izrazom Gospodin ako tražimo plus, onda + i tako dalje

Sljedeća dva znaka u našem predlošku, točka i zvjezdica kvantifikatora, označavaju bilo koji broj bilo kojih znakova, tj. bilo koje ime grada.

Na kraju šablona stoji zarez, jer tražimo tekst od “g.” do zareza. Ali u tekstu može biti nekoliko zareza, zar ne? Ne samo nakon grada, nego i nakon ulice, kuće itd. Na kojem će se od njih naš zahtjev zaustaviti? Tome služi upitnik. Bez njega bi naš regularni izraz izvukao najduži mogući niz:

U smislu regularnih izraza, takav obrazac je "pohlepan". Da bismo ispravili situaciju, potreban je upitnik – on čini kvantifikator iza kojeg stoji “škrt” – a naš upit uzima tekst samo do prvog brojača zareza iza “g.”:

Naziv datoteke iz punog puta

Još jedna vrlo česta situacija je izdvajanje naziva datoteke iz punog puta. Ovdje će pomoći jednostavan regularni izraz obrasca:

Trik je u tome što se pretraga, zapravo, odvija u suprotnom smjeru – od kraja prema početku, jer je na kraju našeg predloška $, a mi tražimo sve prije njega do prve obrnute kose crte s desne strane. Obrnuta kosa crta je escaped, kao i točka u prethodnom primjeru.

PS

“Prema kraju” želim pojasniti da je sve gore navedeno mali dio svih mogućnosti koje regularni izrazi pružaju. Postoji mnogo posebnih znakova i pravila za njihovu upotrebu, a na tu su temu napisane cijele knjige (preporučam barem ovu za početak). Na neki način pisanje regularnih izraza gotovo je umjetnost. Gotovo uvijek, izmišljeni regularni izraz može se poboljšati ili dopuniti, čineći ga elegantnijim ili sposobnijim za rad sa širim rasponom ulaznih podataka.

Za analizu i raščlanjivanje tuđih regularnih izraza ili otklanjanje pogrešaka vlastitih, postoji nekoliko praktičnih online usluga: RegEx101, RegExr i više

Nažalost, nisu sve značajke klasičnih regularnih izraza podržane u VBA (na primjer, obrnuto pretraživanje ili POSIX klase) i mogu raditi s ćirilicom, ali mislim da je ono što postoji dovoljno za prvi put da vas zadovolji.

Ako niste novi u temi, a imate nešto za podijeliti, u komentarima ispod ostavite regularne izraze korisne pri radu u Excelu. Jednoumlje je dobro, ali dvije čizme su par!

• Zamjena i čišćenje teksta s funkcijom SUBSTITUTE
• Pretraživanje i označavanje latiničnih znakova u tekstu
• Traži najbliži sličan tekst (Ivanov = Ivonov = Ivanof, itd.)