Analysera text med reguljära uttryck (RegExp) i Excel

En av de mest tidskrävande och frustrerande uppgifterna när man arbetar med text i Excel är parsning – analysera den alfanumeriska "gröten" till komponenter och extrahera de fragment vi behöver från den. Till exempel:

• extrahera postnumret från adressen (det är bra om postnumret alltid är i början, men vad händer om det inte är det?)
• hitta numret och datumet på fakturan från beskrivningen av betalningen på kontoutdraget
• utdrag av TIN från brokiga beskrivningar av företag i listan över motparter
• sök efter bilnummer eller artikelnummer i beskrivningen osv.
  

Vanligtvis i sådana fall, efter en halvtimmes tråkigt plockande i texten manuellt, börjar tankar dyka upp på något sätt för att automatisera denna process (särskilt om det finns mycket data). Det finns flera lösningar och med varierande grad av komplexitetseffektivitet:

• Använda inbyggda Excel-textfunktioner för att söka-cut-lim text: LEVSIMV (VÄNSTER), RÄTT (RÄTT), PSTR (mitten), STsEPIT (KONKATERA) och dess analoger, KOMBINERA (SAMMANSTÄLLNING), EXAKT (EXAKT) etc. Denna metod är bra om det finns en tydlig logik i texten (t.ex. är index alltid i början av adressen). Annars blir formlerna mycket mer komplicerade och ibland handlar det till och med om arrayformler, vilket saktar ner kraftigt på stora tabeller.
• Använda som textlikhetsoperator från Visual Basic insvept i en anpassad makrofunktion. Detta gör att du kan implementera en mer flexibel sökning med hjälp av jokertecken (*, #,?, etc.) Tyvärr kan det här verktyget inte extrahera den önskade delsträngen från texten – kontrollera bara om den finns i den.

Utöver ovanstående finns det ett annat tillvägagångssätt som är mycket välkänt i snäva kretsar av professionella programmerare, webbutvecklare och andra tekniker – detta är vanliga uttryck (Reguljära uttryck = RegExp = “regexps” = “vanliga”). Enkelt uttryckt, RegExp är ett språk där specialtecken och regler används för att söka efter nödvändiga delsträngar i texten, extrahera dem eller ersätta dem med annan text. Reguljära uttryck är ett mycket kraftfullt och vackert verktyg som överträffar alla andra sätt att arbeta med text med en storleksordning. Många programmeringsspråk (C#, PHP, Perl, JavaScript...) och textredigerare (Word, Notepad++...) stöder reguljära uttryck.

Microsoft Excel har tyvärr inte RegExp-stöd direkt, men detta kan enkelt fixas med VBA. Öppna Visual Basic Editor från fliken utvecklare (Utvecklaren) eller kortkommandon andra+F11. Sätt sedan in den nya modulen genom menyn Infoga – Modul och kopiera texten till följande makrofunktion dit:

Offentlig funktion RegExpExtract(Text som sträng, mönster som sträng, valfritt objekt som heltal = 1) Som sträng vid fel GoTo ErrHandl Ange regex = CreateObject("VBScript.RegExp") regex.Pattern = Mönster 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 stänga Visual Basic Editor och återgå till Excel för att testa vår nya funktion. Dess syntax är följande:

=RegExpExtract( Txt ; Pattern ; Item )

var

• Text – en cell med texten som vi kontrollerar och från vilken vi vill extrahera den delsträng vi behöver
• mönster – mask (mönster) för delsträngsökning
• Artikel – sekvensnumret för delsträngen som ska extraheras, om det finns flera av dem (om det inte anges, visas den första förekomsten)

Det mest intressanta här är naturligtvis Pattern – en mallsträng med specialtecken "på språket" för RegExp, som anger exakt vad och var vi vill hitta. Här är de mest grundläggande för att komma igång:

Om vi ​​letar efter ett visst antal tecken, till exempel ett sexsiffrigt postnummer eller alla tre bokstäver produktkoder, kommer vi till undsättning kvantifierare or kvantifierare är speciella uttryck som anger antalet tecken som ska sökas. Kvantifierare tillämpas på tecknet som kommer före det:

Låt oss nu gå vidare till den mest intressanta delen - en analys av tillämpningen av den skapade funktionen och vad vi lärde oss om mönster på praktiska exempel från livet.

Extrahera siffror från text

Till att börja med, låt oss analysera ett enkelt fall - du måste extrahera det första numret från alfanumerisk gröt, till exempel kraften hos avbrottsfri strömförsörjning från prislistan:

Logiken bakom det reguljära uttrycket är enkel: d betyder vilken siffra som helst och kvantifieraren + säger att deras nummer ska vara ett eller flera. Det dubbla minuset framför funktionen behövs för att "i farten" konvertera de extraherade tecknen till ett helt tal från siffran-som-text.

Postnummer

Vid första anblicken är allt enkelt här – vi letar efter exakt sex siffror i rad. Vi använder en speciell karaktär d för siffra och kvantifierare 6 {} för antalet tecken:

En situation är dock möjlig när det till vänster om indexet i raden finns en annan stor uppsättning nummer i rad (telefonnummer, TIN, bankkonto, etc.) Då kommer vår ordinarie säsong att dra ut de första 6 siffror från den, dvs kommer inte att fungera korrekt:

För att förhindra att detta händer måste vi lägga till en modifierare runt kanterna på vårt reguljära uttryck b betecknar slutet på ett ord. Detta kommer att göra det klart för Excel att fragmentet (index) vi behöver ska vara ett separat ord och inte en del av ett annat fragment (telefonnummer):

Telefon

Problemet med att hitta ett telefonnummer i texten är att det finns så många alternativ för att skriva siffror – med och utan bindestreck, mellanslag, med eller utan regionskod inom parentes etc. Därför är det enligt min mening lättare att rensa först bort alla dessa tecken från källtexten med hjälp av flera kapslade funktioner ERSÄTTNING (ERSÄTTNING)så att det håller ihop till en enda helhet, och sedan med en primitiv regelbunden d{11} dra ut 11 siffror i rad:

ITN

Det är lite mer komplicerat här, eftersom TIN (i vårt land) kan vara 10-siffrigt (för juridiska personer) eller 12-siffrigt (för privatpersoner). Om du inte hittar fel speciellt, då är det fullt möjligt att vara nöjd med det vanliga d{10,12}, men strängt taget kommer den att dra ut alla siffror från 10 till 12 tecken, dvs. och felaktigt angivna 11 siffror. Det skulle vara mer korrekt att använda två mönster kopplade av en logisk ELLER-operator | (vertikal stapel):

Observera att i frågan letar vi först efter 12-bitars nummer och först sedan efter 10-bitars nummer. Om vi ​​skriver vårt reguljära uttryck tvärtom, kommer det att dra ut för alla, även långa 12-bitars TIN:er, bara de första 10 tecknen. Det vill säga, efter att det första villkoret har utlösts utförs inte längre verifiering:

Detta är den grundläggande skillnaden mellan operatören | från en vanlig excel-logikfunktion OR (ELLER), där omarrangering av argumenten inte ändrar resultatet.

Produkt-SKU: er

I många företag tilldelas unika identifierare till varor och tjänster – artiklar, SAP-koder, SKU:er etc. Om det finns logik i deras notation kan de enkelt dras ut ur vilken text som helst med hjälp av reguljära uttryck. Om vi ​​till exempel vet att våra artiklar alltid består av tre stora engelska bokstäver, ett bindestreck och ett efterföljande tresiffrigt nummer, då:

Logiken bakom mallen är enkel. [AZ] – betyder alla versaler i det latinska alfabetet. Nästa kvantifierare 3 {} säger att det är viktigt för oss att det finns exakt tre sådana bokstäver. Efter bindestrecket väntar vi på tre siffror, så vi lägger till i slutet d{3}

Kontantbelopp

På liknande sätt som i föregående stycke kan du också dra ut priser (kostnader, moms ...) från varubeskrivningen. Om monetära belopp, till exempel, anges med ett bindestreck, då:

Mönster d med kvantifierare + söker efter valfritt tal upp till ett bindestreck, och d{2} kommer att leta efter öre (två siffror) efter.

Om du behöver extrahera inte priser, utan moms, kan du använda det tredje valfria argumentet i vår RegExpExtract-funktion, som anger ordningsnumret för elementet som ska extraheras. Och självklart kan du byta ut funktionen ERSÄTTNING (ERSÄTTNING) i resultaten bindestreck till standarddecimalavgränsaren och lägg till ett dubbelt minus i början så att Excel tolkar den hittade momsen som ett normalt tal:

Bilskyltnummer

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

För att extrahera tiden i formatet HH:MM är följande reguljära uttryck lämpligt:

Efter kolonfragment [0-5]d, eftersom det är lätt att ta reda på, sätter valfritt tal i intervallet 00-59. Innan kolon inom parentes fungerar två mönster, åtskilda av ett logiskt ELLER (rör):

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

Till det erhållna resultatet kan du dessutom använda standardfunktionen Excel TID (TEAM)att konvertera det till ett tidsformat som är förståeligt för programmet och lämpligt för vidare beräkningar.

Lösenordskoll

Anta att vi måste kontrollera listan över lösenord som uppfunnits av användare för korrekthet. Enligt våra regler får lösenord endast innehålla engelska bokstäver (gemener eller versaler) och siffror. Mellanslag, understreck och andra skiljetecken är inte tillåtna.

Kontroll kan organiseras med hjälp av följande enkla reguljära uttryck:

Faktum är att med ett sådant mönster kräver vi att mellan början (^) och slut ($) i vår text fanns det bara tecken från uppsättningen inom hakparenteser. Om du också behöver kontrollera längden på lösenordet (till exempel minst 6 tecken), så måste kvantifieraren + kan ersättas med intervallet "sex eller fler" i formuläret {6,}:

Stad från adress

Låt oss säga att vi måste ta bort staden från adressfältet. Det vanliga programmet hjälper, extrahera texten från "g." till nästa kommatecken:

Låt oss ta en närmare titt på detta mönster.

Om du har läst texten ovan har du redan förstått att vissa tecken i reguljära uttryck (punkter, asterisker, dollartecken etc.) har en speciell betydelse. Om du behöver leta efter dessa karaktärer själva föregås de av ett snedstreck (kallas ibland avskärmning). Därför, när du söker efter fragmentet "g." vi måste skriva i reguljärt uttryck Herr. om vi letar efter ett plus, då + och så vidare

De följande två tecknen i vår mall, punkten och kvantifieraren asterisk, står för valfritt antal av alla tecken, dvs vilket stadnamn som helst.

Det finns ett kommatecken i slutet av mallen, eftersom vi letar efter text från "g." till ett kommatecken. Men det kan väl finnas flera kommatecken i texten? Inte bara efter staden, utan också efter gatan, husen etc. På vilken av dem kommer vår begäran att stanna? Det är vad frågetecknet är till för. Utan det skulle vårt reguljära uttryck dra ut den längsta möjliga strängen:

När det gäller reguljära uttryck är ett sådant mönster "girigt". För att rätta till situationen behövs ett frågetecken – det gör kvantifieraren varefter den blir ”snål” – och vår fråga tar bara texten upp till första räknarkomma efter ”g.”:

Filnamn från fullständig sökväg

En annan mycket vanlig situation är att extrahera filnamnet från den fullständiga sökvägen. Ett enkelt reguljärt uttryck av formuläret hjälper här:

Tricket här är att sökningen faktiskt sker i motsatt riktning – från slutet till början, eftersom i slutet av vår mall är $, och vi letar efter allt innan det upp till första snedstrecket från höger. Omvänt snedstreck är escaped, som punkten i föregående exempel.

PS

"Mot slutet" Jag vill förtydliga att allt ovanstående är en liten del av alla möjligheter som reguljära uttryck ger. Det finns många specialtecken och regler för deras användning, och hela böcker har skrivits om detta ämne (jag rekommenderar åtminstone den här till att börja med). På ett sätt är att skriva reguljära uttryck nästan en konst. Nästan alltid kan ett påhittat reguljärt uttryck förbättras eller kompletteras, vilket gör det mer elegant eller kan arbeta med ett bredare utbud av indata.

För att analysera och analysera andras reguljära uttryck eller felsöka dina egna finns det flera praktiska onlinetjänster: RegEx101, RegExr och mer

Tyvärr stöds inte alla funktioner i klassiska reguljära uttryck i VBA (till exempel omvänd sökning eller POSIX-klasser) och kan fungera med kyrilliska, men jag tror att det som finns räcker för första gången för att behaga dig.

Om du inte är ny i ämnet och har något att dela med dig av, lämna vanliga uttryck användbara när du arbetar i Excel i kommentarerna nedan. Ett sinne är bra, men två stövlar är ett par!

• Ersätta och rensa text med ERSÄTTNINGSfunktionen
• Search and highlighting of Latin characters in text
• Sök efter närmaste liknande text (Ivanov = Ivonov = Ivanof, etc.)