Ontleed teks met gereelde uitdrukkings (RegExp) in Excel

Een van die mees tydrowende en frustrerende take wanneer u met teks in Excel werk, is ontleed – die alfanumeriese "pap" in komponente te ontleed en die fragmente wat ons benodig daaruit te onttrek. Byvoorbeeld:

• onttrek die poskode uit die adres (dit is goed as die poskode altyd aan die begin is, maar wat as dit nie is nie?)
• vind die nommer en datum van die faktuur uit die beskrywing van die betaling in die bankstaat
• onttrekking van TIN uit bont beskrywings van maatskappye in die lys van teenpartye
• soek 'n motornommer of artikelnommer in die beskrywing, ens.
  

Gewoonlik in sulke gevalle, na 'n halfuur van somber handmatig in die teks, begin gedagtes op een of ander manier by my opkom om hierdie proses te outomatiseer (veral as daar baie data is). Daar is verskeie oplossings en met verskillende grade van kompleksiteit-doeltreffendheid:

• Gebruik ingeboude Excel-teksfunksies om te soek-knip-gom teks: LEVSIMV (Links), REGS (Regs), PSTR (middel), STEPIT (SAAMVOEG) en sy analoë, kombineer (SAANSLUITTEKS), PRESIES (PRESIES) ens. Hierdie metode is goed as daar 'n duidelike logika in die teks is (byvoorbeeld, die indeks is altyd aan die begin van die adres). Andersins word die formules baie meer ingewikkeld en soms kom dit selfs by skikkingsformules, wat baie stadiger word op groot tabelle.
• Die gebruik van soos teksooreenkoms-operateur van Visual Basic toegedraai in 'n pasgemaakte makro-funksie. Dit laat jou toe om 'n meer buigsame soektog te implementeer deur gebruik te maak van jokertekens (*, #,?, ens.) Ongelukkig kan hierdie hulpmiddel nie die verlangde substring uit die teks onttrek nie - kyk net of dit daarin vervat is.

Benewens bogenoemde, is daar nog 'n benadering wat baie bekend is in noue kringe van professionele programmeerders, webontwikkelaars en ander tegnologieë – dit is gereelde uitdrukkings (Gereelde uitdrukkings = RegExp = "regexps" = "gewone uitdrukkings"). Eenvoudig gestel, RegExp is 'n taal waar spesiale karakters en reëls gebruik word om die nodige substringe in die teks te soek, dit te onttrek of met ander teks te vervang. Gereelde uitdrukkings is 'n baie kragtige en pragtige hulpmiddel wat alle ander maniere van werk met teks met 'n orde van grootte oortref. Baie programmeertale (C#, PHP, Perl, JavaScript ...) en teksredigeerders (Word, Notepad++...) ondersteun gereelde uitdrukkings.

Microsoft Excel het ongelukkig nie RegExp-ondersteuning uit die boks nie, maar dit kan maklik met VBA reggestel word. Maak die Visual Basic Editor vanaf die oortjie oop ontwikkelaar (Ontwikkelaar) of sleutelbordkortpad alt+F11. Voeg dan die nuwe module deur die kieslys in Voeg in – Module en kopieer die teks van die volgende makrofunksie daarheen:

Publieke Funksie RegExpExtract(Teks As String, Patroon As String, Opsionele Item As Heelgetal = 1) As String By Error GoTo ErrHandl Stel regex = CreateObject("VBScript.RegExp") regex.Pattern = Patroon regex.Global = Waar As regex.Test (Teks) Stel dan matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Ons kan nou die Visual Basic Editor toemaak en terugkeer na Excel om ons nuwe funksie te probeer. Die sintaksis daarvan is die volgende:

=RegExpExtract(Txt; Patroon; Item)

waar

• txt – 'n sel met die teks wat ons nagaan en waaruit ons die substring wat ons benodig wil onttrek
• patroon - masker (patroon) vir substring soektog
• item – die volgordenommer van die substring wat onttrek moet word, indien daar verskeie van hulle is (indien nie gespesifiseer nie, word die eerste voorkoms vertoon)

Die interessantste ding hier is natuurlik Pattern – 'n sjabloonstring spesiale karakters "in die taal" van RegExp, wat spesifiseer wat presies en waar ons wil vind. Hier is die mees basiese om aan die gang te kom:

As ons op soek is na 'n sekere aantal karakters, byvoorbeeld 'n ses-syfer poskode of al drie-letter produk kodes, dan kom ons tot die redding kwantifiseerders or kwantifiseerders is spesiale uitdrukkings wat die aantal karakters spesifiseer wat gesoek moet word. Kwantifiseerders word toegepas op die karakter wat daarvoor kom:

Kom ons gaan nou oor na die interessantste deel – 'n ontleding van die toepassing van die geskepte funksie en wat ons oor patrone op praktiese voorbeelde uit die lewe geleer het.

Onttrek getalle uit teks

Om mee te begin, kom ons ontleed 'n eenvoudige geval - jy moet die eerste nommer uit alfanumeriese pap onttrek, byvoorbeeld die krag van ononderbroke kragbronne uit die pryslys:

Die logika agter die gewone uitdrukking is eenvoudig: d beteken enige syfer, en die kwantifiseerder + sê dat hul nommer een of meer moet wees. Die dubbele minus voor die funksie is nodig om "on the fly" die onttrekte karakters om te skakel in 'n volle getal uit die nommer-as-teks.

Poskode

Met die eerste oogopslag is alles eenvoudig hier – ons soek presies ses syfers in 'n ry. Ons gebruik 'n spesiale karakter d vir syfer en kwantifiseerder 6 {} vir die aantal karakters:

'n Situasie is egter moontlik wanneer daar links van die indeks in die lyn nog 'n groot stel nommers in 'n ry is (foonnommer, TIN, bankrekening, ens.) Dan sal ons gewone seisoen die eerste 6 uittrek. syfers daaruit, dws sal nie reg werk nie:

Om te voorkom dat dit gebeur, moet ons 'n wysiger om die kante van ons gereelde uitdrukking byvoeg b wat die einde van 'n woord aandui. Dit sal dit vir Excel duidelik maak dat die fragment (indeks) wat ons benodig 'n aparte woord moet wees, en nie deel van 'n ander fragment (foonnommer) nie:

Kontak

Die probleem met die vind van 'n telefoonnommer in die teks is dat daar soveel opsies is om nommers te skryf – met en sonder koppeltekens, deur spasies, met of sonder 'n streekkode tussen hakies, ens. Daarom is dit na my mening makliker om maak eers al hierdie karakters uit die bronteks skoon deur verskeie geneste funksies te gebruik PLAASVERVANGER (PLAASVERVANG)sodat dit in 'n enkele geheel aanmekaar kleef, en dan met 'n primitiewe reëlmaat d{11} trek 11 syfers in 'n ry uit:

ITN

Dit is 'n bietjie meer ingewikkeld hier, want TIN (in ons land) kan 10-syfer (vir regspersone) of 12-syfer (vir individue) wees. As jy nie veral fout vind nie, dan is dit heel moontlik om tevrede te wees met die gereelde d{10,12}, maar streng gesproke sal dit alle nommers van 10 tot 12 karakters uittrek, dws en 11 syfers foutief ingevoer. Dit sal meer korrek wees om twee patrone te gebruik wat deur 'n logiese OF-operateur verbind word | (vertikale balk):

Let asseblief daarop dat ons in die navraag eers vir 12-bis-nommers soek, en dan eers vir 10-bis-nommers. As ons ons gewone uitdrukking andersom skryf, sal dit vir almal uittrek, selfs lang 12-bis TIN's, net die eerste 10 karakters. Dit wil sê, nadat die eerste toestand geaktiveer is, word verdere verifikasie nie meer uitgevoer nie:

Dit is die fundamentele verskil tussen die operateur | van 'n standaard Excel-logika-funksie OR (OR), waar die herrangskikking van die argumente nie die resultaat verander nie.

Produk SKU's

In baie maatskappye word unieke identifiseerders aan goedere en dienste toegeken – artikels, SAP-kodes, SKU's, ens. As daar logika in hul notasie is, kan dit maklik uit enige teks getrek word deur gebruik te maak van gewone uitdrukkings. Byvoorbeeld, as ons weet dat ons artikels altyd bestaan ​​uit drie hoofletters in Engels, 'n koppelteken en 'n daaropvolgende drie-syfer nommer, dan:

Die logika agter die sjabloon is eenvoudig. [AZ] – beteken enige hoofletters van die Latynse alfabet. Die volgende kwantifiseerder 3 {} sê dat dit vir ons belangrik is dat daar presies drie sulke letters is. Na die koppelteken wag ons vir drie syfers, so ons voeg aan die einde by d{3}

Kontantbedrae

Op 'n soortgelyke manier as die vorige paragraaf, kan jy ook pryse (koste, BTW …) uit die beskrywing van goedere trek. As geldbedrae, byvoorbeeld, met 'n koppelteken aangedui word, dan:

Patroon d met kwantifiseerder + soek vir enige getal tot by 'n koppelteken, en d{2} sal kyk vir pennies (twee syfers) na.

As jy nie pryse moet onttrek nie, maar BTW, dan kan jy die derde opsionele argument van ons RegExpExtract-funksie gebruik, wat die rangnommer spesifiseer van die element wat onttrek moet word. En natuurlik kan jy die funksie vervang PLAASVERVANGER (PLAASVERVANG) in die resultate, koppelteken na die standaard desimale skeiding en voeg 'n dubbele minus aan die begin by sodat Excel die gevonde BTW as 'n normale getal interpreteer:

Motorplaatnommers

As jy nie spesiale voertuie, sleepwaens en ander motorfietse neem nie, word die standaard motornommer volgens die beginsel “letter – drie syfers – twee letters – streekkode” ontleed. Boonop kan die streekkode 2- of 3-syfer wees, en slegs dié wat in voorkoms soortgelyk is aan die Latynse alfabet, word as letters gebruik. Die volgende reëlmatige uitdrukking sal ons dus help om getalle uit die teks te onttrek:

tyd

Om die tyd in die HH:MM-formaat te onttrek, is die volgende gereelde uitdrukking geskik:

Na kolon fragment [0-5]d, aangesien dit maklik is om uit te vind, stel enige getal in die reeks 00-59. Voor die dubbelpunt tussen hakies, werk twee patrone, geskei deur 'n logiese OF (pyp):

• [0-1]d – enige getal in die reeks 00-19
• 2[0-3] – enige getal in die reeks 20-23

Vir die resultaat wat verkry word, kan u ook die standaard Excel-funksie toepas TYD (SPAN)om dit om te skakel in 'n tydformaat wat vir die program verstaanbaar is en geskik is vir verdere berekeninge.

Wagwoord kontroleer

Gestel ons moet die lys wagwoorde wat deur gebruikers uitgevind is, nagaan vir korrektheid. Volgens ons reëls kan wagwoorde slegs Engelse letters (kleinletters of hoofletters) en syfers bevat. Spasies, onderstrepings en ander leestekens word nie toegelaat nie.

Kontrolering kan georganiseer word deur die volgende eenvoudige gereelde uitdrukking te gebruik:

Trouens, met so 'n patroon vereis ons dat tussen die begin (^) en eindig ($) in ons teks was daar net karakters uit die stel wat tussen vierkantige hakies gegee is. As jy ook die lengte van die wagwoord moet kontroleer (byvoorbeeld, ten minste 6 karakters), dan moet die kwantifiseerder + kan vervang word deur die interval "ses of meer" in die vorm {6,}:

Stad vanaf adres

Kom ons sê ons moet die stad uit die adresbalk haal. Die gewone program sal help deur die teks uit "g" te onttrek. na die volgende komma:

Kom ons kyk na hierdie patroon van naderby.

As jy die teks hierbo gelees het, dan het jy reeds verstaan ​​dat sommige karakters in gewone uitdrukkings (punte, sterretjies, dollartekens, ens.) 'n spesiale betekenis het. As jy self na hierdie karakters moet soek, word hulle voorafgegaan deur 'n terugskuinsstreep (soms genoem beskerm). Daarom, wanneer u na die fragment "g" soek. ons moet in gewone uitdrukking skryf Mnr. as ons 'n pluspunt soek, dan + ens.

Die volgende twee karakters in ons sjabloon, die punt en die kwantifiseerder asterisk, staan ​​vir enige aantal karakters, dws enige stadnaam.

Daar is 'n komma aan die einde van die sjabloon, want ons soek teks van "g." na 'n komma. Maar daar kan verskeie kommas in die teks wees, nie waar nie? Nie net na die stad nie, maar ook na die straat, huise, ens. Op watter van hulle sal ons versoek stop? Dit is waarvoor die vraagteken is. Daarsonder sou ons gereelde uitdrukking die langste moontlike tou uittrek:

In terme van gereelde uitdrukkings is so 'n patroon "gierig". Om die situasie reg te stel, is 'n vraagteken nodig - dit maak die kwantifiseerder waarna dit "suinig" staan ​​- en ons navraag neem die teks net tot by die eerste tellerkomma na "g.":

Lêernaam vanaf volle pad

Nog 'n baie algemene situasie is om die lêernaam uit die volledige pad te onttrek. 'n Eenvoudige gereelde uitdrukking van die vorm sal hier help:

Die truuk hier is dat die soektog in werklikheid in die teenoorgestelde rigting plaasvind – van die einde tot die begin, want aan die einde van ons sjabloon is $, en ons soek alles voor dit tot by die eerste skuinsstreep van regs af. Die agterste skuinsstreep word ontsnap, soos die punt in die vorige voorbeeld.

PS

"Towards the end" Ek wil dit duidelik maak dat al die bogenoemde 'n klein deel is van al die moontlikhede wat gereelde uitdrukkings bied. Daar is baie spesiale karakters en reëls vir hul gebruik, en hele boeke is oor hierdie onderwerp geskryf (ek beveel ten minste hierdie een vir 'n begin aan). Op 'n manier is die skryf van gereelde uitdrukkings amper 'n kuns. Byna altyd kan 'n uitgevind gereelde uitdrukking verbeter of aangevul word, wat dit meer elegant maak of in staat is om met 'n wyer reeks invoerdata te werk.

Om ander mense se gereelde uitdrukkings te ontleed en te ontleed of jou eie te ontfout, is daar verskeie gerieflike aanlyn dienste: RegEx101, RegExr en nog baie meer

Ongelukkig word nie al die kenmerke van klassieke gewone uitdrukkings in VBA ondersteun nie (byvoorbeeld omgekeerde soektog of POSIX-klasse) en kan dit met Cyrillies werk, maar ek dink dat wat daar is, genoeg is vir die eerste keer om jou tevrede te stel.

As jy nie nuut is in die onderwerp nie, en jy het iets om te deel, laat gereelde uitdrukkings nuttig wanneer jy in Excel werk in die kommentaar hieronder. Een verstand is goed, maar twee stewels is 'n paar!

• Vervang en skoonmaak van teks met die SUBSTITUT-funksie
• Soek en uitlig van Latynse karakters in teks
• Soek vir die naaste soortgelyke teks (Ivanov = Ivonov = Ivanof, ens.)