Parse tekst med regulære udtryk (RegExp) i Excel

En af de mest tidskrævende og frustrerende opgaver, når man arbejder med tekst i Excel er parsing – parsing af den alfanumeriske "grød" til komponenter og udvinding af de fragmenter, vi har brug for, fra den. For eksempel:

• udtrækning af postnummeret fra adressen (det er godt, hvis postnummeret altid er i begyndelsen, men hvad hvis det ikke er det?)
• at finde nummer og dato på fakturaen ud fra beskrivelsen af ​​betalingen på kontoudtoget
• udtræk af TIN fra brogede beskrivelser af virksomheder på listen over modparter
• søg på bilnummer eller artikelnummer i beskrivelsen mv.
  

Normalt i sådanne tilfælde, efter en halv times kedelig plukning af teksten manuelt, begynder tanker at komme op på en eller anden måde for at automatisere denne proces (især hvis der er mange data). Der er flere løsninger og med varierende grader af kompleksitetseffektivitet:

• Brug indbyggede Excel-tekstfunktioner for at søge-cut-lim tekst: LEVSIMV (VENSTRE), HØJRE (RET), PSTR (midt), STsEPIT (SAMMENHÆNG) og dets analoger, FORENE (FINDETEKST), EKSAKT (PRÆCIS) osv. Denne metode er god, hvis der er en klar logik i teksten (for eksempel er indekset altid i begyndelsen af ​​adressen). Ellers bliver formlerne meget mere komplicerede, og nogle gange kommer det endda til matrixformler, som i høj grad sænker farten på store tabeller.
• Ved brug af som tekstlighedsoperator fra Visual Basic pakket ind i en brugerdefineret makrofunktion. Dette giver dig mulighed for at implementere en mere fleksibel søgning ved hjælp af jokertegn (*, #,? osv.) Desværre kan dette værktøj ikke udtrække den ønskede understreng fra teksten - tjek kun om den er indeholdt i den.

Ud over ovenstående er der en anden tilgang, som er meget kendt i snævre kredse af professionelle programmører, webudviklere og andre teknologer – dette er regulære udtryk (Regulære udtryk = RegExp = "regexps" = "regulære"). Kort fortalt, RegExp er et sprog, hvor specialtegn og regler bruges til at søge efter de nødvendige understrenge i teksten, udtrække dem eller erstatte dem med anden tekst. Regulære udtryk er et meget kraftfuldt og smukt værktøj, der overgår alle andre måder at arbejde med tekst på i en størrelsesorden. Mange programmeringssprog (C#, PHP, Perl, JavaScript...) og teksteditorer (Word, Notepad++...) understøtter regulære udtryk.

Microsoft Excel har desværre ikke RegExp-understøttelse ud af boksen, men dette kan nemt rettes med VBA. Åbn Visual Basic Editor fra fanen developer (Udvikler) eller tastaturgenvej andre+F11. Indsæt derefter det nye modul gennem menuen Indsæt – Modul og kopier teksten til følgende makrofunktion der:

Offentlig funktion RegExpExtract(Tekst som streng, mønster som streng, valgfrit element som heltal = 1) Som streng ved fejl GoTo ErrHandl Indstil regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Vi kan nu lukke Visual Basic Editor og vende tilbage til Excel for at prøve vores nye funktion. Dens syntaks er følgende:

=RegExpExtract( Txt ; Pattern ; Item )

hvor

• txt – en celle med den tekst, vi tjekker, og hvorfra vi vil udtrække den understreng, vi skal bruge
• mønster – maske (mønster) til understrengssøgning
• Vare – sekvensnummeret på den understreng, der skal udtrækkes, hvis der er flere af dem (hvis ikke angivet, vises den første forekomst)

Det mest interessante her er selvfølgelig Pattern - en skabelonstreng af specialtegn "på sproget" af RegExp, som specificerer præcis, hvad og hvor vi vil finde. Her er de mest grundlæggende for at komme i gang:

Hvis vi leder efter et bestemt antal tegn, for eksempel et sekscifret postnummer eller alle tre-bogstavs produktkoder, så kommer vi til undsætning kvantificatorer or kvantificatorer er specielle udtryk, der angiver antallet af tegn, der skal søges efter. Kvantifikatorer anvendes på det tegn, der kommer før det:

Lad os nu gå videre til den mest interessante del - en analyse af anvendelsen af ​​den oprettede funktion og hvad vi lærte om mønstre på praktiske eksempler fra livet.

Udtræk tal fra tekst

Til at begynde med, lad os analysere en simpel sag - du skal udtrække det første tal fra alfanumerisk grød, for eksempel kraften fra uafbrydelige strømforsyninger fra prislisten:

Logikken bag det regulære udtryk er enkel: d betyder ethvert ciffer, og kvantifieren + siger, at deres nummer skal være et eller flere. Det dobbelte minus foran funktionen er nødvendigt for at "on the fly" konvertere de udtrukne tegn til et fuldt tal fra nummer-som-tekst.

postnummer

Ved første øjekast er alt simpelt her - vi leder efter præcis seks cifre i træk. Vi bruger en speciel karakter d for ciffer og kvantifier 6 {} for antallet af tegn:

En situation er dog mulig, når der til venstre for indekset i linjen er endnu et stort sæt tal i rækken (telefonnummer, TIN, bankkonto osv.) Så trækker vores ordinære sæson de første 6 ud. cifre fra det, dvs. vil ikke fungere korrekt:

For at forhindre dette i at ske, skal vi tilføje en modifikator rundt om kanterne af vores regulære udtryk b betegner slutningen af ​​et ord. Dette vil gøre det klart for Excel, at det fragment (indeks), vi skal bruge, skal være et separat ord og ikke en del af et andet fragment (telefonnummer):

Telefon

Problemet med at finde et telefonnummer i teksten er, at der er så mange muligheder for at skrive tal – med og uden bindestreger, gennem mellemrum, med eller uden regionskode i parentes osv. Derfor er det efter min mening nemmere at ryd først ud af alle disse tegn fra kildeteksten ved hjælp af flere indlejrede funktioner ERSTATNING (ERSTATNING)så det klæber sammen til en enkelt helhed, og så med en primitiv regulær d{11} træk 11 cifre ud i træk:

ITN

Det er lidt mere kompliceret her, fordi TIN (i vores land) kan være 10-cifret (for juridiske enheder) eller 12-cifret (for enkeltpersoner). Hvis du ikke finder fejl især, så er det sagtens muligt at være tilfreds med den almindelige d{10,12}, men strengt taget trækker den alle tal fra 10 til 12 tegn ud, dvs. og indtaster fejlagtigt 11 cifre. Det ville være mere korrekt at bruge to mønstre forbundet med en logisk ELLER-operator | (lodret bjælke):

Bemærk venligst, at vi i forespørgslen først kigger efter 12-bit tal, og først derefter efter 10-bit tal. Hvis vi skriver vores regulære udtryk den anden vej rundt, så trækker det ud for alle, selv lange 12-bit TIN'er, kun de første 10 tegn. Det vil sige, efter at den første betingelse er udløst, udføres yderligere verifikation ikke længere:

Dette er den grundlæggende forskel mellem operatøren | fra en standard excel-logikfunktion OR (OR), hvor omarrangering af argumenterne ikke ændrer resultatet.

Produkt-SKU'er

I mange virksomheder tildeles unikke identifikatorer til varer og tjenester – artikler, SAP-koder, SKU'er osv. Hvis der er logik i deres notation, så kan de nemt trækkes ud af enhver tekst ved hjælp af regulære udtryk. For eksempel, hvis vi ved, at vores artikler altid består af tre store engelske bogstaver, en bindestreg og et efterfølgende trecifret tal, så:

Logikken bag skabelonen er enkel. [AZ] – betyder alle store bogstaver i det latinske alfabet. Den næste kvantifier 3 {} siger, at det er vigtigt for os, at der er præcis tre sådanne bogstaver. Efter bindestregen venter vi på tre cifre, så vi tilføjer til sidst d{3}

Kontantbeløb

På samme måde som i det foregående afsnit kan du også trække priser (omkostninger, moms …) ud af varebeskrivelsen. Hvis pengebeløb f.eks. er angivet med en bindestreg, så:

Mønster d med kvantifier + søger efter et hvilket som helst tal op til en bindestreg, og d{2} vil lede efter øre (to cifre) efter.

Hvis du ikke skal udtrække priser, men moms, så kan du bruge det tredje valgfrie argument i vores RegExpExtract-funktion, som angiver ordensnummeret på det element, der skal udtrækkes. Og du kan selvfølgelig erstatte funktionen ERSTATNING (ERSTATNING) i resultaterne skal du bindestreg til standard decimalseparatoren og tilføje et dobbelt minus i begyndelsen, så Excel fortolker den fundne moms som et normalt tal:

Bilens pladenumre

If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:

Tid

For at udtrække tiden i TT:MM-formatet er følgende regulære udtryk velegnet:

Efter tyktarmsfragment [0-5]d, som det er nemt at finde ud af, sætter et hvilket som helst tal i området 00-59. Før tyktarmen i parentes virker to mønstre adskilt af et logisk ELLER (rør):

• [0-1]d – ethvert tal i intervallet 00-19
• 2[0-3] – ethvert tal i intervallet 20-23

Til det opnåede resultat kan du desuden anvende standard Excel-funktionen TIME (HOLD)at konvertere det til et tidsformat, der er forståeligt for programmet og egnet til yderligere beregninger.

Kodeordskontrol

Antag, at vi skal tjekke listen over adgangskoder opfundet af brugere for korrekthed. I henhold til vores regler må adgangskoder kun indeholde engelske bogstaver (små eller store bogstaver) og tal. Mellemrum, understregninger og andre tegnsætningstegn er ikke tilladt.

Kontrol kan organiseres ved hjælp af følgende simple regulære udtryk:

Faktisk kræver et sådant mønster, at mellem begyndelsen (^) og afslutte ($) i vores tekst var der kun tegn fra sættet angivet i firkantede parenteser. Hvis du også skal kontrollere længden af ​​adgangskoden (f.eks. mindst 6 tegn), så skal kvantifieren + kan erstattes af intervallet "seks eller mere" i formularen {6,}:

By fra adresse

Lad os sige, at vi skal trække byen fra adresselinjen. Det almindelige program hjælper ved at udtrække teksten fra "g." til næste komma:

Lad os se nærmere på dette mønster.

Hvis du har læst ovenstående tekst, så har du allerede forstået, at nogle tegn i regulære udtryk (perioder, stjerner, dollartegn osv.) har en særlig betydning. Hvis du har brug for at lede efter disse karakterer selv, så er de forudgået af en skråstreg (nogle gange kaldet afskærmning). Derfor, når du søger efter fragmentet "g." vi skal skrive i regulært udtryk Hr. hvis vi leder efter et plus, så + etc.

De næste to tegn i vores skabelon, prikken og kvantificeringsstjernen, står for et hvilket som helst antal af alle tegn, dvs. et hvilket som helst bynavn.

Der er et komma i slutningen af ​​skabelonen, fordi vi leder efter tekst fra "g." til et komma. Men der kan være flere kommaer i teksten, ikke? Ikke kun efter byen, men også efter gaden, huse osv. På hvilken af ​​dem vil vores anmodning stoppe? Det er det, spørgsmålstegnet er til. Uden det ville vores regulære udtryk trække den længste streng ud:

Med hensyn til regulære udtryk er et sådant mønster "grådigt". For at rette op på situationen er der brug for et spørgsmålstegn – det gør kvantifieren, hvorefter den står “nærlig” – og vores forespørgsel fører kun teksten op til det første tællerkomma efter “g.”:

Filnavn fra fuld sti

En anden meget almindelig situation er at udtrække filnavnet fra den fulde sti. Et simpelt regulært udtryk af formularen vil hjælpe her:

Tricket her er, at søgningen i virkeligheden sker i den modsatte retning – fra slutningen til begyndelsen, for i slutningen af ​​vores skabelon er $, og vi leder efter alt før det op til den første skråstreg fra højre. Omvendt skråstreg er escaped, ligesom prikken i det foregående eksempel.

PS

"Mod slutningen" Jeg vil gerne præcisere, at alt det ovenstående er en lille del af alle de muligheder, regulære udtryk giver. Der er en masse specialtegn og regler for deres brug, og hele bøger er blevet skrevet om dette emne (jeg anbefaler i hvert fald denne til en start). På en måde er det nærmest en kunst at skrive regulære udtryk. Næsten altid kan et opfundet regulært udtryk forbedres eller suppleres, hvilket gør det mere elegant eller i stand til at arbejde med en bredere vifte af inputdata.

For at analysere og analysere andres regulære udtryk eller fejlfinde dine egne, er der flere praktiske onlinetjenester: RegEx101, RegExr og mere

Desværre er ikke alle funktionerne i klassiske regulære udtryk understøttet i VBA (for eksempel omvendt søgning eller POSIX-klasser) og kan fungere med kyrillisk, men jeg tror, ​​at det, der er, er nok for første gang til at behage dig.

Hvis du ikke er ny inden for emnet, og du har noget at dele, så lad regulære udtryk være nyttige, når du arbejder i Excel i kommentarerne nedenfor. Ét sind er godt, men to støvler er et par!

• Udskiftning og oprydning af tekst med SUBSTITUTE-funktionen
• Search and highlighting of Latin characters in text
• Søg efter den nærmeste lignende tekst (Ivanov = Ivonov = Ivanof osv.)