Parsirajte tekst s regularnim izrazima (RegExp) u Excelu

Jedan od najzahtjevnijih i najfrustrirajućih zadataka pri radu s tekstom u Excelu je raščlanjivanje – raščlanjivanje 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 ako je poštanski broj uvijek na početku, ali šta ako nije?)
• pronalaženje broja i datuma računa iz opisa plaćanja na izvodu banke
• vađenje PIB-a iz šarolikih opisa kompanija na listi ugovornih strana
• potražite u opisu broj automobila ili artikla itd.
  

Obično u takvim slučajevima, nakon pola sata turobnog ručnog prebiranja teksta, počnu padaju na pamet misli da se nekako automatizira ovaj proces (pogotovo ako ima puno podataka). Postoji nekoliko rješenja i različitog stepena složenosti-efikasnosti:

• upotreba ugrađene Excel tekstualne funkcije za pretraživanje-izrezivanje-lijepljenje teksta: LEVSIMV (LIJEVO), PRAVO (DESNO), PSTR (sredina), STsEPIT (SPOJITI) i njegovih analoga, POGLEDAJTE (JOINTEXT), TOČNO (TAČNO) itd. Ova metoda je dobra ako postoji jasna logika u tekstu (na primjer, indeks je uvijek na početku adrese). U suprotnom, formule postaju mnogo kompliciranije, a ponekad čak dolazi i do formula niza, što u velikoj mjeri usporava na velikim tablicama.
• korišćenje poput operatora sličnosti teksta iz Visual Basica umotanog u prilagođenu makro funkciju. Ovo vam omogućava da implementirate fleksibilnije pretraživanje pomoću zamjenskih znakova (*, #,?, itd.) Nažalost, ovaj alat ne može izdvojiti željeni podniz iz teksta – samo provjerite da li se on nalazi u njemu.

Pored navedenog, postoji još jedan pristup koji je vrlo dobro poznat u uskim krugovima profesionalnih programera, web programera i ostalih tehničara – to je regularni izrazi (Regularni izrazi = RegExp = “regexps” = “regulars”). jednostavno rečeno, RegExp je jezik u kojem se koriste posebni znakovi i pravila za traženje potrebnih podnizova u tekstu, njihovo izdvajanje ili zamjenu drugim tekstom. Regularni izrazi su vrlo moćan i lijep alat koji za red veličine nadmašuje sve druge načine rada s tekstom. 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 iz kutije, ali to se može lako popraviti pomoću VBA. Otvorite Visual Basic Editor sa kartice Developer (programer) ili prečica na tastaturi alt+F11. Zatim umetnite novi modul kroz meni Umetak – Modul i tamo kopirajte tekst sljedeće makro funkcije:

Javna funkcija RegExpExtract(tekst kao string, uzorak kao string, opciona stavka kao cijeli broj = 1) Kao string na grešci GoTo ErrHandl Postavi regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global. =T Truest Ako regex (Tekst) Zatim postavite podudaranja = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Izlaz iz funkcije Kraj ako ErrHandl: RegExpExtract = CVERr(xlErrValue) Kraj funkcije  

Sada možemo zatvoriti Visual Basic Editor i vratiti se u Excel da isprobamo našu novu funkciju. Njegova sintaksa je sljedeća:

=RegExpExtract(Txt; Uzorak; Stavka)

gdje

• TXT – ćelija sa tekstom koji provjeravamo i iz koje želimo izdvojiti podniz koji nam je potreban
• obrazac – maska ​​(uzorak) za pretragu podniza
• stavka – redni broj podniza koji se izdvaja, ako ih ima nekoliko (ako nije navedeno, onda se prikazuje prvo pojavljivanje)

Najzanimljivija stvar ovdje je, naravno, Pattern – šablonski niz specijalnih znakova “na jeziku” RegExp-a, koji određuje šta tačno i gdje želimo pronaći. Evo najosnovnijih za početak:

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

Pređimo sada na najzanimljiviji dio – analizu primjene kreirane funkcije i onoga što smo naučili o obrascima na praktičnim primjerima iz života.

Izdvajanje brojeva iz teksta

Za početak, analizirajmo jednostavan slučaj – potrebno je izdvojiti prvi broj iz alfanumeričke kaše, na primjer, snagu neprekidnog napajanja iz cjenovnika:

Logika koja stoji iza regularnog izraza je jednostavna: d znači bilo koju cifru i kvantifikator + kaže da njihov broj treba da bude jedan ili više. Dupli minus ispred funkcije je potreban da bi se „u hodu“ pretvorili izdvojeni znakovi u puni broj iz broja kao teksta.

Poštanski broj

Ovdje je na prvi pogled sve jednostavno – tražimo tačno šest cifara za redom. Koristimo poseban znak d za cifru i kvantifikator 6 {} za broj znakova:

Međutim, moguća je situacija kada se lijevo od indeksa u redu nalazi još jedan veliki skup brojeva u nizu (broj telefona, PIB, bankovni račun itd.) Tada će naš regularni dio izvući prvih 6 cifre 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čava kraj riječi. Time će Excelu biti jasno da fragment (indeks) koji nam treba treba biti zasebna riječ, a ne dio drugog fragmenta (broj telefona):

Telefon

Problem sa pronalaženjem telefonskog broja u tekstu je što postoji toliko opcija za pisanje brojeva – sa i bez crtica, kroz razmake, sa ili bez koda regije u zagradama itd. Stoga je, po mom mišljenju, lakše prvo očistite sve ove znakove iz izvornog teksta koristeći nekoliko ugniježđenih funkcija SUBSTITUTE (ZAMJENA)tako da se zalijepi u jedinstvenu cjelinu, a zatim u primitivnu regularnost d{11} izvucite 11 cifara za redom:

ITN

Ovde je malo komplikovanije, jer PIB (kod nas) može biti 10-cifreni (za pravna lica) ili 12-cifreni (za fizička lica). Ako ne nađete posebno zamjerke, onda je sasvim moguće biti zadovoljan sa regularnim d{10,12}, ali, striktno govoreći, izvući će sve brojeve od 10 do 12 znakova, odnosno pogrešno unesenih 11 cifara. Bilo bi ispravnije koristiti dva obrasca povezana logičkim OR operatorom | (vertikalna traka):

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, onda će izvući za sve, čak i duge 12-bitne TIN-ove, samo prvih 10 znakova. Odnosno, nakon što se pokrene prvi uslov, dalja verifikacija se više ne vrši:

Ovo je fundamentalna razlika između operatera | iz standardne excel logičke funkcije OR (OR), pri čemu preuređivanje argumenata ne mijenja rezultat.

Šifre proizvoda

U mnogim kompanijama, robi i uslugama se dodeljuju jedinstveni identifikatori – artikli, SAP kodovi, SKU-ovi, itd. Ako postoji logika u njihovoj notaciji, onda se mogu lako izvući iz bilo kog teksta koristeći regularne izraze. Na primjer, ako znamo da se naši članci uvijek sastoje od tri velika engleska slova, crtice i sljedećeg trocifrenog broja, tada:

Logika iza šablona je jednostavna. [AZ] – označava svako veliko slovo latinice. Sljedeći kvantifikator 3 {} kaže da je za nas važno da postoje tačno tri takva pisma. Nakon crtice čekamo tri cifre, pa dodajemo na kraju d{3}

Iznosi gotovine

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

obrazac d sa kvantifikatorom + traži bilo koji broj do crtice i d{2} će tražiti penije (dvije cifre) nakon.

Ako ne trebate izdvojiti cijene, već PDV, onda možete koristiti treći opcijski argument naše funkcije RegExpExtract, koji specificira redni broj elementa koji treba izdvojiti. I, naravno, možete zamijeniti funkciju SUBSTITUTE (ZAMJENA) u rezultatima stavite crticu na standardni decimalni separator 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 analizira po principu „slovo – tri broja – dva slova – kod regije“. Štaviše, kod regiona može biti 2- ili 3-cifreni, a kao slova se koriste samo ona koja su po izgledu slična latiničnoj abecedi. Dakle, sljedeći regularni izraz će nam pomoći da izvučemo 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 zaključiti, postavlja bilo koji broj u rasponu od 00-59. Prije dvotočka u zagradama rade dva obrasca, odvojena logičkim ILI (cijevom):

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

Na dobiveni rezultat možete dodatno primijeniti standardnu ​​Excel funkciju TIME (TIM)da ga konvertujete u vremenski format koji je razumljiv programu i pogodan za dalje proračune.

Provjera lozinke

Pretpostavimo da trebamo provjeriti ispravnost liste lozinki koje su izmislili korisnici. Prema našim pravilima, lozinke mogu sadržavati samo engleska slova (mala ili velika) i brojeve. Razmaci, donje crte i drugi znaci interpunkcije nisu dozvoljeni.

Provjera se može organizirati korištenjem sljedećeg jednostavnog regularnog izraza:

Zapravo, s takvim obrascem zahtijevamo da između početka (^) i kraj ($) u našem tekstu su bili samo znakovi iz skupa dati u uglastim zagradama. Ako također trebate provjeriti dužinu lozinke (na primjer, najmanje 6 znakova), tada kvantifikator + može se zamijeniti intervalom „šest ili više“ u obrascu {6,}:

Grad sa adrese

Recimo da trebamo povući grad iz adresne trake. Redovni program će pomoći, izdvajajući tekst iz "g". do sljedećeg zareza:

Pogledajmo pobliže ovaj obrazac.

Ako ste pročitali gornji tekst, onda ste već shvatili da neki znakovi u regularnim izrazima (tačke, zvjezdice, znakovi dolara, itd.) imaju posebno značenje. Ako trebate sami potražiti ove znakove, onda im prethodi obrnuta kosa crta (ponekad se naziva štit). Stoga, kada tražite fragment "g." moramo pisati u regularnom izrazu Gospodin. ako tražimo plus, onda + itd

Sljedeća dva znaka u našem predlošku, tačka i kvantifikator zvjezdica, označavaju bilo koji broj bilo kojeg karaktera, tj. bilo koji naziv grada.

Na kraju šablona nalazi se zarez, jer tražimo tekst iz "g". na zarez. Ali u tekstu može biti nekoliko zareza, zar ne? Ne samo nakon grada, već i nakon ulice, kuća itd. Na kojoj će od njih stati naš zahtjev? Za to je znak pitanja. Bez toga, naš regularni izraz bi izvukao najduži mogući niz:

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

Ime datoteke iz pune putanje

Druga vrlo česta situacija je izdvajanje imena datoteke iz pune putanje. Jednostavan regularni izraz forme pomoći će ovdje:

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

PS

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

Da biste analizirali i raščlanili tuđe regularne izraze ili otklonili vlastite greške, postoji nekoliko zgodnih online usluga: RegEx101, RegExr i više

Nažalost, nisu sve karakteristike klasičnih regularnih izraza podržane u VBA (na primjer, obrnuta pretraga ili POSIX klase) i mogu raditi sa ćirilicom, ali mislim da je ono što ima dovoljno za prvi put.

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

• Zamjena i čišćenje teksta sa funkcijom SUBSTITUTE
• Pretraga i isticanje latiničnih znakova u tekstu
• Potražite najbliži sličan tekst (Ivanov = Ivonov = Ivanof, itd.)