Inhoud
Een van de meest tijdrovende en frustrerende taken bij het werken met tekst in Excel is: parsing – het ontleden van de alfanumerieke "pap" in componenten en het extraheren van de fragmenten die we nodig hebben. Bijvoorbeeld:
- de postcode uit het adres halen (het is goed als de postcode altijd aan het begin staat, maar wat als dat niet het geval is?)
- het nummer en de datum van de factuur opzoeken in de omschrijving van de betaling op het bankafschrift
- extractie van FIN uit bonte beschrijvingen van bedrijven in de lijst van tegenpartijen
- zoek een autonummer of artikelnummer in de omschrijving, etc.
Meestal beginnen in dergelijke gevallen, na een half uur lang somber handmatig in de tekst plukken, de gedachten op te komen om dit proces op de een of andere manier te automatiseren (vooral als er veel gegevens zijn). Er zijn verschillende oplossingen en met verschillende gradaties van complexiteit-efficiëntie:
- Te gebruiken ingebouwde Excel-tekstfuncties om tekst te zoeken-cut-lijm: LEVSIMV (LINKS), RECHTS (RECHTSAF), PSTR (midden), STsEPIT (CONCATENAAT) en zijn analogen, COMBINEREN (GEZAMENLIJKE TEKST), EXACT (EXACT) etc. Deze methode is goed als er een duidelijke logica in de tekst zit (de index staat bijvoorbeeld altijd aan het begin van het adres). Anders worden de formules veel gecompliceerder en soms komt het zelfs tot matrixformules, die bij grote tabellen enorm vertragen.
- gebruik like-operator voor tekstovereenkomst van Visual Basic verpakt in een aangepaste macrofunctie. Hierdoor kunt u flexibeler zoeken met jokertekens (*, #,?, enz.). Helaas kan deze tool de gewenste substring niet uit de tekst halen - controleer alleen of deze erin voorkomt.
Naast het bovenstaande is er nog een andere benadering die zeer goed bekend is in nauwe kringen van professionele programmeurs, webontwikkelaars en andere techneuten - dit is normale uitdrukkingen (Reguliere expressies = RegExp = "regexps" = "regulars"). Simpel gezegd, RegExp is een taal waarin speciale tekens en regels worden gebruikt om naar de benodigde substrings in de tekst te zoeken, deze te extraheren of te vervangen door andere tekst. Reguliere expressies zijn een zeer krachtige en mooie tool die alle andere manieren om met tekst te werken met een orde van grootte overtreft. Veel programmeertalen (C#, PHP, Perl, JavaScript…) en teksteditors (Word, Notepad++…) ondersteunen reguliere expressies.
Microsoft Excel heeft helaas standaard geen RegExp-ondersteuning, maar dit kan eenvoudig worden opgelost met VBA. Open de Visual Basic Editor vanaf het tabblad ontwikkelaar (Ontwikkelaar) of sneltoets anders+F11. Voeg vervolgens de nieuwe module in via het menu Invoegen – Module en kopieer daar de tekst van de volgende macrofunctie:
Openbare functie RegExpExtract(Tekst als tekenreeks, patroon als tekenreeks, optioneel item als geheel getal = 1) Als tekenreeks bij fout GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Patroon regex.Global = True If regex.Test (Tekst) Dan Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Functie beëindigen End If ErrHandl: RegExpExtract = CVErr(xlErrValue) Functie beëindigen We kunnen nu de Visual Basic Editor sluiten en terugkeren naar Excel om onze nieuwe functie uit te proberen. De syntaxis is de volgende:
=RegExpExtract(Txt; Patroon; Item)
WAAR
- txt – een cel met de tekst die we aan het controleren zijn en waaruit we de substring willen extraheren die we nodig hebben
- patroon – masker (patroon) voor zoeken op substring
- item – het volgnummer van de substring die moet worden geëxtraheerd, als er meerdere zijn (indien niet gespecificeerd, wordt de eerste instantie weergegeven)
Het meest interessante hier is natuurlijk Pattern - een sjabloonreeks van speciale tekens "in de taal" van RegExp, die specificeert wat en waar we precies willen vinden. Hier zijn de meest elementaire om u op weg te helpen:
| Patronen | Omschrijving |
| . | De eenvoudigste is een punt. Het komt overeen met elk teken in het patroon op de opgegeven positie. |
| s | Elk teken dat eruitziet als een spatie (spatie, tab of regeleinde). |
| S | Een anti-variant van het vorige patroon, dwz elk niet-witruimteteken. |
| d | Elk nummer |
| D | Een anti-variant van de vorige, dwz een NIET-cijfer |
| w | Elk Latijns teken (AZ), cijfer of onderstrepingsteken |
| W | Een anti-variant van de vorige, dus niet Latijn, geen cijfer en geen onderstrepingsteken. |
| [tekens] | Tussen vierkante haken kunt u een of meer tekens opgeven die zijn toegestaan op de opgegeven positie in de tekst. Bijvoorbeeld Kunst komt overeen met een van de woorden: tafel or stoel. U kunt tekens ook niet opsommen, maar instellen als een bereik gescheiden door een koppelteken, dwz in plaats van [ABDCDEF] schrijven [AF]. of in plaats daarvan [4567] voorstellen [-4 7]. Om bijvoorbeeld alle Cyrillische tekens aan te duiden, kunt u de sjabloon . gebruiken [a-yaA-YayoYo]. |
| [^tekens] | Als na de opening vierkante haakjes het symbool "deksel" worden toegevoegd ^, dan krijgt de set de tegenovergestelde betekenis - op de opgegeven positie in de tekst zijn alle tekens toegestaan, behalve de vermelde. Ja, sjabloon [^ЖМ]u zal vinden Pad or Stof or vergeten, Maar niet Eng or Mut, bijv. |
| | | Booleaanse operator OR (OR) om te controleren op een van de opgegeven criteria. Bijvoorbeeld (metdo|seven|factuur) zal de tekst doorzoeken op een van de opgegeven woorden. Meestal staat een reeks opties tussen haakjes. |
| ^ | Begin van regel |
| $ | Einde van de lijn |
| b | Einde van het woord |
Als we op zoek zijn naar een bepaald aantal tekens, bijvoorbeeld een zescijferige postcode of alle drieletterige productcodes, dan komen we te hulp kwantoren or kwantoren zijn speciale uitdrukkingen die het aantal te doorzoeken tekens specificeren. Quantifiers worden toegepast op het teken dat ervoor komt:
| Quantor | Omschrijving |
| ? | Nul of één voorval. Bijvoorbeeld .? zal een teken of de afwezigheid ervan betekenen. |
| + | Een of meer vermeldingen. Bijvoorbeeld d+ betekent een willekeurig aantal cijfers (dwz een willekeurig getal tussen 0 en oneindig). |
| * | Nul of meer gebeurtenissen, dwz elke hoeveelheid. Dus s* betekent een willekeurig aantal spaties of geen spaties. |
| {aantal} or {number1,number2} | Als u een strikt gedefinieerd aantal keren moet opgeven, wordt dit tussen accolades opgegeven. Bijvoorbeeld d{6} betekent strikt zes cijfers, en het patroon s{2,5} – twee tot vijf spaties |
Laten we nu verder gaan met het meest interessante deel - een analyse van de toepassing van de gecreëerde functie en wat we hebben geleerd over patronen op praktische voorbeelden uit het leven.
Cijfers uit tekst halen
Laten we om te beginnen een eenvoudig geval analyseren - u moet het eerste cijfer uit alfanumerieke pap halen, bijvoorbeeld de kracht van ononderbroken voedingen uit de prijslijst:
De logica achter de reguliere expressie is eenvoudig: d betekent een willekeurig cijfer, en de kwantor + zegt dat hun aantal een of meer moet zijn. De dubbele min voor de functie is nodig om "on the fly" de geëxtraheerde tekens om te zetten in een volledig getal uit het getal-als-tekst.
Postcode
Op het eerste gezicht is alles hier eenvoudig - we zoeken precies zes cijfers op een rij. We gebruiken een speciaal teken d voor cijfer en kwantor 6 {} voor het aantal karakters:
Er is echter een situatie mogelijk dat er links van de index in de regel nog een grote reeks cijfers op een rij staat (telefoonnummer, TIN, bankrekening, enz.) Dan trekt ons reguliere seizoen de eerste 6 cijfers ervan, dwz zal niet correct werken:
Om dit te voorkomen, moeten we een modifier toevoegen aan de randen van onze reguliere expressie b betekent het einde van een woord. Dit maakt het voor Excel duidelijk dat het fragment (index) dat we nodig hebben een apart woord moet zijn en geen onderdeel van een ander fragment (telefoonnummer):
Telefoonnummer
Het probleem met het vinden van een telefoonnummer in de tekst is dat er zoveel mogelijkheden zijn om nummers te schrijven - met en zonder koppeltekens, door spaties, met of zonder regiocode tussen haakjes, enz. Daarom is het naar mijn mening gemakkelijker om verwijder eerst al deze tekens uit de brontekst met behulp van verschillende geneste functies PLAATSVERVANGER (VERVANGING)zodat het aan elkaar plakt tot één geheel, en dan met een primitieve regular d{11} trek 11 cijfers achter elkaar uit:
ITN
Het is hier iets gecompliceerder, omdat TIN (in Ons Land) 10-cijferig (voor rechtspersonen) of 12-cijferig (voor particulieren) kan zijn. Als u geen fout vindt in het bijzonder, dan is het heel goed mogelijk om tevreden te zijn met de reguliere d{10,12}, maar strikt genomen haalt het alle cijfers van 10 tot 12 tekens eruit, dwz en per ongeluk 11 cijfers ingevoerd. Het zou juister zijn om twee patronen te gebruiken die verbonden zijn door een logische OR-operator | (verticale balk):
Houd er rekening mee dat we in de zoekopdracht eerst naar 12-bits getallen zoeken en pas daarna naar 10-bits getallen. Als we onze reguliere expressie andersom schrijven, zal het voor iedereen, zelfs lange 12-bits TIN's, alleen de eerste 10 tekens gebruiken. Dat wil zeggen, nadat de eerste voorwaarde is geactiveerd, wordt geen verdere verificatie meer uitgevoerd:
Dit is het fundamentele verschil tussen de operator: | van een standaard Excel logische functie OR (OR), waarbij het herschikken van de argumenten het resultaat niet verandert.
Product-SKU's
In veel bedrijven worden unieke identificatiecodes toegekend aan goederen en diensten - artikelen, SAP-codes, SKU's, enz. Als er logica in hun notatie zit, kunnen ze eenvoudig uit elke tekst worden gehaald met behulp van reguliere expressies. Als we bijvoorbeeld weten dat onze artikelen altijd uit drie Engelse hoofdletters, een koppelteken en een volgend driecijferig nummer bestaan, dan:
De logica achter de sjabloon is eenvoudig. [AZ] – betekent alle hoofdletters van het Latijnse alfabet. De volgende kwantor 3 {} zegt dat het voor ons belangrijk is dat er precies drie van zulke letters zijn. Na het koppelteken wachten we op drie cijfers, dus voegen we aan het einde toe d{3}
Contante bedragen
Op een gelijkaardige manier als in de vorige paragraaf kan je ook prijzen (kosten, btw …) uit de omschrijving van goederen halen. Als geldbedragen bijvoorbeeld worden aangegeven met een koppelteken, dan:
Patronen d met kwantor + zoekt naar een willekeurig getal tot aan een koppelteken, en d{2} zoekt daarna naar centen (twee cijfers).
Als u geen prijzen maar btw wilt extraheren, kunt u het derde optionele argument van onze RegExpExtract-functie gebruiken, die het volgnummer specificeert van het element dat moet worden geëxtraheerd. En natuurlijk kun je de functie vervangen PLAATSVERVANGER (VERVANGING) koppel in de resultaten af naar het standaard decimaalteken en voeg aan het begin een dubbele min toe zodat Excel de gevonden btw als een normaal getal interpreteert:
Nummers van auto's
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:
Tijd
Om de tijd in het HH:MM-formaat te extraheren, is de volgende reguliere expressie geschikt:
Na dubbele punt fragment [0-5]d, zoals het gemakkelijk te achterhalen is, stelt elk nummer in het bereik 00-59 in. Voor de dubbele punt tussen haakjes werken twee patronen, gescheiden door een logische OR (pijp):
- [0-1]d - elk nummer in het bereik 00-19
- 2[0-3] - elk nummer in het bereik 20-23
Op het verkregen resultaat kunt u bovendien de standaard Excel-functie toepassen TIJD (TEAM)om het om te zetten in een voor het programma begrijpelijke tijdsindeling en geschikt voor verdere berekeningen.
Wachtwoordcontrole
Stel dat we de lijst met door gebruikers bedachte wachtwoorden moeten controleren op juistheid. Volgens onze regels mogen wachtwoorden alleen Engelse letters (kleine letters of hoofdletters) en cijfers bevatten. Spaties, onderstrepingstekens en andere leestekens zijn niet toegestaan.
Het controleren kan worden georganiseerd met behulp van de volgende eenvoudige reguliere expressie:
In feite vereisen we met een dergelijk patroon dat tussen het begin (^) en einde ($) in onze tekst waren er alleen tekens uit de set tussen vierkante haken. Als u ook de lengte van het wachtwoord moet controleren (bijvoorbeeld ten minste 6 tekens), dan is de kwantor + kan worden vervangen door het interval "zes of meer" in de vorm {6,}:
Plaats van adres
Laten we zeggen dat we de stad uit de adresbalk moeten halen. Het reguliere programma zal helpen door de tekst uit "g" te extraheren. naar de volgende komma:
Laten we dit patroon eens nader bekijken.
Als je bovenstaande tekst hebt gelezen, dan heb je al begrepen dat sommige karakters in reguliere expressies (punten, sterretjes, dollartekens, etc.) een speciale betekenis hebben. Als u deze tekens zelf moet zoeken, worden ze voorafgegaan door een backslash (soms ook wel afscherming). Daarom, bij het zoeken naar het fragment "g." we moeten in reguliere expressie schrijven De heer als we op zoek zijn naar een plus, dan + enz.
De volgende twee karakters in onze sjabloon, de punt en de quantifier asterisk, staan voor een willekeurig aantal karakters, dwz een willekeurige plaatsnaam.
Er staat een komma aan het einde van de sjabloon, omdat we op zoek zijn naar tekst van 'g'. naar een komma. Maar er kunnen meerdere komma's in de tekst staan, toch? Niet alleen na de stad, maar ook na de straat, huizen, etc. Op welke daarvan zal ons verzoek stoppen? Daar is het vraagteken voor. Zonder dit zou onze reguliere expressie de langst mogelijke reeks eruit halen:
In termen van reguliere expressies is zo'n patroon "hebzuchtig". Om de situatie te corrigeren, is een vraagteken nodig - het maakt de kwantor waarna het "gierig" staat - en onze vraag brengt de tekst alleen tot de eerste tegenkomma na "g.":
Bestandsnaam van volledig pad
Een andere veel voorkomende situatie is om de bestandsnaam uit het volledige pad te extraheren. Een eenvoudige reguliere expressie van het formulier helpt hier:
De truc hier is dat de zoekopdracht in feite in de tegenovergestelde richting plaatsvindt - van het einde naar het begin, omdat aan het einde van onze sjabloon $, en we zoeken alles ervoor tot aan de eerste backslash van rechts. De backslash is een escapeteken, net als de punt in het vorige voorbeeld.
PS
“Towards the end” Ik wil duidelijk maken dat al het bovenstaande een klein deel is van alle mogelijkheden die reguliere expressies bieden. Er zijn veel speciale tekens en regels voor het gebruik ervan, en er zijn hele boeken over dit onderwerp geschreven (ik raad in ieder geval deze aan om te beginnen). In zekere zin is het schrijven van reguliere expressies bijna een kunst. Bijna altijd kan een uitgevonden reguliere expressie worden verbeterd of aangevuld, waardoor deze eleganter wordt of met een breder scala aan invoergegevens kan werken.
Om de reguliere expressies van andere mensen te analyseren en te ontleden of om uw eigen expressies te debuggen, zijn er verschillende handige online services: RegEx101, RegExr en meer
Helaas worden niet alle functies van klassieke reguliere expressies ondersteund in VBA (bijvoorbeeld reverse search of POSIX-klassen) en kunnen ze werken met Cyrillisch, maar ik denk dat wat er is genoeg is voor de eerste keer om je te plezieren.
Als je niet nieuw bent in het onderwerp en je hebt iets te delen, laat dan in de onderstaande opmerkingen reguliere expressies die nuttig zijn bij het werken in Excel. Eén geest is goed, maar twee laarzen zijn een paar!
- Tekst vervangen en opschonen met de SUBSTITUTE-functie
- Search and highlighting of Latin characters in text
- Zoek naar de dichtstbijzijnde vergelijkbare tekst (Ivanov = Ivonov = Ivanof, etc.)