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

En av de mest tidkrevende og frustrerende oppgavene når du arbeider med tekst i Excel er parsing – analysere den alfanumeriske "grøten" til komponenter og trekke ut fragmentene vi trenger fra den. For eksempel:

• trekke ut postnummeret fra adressen (det er bra hvis postnummeret alltid er i begynnelsen, men hva om det ikke er det?)
• finne nummeret og datoen på fakturaen fra beskrivelsen av betalingen på kontoutskriften
• uttak av TIN fra brokete beskrivelser av selskaper i listen over motparter
• søk etter bilnummer eller artikkelnummer i beskrivelsen osv.
  

Vanligvis i slike tilfeller, etter en halvtimes kjedelig plukking av teksten manuelt, begynner tanker å dukke opp på en eller annen måte for å automatisere denne prosessen (spesielt hvis det er mye data). Det finnes flere løsninger og med varierende grad av kompleksitetseffektivitet:

• Bruk innebygde Excel-tekstfunksjoner for å søke-cut-lim tekst: LEVSIMV (VENSTRE), HØYRE (IKKE SANT), PSTR (midt), STEPIT (KONKATENE) og dets analoger, KOMBINERE (SAMLINGSTEKST), NØYAKTIG (NØYAKTIG) osv. Denne metoden er god hvis det er en klar logikk i teksten (for eksempel er indeksen alltid i begynnelsen av adressen). Ellers blir formlene mye mer kompliserte, og noen ganger kommer det til og med med matriseformler, noe som reduserer hastigheten betydelig på store tabeller.
• Ved hjelp av som tekstlikhetsoperator fra Visual Basic pakket inn i en tilpasset makrofunksjon. Dette lar deg implementere et mer fleksibelt søk ved å bruke jokertegn (*, #,? osv.) Dessverre kan ikke dette verktøyet trekke ut den ønskede understrengen fra teksten – sjekk bare om den finnes i den.

I tillegg til det ovennevnte er det en annen tilnærming som er veldig godt kjent i trange kretser av profesjonelle programmerere, webutviklere og andre teknologer – dette er vanlig uttrykk (Regulære uttrykk = RegExp = "regexps" = "regulære"). For å si det enkelt, RegExp er et språk der spesialtegn og regler brukes til å søke etter de nødvendige understrengene i teksten, trekke dem ut eller erstatte dem med annen tekst. Regulære uttrykk er et veldig kraftig og vakkert verktøy som overgår alle andre måter å jobbe med tekst på med en størrelsesorden. Mange programmeringsspråk (C#, PHP, Perl, JavaScript...) og tekstredigerere (Word, Notepad++...) støtter regulære uttrykk.

Microsoft Excel har dessverre ikke RegExp-støtte ut av esken, men dette kan enkelt fikses med VBA. Åpne Visual Basic Editor fra fanen utvikler (Utvikler) eller hurtigtast andre+F11. Sett deretter inn den nye modulen gjennom menyen Sett inn – modul og kopier teksten til følgende makrofunksjon dit:

Offentlig funksjon RegExpExtract(Tekst som streng, mønster som streng, valgfritt element som heltall = 1) Som streng ved feil GoTo ErrHandl Sett 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 nå lukke Visual Basic Editor og gå tilbake til Excel for å prøve ut vår nye funksjon. Syntaksen er som følger:

=RegExpExtract( Txt ; Pattern ; Item )

hvor

• txt – en celle med teksten som vi sjekker og som vi ønsker å trekke ut delstrengen vi trenger fra
• mønster – maske (mønster) for understrengsøk
• Sak – sekvensnummeret til understrengen som skal trekkes ut, hvis det er flere av dem (hvis ikke spesifisert, vises den første forekomsten)

Det mest interessante her er selvfølgelig Pattern - en malstreng med spesialtegn "på språket" til RegExp, som spesifiserer nøyaktig hva og hvor vi ønsker å finne. Her er de mest grunnleggende for å komme i gang:

Hvis vi ser etter et visst antall tegn, for eksempel et sekssifret postnummer eller alle trebokstavs produktkoder, så kommer vi til unnsetning kvantifiserere or kvantifiserere er spesielle uttrykk som spesifiserer antall tegn som skal søkes. Kvantifiserere brukes på tegnet som kommer foran det:

La oss nå gå videre til den mest interessante delen - en analyse av anvendelsen av den opprettede funksjonen og hva vi lærte om mønstre på praktiske eksempler fra livet.

Trekker ut tall fra tekst

Til å begynne med, la oss analysere en enkel sak - du må trekke ut det første tallet fra alfanumerisk grøt, for eksempel kraften til avbruddsfri strømforsyning fra prislisten:

Logikken bak det regulære uttrykket er enkel: d betyr et hvilket som helst siffer, og kvantifisereren + sier at nummeret deres skal være ett eller flere. Det doble minuset foran funksjonen er nødvendig for å "i farten" konvertere de utpakkede tegnene til et fullstendig tall fra tallet-som-tekst.

Postnummer

Ved første øyekast er alt enkelt her – vi ser etter nøyaktig seks sifre på rad. Vi bruker et spesialtegn d for siffer og kvantifier 6 {} for antall tegn:

En situasjon er imidlertid mulig når det til venstre for indeksen i linjen er et annet stort sett med tall på rad (telefonnummer, TIN, bankkonto osv.) Da vil vår ordinære sesong trekke ut de første 6 sifre fra den, dvs. vil ikke fungere riktig:

For å forhindre at dette skjer, må vi legge til en modifikator rundt kantene på vårt regulære uttrykk b betyr slutten på et ord. Dette vil gjøre det klart for Excel at fragmentet (indeksen) vi trenger skal være et eget ord, og ikke en del av et annet fragment (telefonnummer):

Telefon

Problemet med å finne et telefonnummer i teksten er at det er så mange muligheter for å skrive tall – med og uten bindestreker, mellomrom, med eller uten regionkode i parentes osv. Derfor er det etter min mening lettere å rens først ut alle disse tegnene fra kildeteksten ved å bruke flere nestede funksjoner ERSTATNING (ERSTATNING)slik at det henger sammen til en enkelt helhet, og da med en primitiv regelmessig d{11} trekk ut 11 sifre på rad:

ITN

Det er litt mer komplisert her, fordi TIN (i vårt land) kan være 10-sifret (for juridiske personer) eller 12-sifret (for enkeltpersoner). Hvis du ikke finner feil spesielt, så er det fullt mulig å være fornøyd med det vanlige d{10,12}, men strengt tatt vil den trekke ut alle tall fra 10 til 12 tegn, dvs. og feilaktig tastet inn 11 sifre. Det ville være mer riktig å bruke to mønstre forbundet med en logisk ELLER-operator | (vertikal stolpe):

Vær oppmerksom på at i spørringen ser vi først etter 12-bits tall, og først deretter etter 10-biters tall. Hvis vi skriver vårt vanlige uttrykk omvendt, vil det trekke ut for alle, selv lange 12-bits TIN-er, bare de første 10 tegnene. Det vil si at etter at den første tilstanden er utløst, utføres ikke ytterligere verifisering lenger:

Dette er den grunnleggende forskjellen mellom operatøren | fra en standard excel-logikkfunksjon OR (ELLER), der omorganisering av argumentene ikke endrer resultatet.

Produkt-SKUer

I mange selskaper tildeles unike identifikatorer til varer og tjenester – artikler, SAP-koder, SKU-er, osv. Hvis det er logikk i notasjonen deres, kan de enkelt trekkes ut av tekst ved hjelp av regulære uttrykk. For eksempel, hvis vi vet at artiklene våre alltid består av tre store engelske bokstaver, en bindestrek og et påfølgende tresifret tall, så:

Logikken bak malen er enkel. [AZ] – betyr alle store bokstaver i det latinske alfabetet. Den neste kvantifisereren 3 {} sier at det er viktig for oss at det er akkurat tre slike bokstaver. Etter bindestreken venter vi på tre sifre, så vi legger til på slutten d{3}

Kontantbeløp

På samme måte som i forrige avsnitt kan du også trekke ut priser (kostnader, mva …) fra varebeskrivelsen. Hvis pengebeløp, for eksempel, er indikert med en bindestrek, så:

Mønster d med kvantifiserer + søker etter et hvilket som helst tall opp til en bindestrek, og d{2} vil se etter pennies (to sifre) etter.

Hvis du ikke trenger å trekke ut priser, men merverdiavgift, kan du bruke det tredje valgfrie argumentet til vår RegExpExtract-funksjon, som spesifiserer ordensnummeret til elementet som skal trekkes ut. Og du kan selvfølgelig erstatte funksjonen ERSTATNING (ERSTATNING) i resultatene, bind bindestrek til standard desimalskilletegn og legg til en dobbel minus i begynnelsen slik at Excel tolker funnet MVA som et normalt tall:

Bilskiltnummer

Hvis du ikke tar spesielle kjøretøy, tilhengere og andre motorsykler, analyseres standardbilnummeret i henhold til prinsippet "bokstav - tre tall - to bokstaver - regionskode". Dessuten kan regionkoden være 2- eller 3-sifret, og bare de som ligner på det latinske alfabetet brukes som bokstaver. Følgende regulære uttrykk vil derfor hjelpe oss å trekke ut tall fra teksten:

Tid

For å trekke ut tiden i HH:MM-formatet er følgende regulære uttrykk egnet:

Etter kolonfragment [0-5]d, som det er lett å finne ut av, setter et hvilket som helst tall i området 00-59. Før kolon i parentes fungerer to mønstre, atskilt med en logisk ELLER (rør):

• [0-1]d – et hvilket som helst tall i området 00-19
• 2[0-3] – et hvilket som helst tall i området 20-23

Til det oppnådde resultatet kan du i tillegg bruke standard Excel-funksjonen TIME (TEAM)å konvertere det til et tidsformat som er forståelig for programmet og egnet for videre beregninger.

Passordsjekk

Anta at vi må sjekke listen over passord oppfunnet av brukere for korrekthet. I henhold til våre regler kan passord bare inneholde engelske bokstaver (små eller store bokstaver) og tall. Mellomrom, understrek og andre skilletegn er ikke tillatt.

Kontroll kan organiseres ved hjelp av følgende enkle regulære uttrykk:

Faktisk, med et slikt mønster krever vi at mellom begynnelsen (^) og slutt ($) i teksten vår var det bare tegn fra settet oppgitt i hakeparenteser. Hvis du også trenger å sjekke lengden på passordet (for eksempel minst 6 tegn), så kvantifiserer + kan erstattes av intervallet "seks eller mer" i skjemaet {6,}:

By fra adresse

La oss si at vi må trekke byen fra adressefeltet. Det vanlige programmet vil hjelpe, trekke ut teksten fra "g." til neste komma:

La oss se nærmere på dette mønsteret.

Hvis du har lest teksten ovenfor, har du allerede forstått at noen tegn i regulære uttrykk (perioder, stjerner, dollartegn osv.) har en spesiell betydning. Hvis du trenger å lete etter disse karakterene selv, blir de innledet av en omvendt skråstrek (noen ganger kalt skjerming). Derfor, når du søker etter fragmentet "g." vi må skrive i regulært uttrykk MR. hvis vi ser etter et pluss, da + og så videre

De to neste tegnene i malen vår, prikken og kvantifisereren stjerne, står for et hvilket som helst antall tegn, dvs. hvilket som helst bynavn.

Det er et komma på slutten av malen, fordi vi ser etter tekst fra "g." til et komma. Men det kan være flere komma i teksten, ikke sant? Ikke bare etter byen, men også etter gaten, husene osv. På hvilken av dem vil vår forespørsel stoppe? Det er det spørsmålstegnet er for. Uten det ville vårt regulære uttrykk trekke ut den lengste strengen mulig:

Når det gjelder regulære uttrykk, er et slikt mønster "grådig". For å rette opp situasjonen er det nødvendig med et spørsmålstegn – det gjør kvantifisereren, hvoretter den blir "gjerrig" - og spørringen vår tar teksten bare opp til det første tellerkommaet etter "g.":

Filnavn fra full bane

En annen svært vanlig situasjon er å trekke ut filnavnet fra hele banen. Et enkelt regulært uttrykk for skjemaet vil hjelpe her:

Trikset her er at søket faktisk skjer i motsatt retning – fra slutten til begynnelsen, fordi på slutten av malen vår er $, og vi ser etter alt før det opp til første skråstrek fra høyre. Omvendt skråstrek er escaped, som prikken i forrige eksempel.

PS

«Mot slutten» Jeg ønsker å presisere at alt det ovennevnte er en liten del av alle mulighetene som regulære uttrykk gir. Det er mange spesialtegn og regler for deres bruk, og hele bøker er skrevet om dette emnet (jeg anbefaler i det minste denne til å begynne med). På en måte er det nesten en kunst å skrive regulære uttrykk. Nesten alltid kan et oppfunnet regulært uttrykk forbedres eller suppleres, noe som gjør det mer elegant eller i stand til å arbeide med et bredere spekter av inndata.

For å analysere og analysere andres vanlige uttrykk eller feilsøke dine egne, finnes det flere praktiske nettjenester: RegEx101, RegExr og mer

Dessverre er ikke alle funksjonene til klassiske regulære uttrykk støttet i VBA (for eksempel omvendt søk eller POSIX-klasser) og kan fungere med kyrillisk, men jeg tror at det som finnes er nok for første gang til å glede deg.

Hvis du ikke er ny i emnet, og du har noe å dele, la regulære uttrykk være nyttige når du arbeider i Excel i kommentarene nedenfor. Ett sinn er bra, men to støvler er et par!

• Erstatte og rydde opp i tekst med ERSTATT-funksjonen
• Søk og utheving av latinske tegn i tekst
• Søk etter den nærmeste lignende teksten (Ivanov = Ivonov = Ivanof, etc.)