Analysieren Sie Text mit regulären Ausdrücken (RegExp) in Excel

Eine der zeitaufwändigsten und frustrierendsten Aufgaben bei der Arbeit mit Text in Excel ist Parsing – Zerlegen des alphanumerischen „Breis“ in Komponenten und Extrahieren der benötigten Fragmente daraus. Zum Beispiel:

• Extrahieren der Postleitzahl aus der Adresse (es ist gut, wenn die Postleitzahl immer am Anfang steht, aber was, wenn nicht?)
• Ermitteln der Nummer und des Datums der Rechnung aus der Beschreibung der Zahlung auf dem Kontoauszug
• Extraktion der TIN aus kunterbunten Firmenbezeichnungen in der Liste der Kontrahenten
• Suche nach einer Autonummer oder Artikelnummer in der Beschreibung etc.
  

Meist kommen in solchen Fällen nach einer halben Stunde mühsamen manuellen Herumklaubens im Text die Gedanken in den Sinn, diesen Prozess irgendwie zu automatisieren (besonders wenn es viele Daten gibt). Es gibt mehrere Lösungen und mit unterschiedlicher Komplexitätseffizienz:

• Verwenden Sie die eingebaute Excel-Textfunktionen zum Suchen-Schneiden-Kleben-Text: LEVSIMV (LINKS), RECHTS (RECHT), PSTR (Mitte), STsEPIT (VERKETTEN) und seine Analoga, KOMBINIEREN (GEMEINS.TEXT), GENAU (GENAU) usw. Diese Methode ist gut, wenn der Text eine klare Logik enthält (z. B. wenn der Index immer am Anfang der Adresse steht). Ansonsten werden die Formeln deutlich komplizierter und teilweise kommt es sogar zu Matrixformeln, was bei großen Tabellen stark ausbremst.
• Die richtigen wie Textähnlichkeitsoperator von Visual Basic, verpackt in einer benutzerdefinierten Makrofunktion. Dadurch können Sie eine flexiblere Suche mit Wildcard-Zeichen (*, #,?, etc.) realisieren. Leider kann dieses Tool den gewünschten Teilstring nicht aus dem Text extrahieren – prüfen Sie nur, ob er darin enthalten ist.

Darüber hinaus gibt es einen weiteren Ansatz, der in engen Kreisen von professionellen Programmierern, Webentwicklern und anderen Techies sehr bekannt ist – das ist reguläre Ausdrücke verwenden (Reguläre Ausdrücke = RegExp = „regexps“ = „regulars“). Einfach gesagt, RegExp ist eine Sprache, in der Sonderzeichen und Regeln verwendet werden, um nach den erforderlichen Teilzeichenfolgen im Text zu suchen, sie zu extrahieren oder durch anderen Text zu ersetzen. Reguläre Ausdrücke sind ein sehr mächtiges und schönes Werkzeug, das alle anderen Arten der Arbeit mit Text um eine Größenordnung übertrifft. Viele Programmiersprachen (C#, PHP, Perl, JavaScript…) und Texteditoren (Word, Notepad++…) unterstützen reguläre Ausdrücke.

Microsoft Excel hat leider keine RegExp-Unterstützung von Haus aus, aber das kann leicht mit VBA behoben werden. Öffnen Sie den Visual Basic-Editor über die Registerkarte Entwickler (Entwickler) oder Tastenkürzel Andere+F11. Fügen Sie dann das neue Modul über das Menü ein Einfügen – Modul und kopiere dort den Text der folgenden Makrofunktion:

Öffentliche Funktion RegExpExtract(Text als Zeichenfolge, Muster als Zeichenfolge, optionales Element als ganze Zahl = 1) als Zeichenfolge bei Fehler GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) Dann Set matchs = regex.Execute(Text) RegExpExtract = matchs.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Wir können jetzt den Visual Basic-Editor schließen und zu Excel zurückkehren, um unsere neue Funktion auszuprobieren. Seine Syntax ist die folgende:

=RegExpExtract( Txt ; Muster ; Artikel )

woher

• txt – eine Zelle mit dem Text, den wir überprüfen und aus dem wir den benötigten Teilstring extrahieren möchten
• Anleitungen – Maske (Muster) für die Teilstringsuche
• Artikel – die Sequenznummer des zu extrahierenden Teilstrings, wenn es mehrere davon gibt (wenn nicht angegeben, wird das erste Vorkommen angezeigt)

Am interessantesten ist hier natürlich Pattern – eine Musterfolge aus Sonderzeichen „in der Sprache“ von RegExp, die angibt, was genau und wo wir finden wollen. Hier sind die grundlegendsten, um Ihnen den Einstieg zu erleichtern:

Wenn wir nach einer bestimmten Anzahl von Zeichen suchen, zum Beispiel eine sechsstellige Postleitzahl oder alle dreistelligen Produktcodes, dann kommen wir zur Rettung Quantifizierer or Quantifizierer sind spezielle Ausdrücke, die die Anzahl der zu suchenden Zeichen angeben. Quantifizierer werden auf das vorangestellte Zeichen angewendet:

Kommen wir nun zum interessantesten Teil – einer Analyse der Anwendung der erstellten Funktion und was wir über Muster an praktischen Beispielen aus dem Leben gelernt haben.

Zahlen aus Text extrahieren

Lassen Sie uns zunächst einen einfachen Fall analysieren – Sie müssen die erste Zahl aus alphanumerischem Brei extrahieren, zum Beispiel die Leistung von unterbrechungsfreien Stromversorgungen aus der Preisliste:

Die Logik hinter dem regulären Ausdruck ist einfach: d bedeutet eine beliebige Ziffer, und der Quantor + sagt, dass ihre Anzahl eins oder mehr sein sollte. Das doppelte Minus vor der Funktion wird benötigt, um die extrahierten Zeichen „on the fly“ aus der Zahl-als-Text in eine vollständige Zahl umzuwandeln.

Postleitzahl

Auf den ersten Blick ist hier alles einfach – wir suchen genau sechs Ziffern hintereinander. Wir verwenden ein Sonderzeichen d für Ziffer und Quantor 6 {} für die Anzahl der Zeichen:

Es ist jedoch eine Situation möglich, in der links vom Index in der Zeile eine weitere große Anzahl von Zahlen in einer Reihe steht (Telefonnummer, TIN, Bankkonto usw.). Dann zieht unsere reguläre Saison die ersten 6 heraus Ziffern daraus, dh wird nicht richtig funktionieren:

Um dies zu verhindern, müssen wir an den Rändern unseres regulären Ausdrucks einen Modifikator hinzufügen b bedeutet das Ende eines Wortes. Dadurch wird Excel klar, dass das benötigte Fragment (Index) ein separates Wort und nicht Teil eines anderen Fragments (Telefonnummer) sein sollte:

Telefon

Das Problem beim Auffinden einer Telefonnummer im Text ist, dass es so viele Möglichkeiten gibt, Zahlen zu schreiben – mit und ohne Bindestrich, durch Leerzeichen, mit oder ohne Regionalcode in Klammern usw. Daher ist es meiner Meinung nach einfacher säubern Sie zuerst alle diese Zeichen aus dem Quelltext, indem Sie mehrere verschachtelte Funktionen verwenden ERSATZ (ERSATZ)so dass es zu einem einzigen Ganzen zusammenklebt, und dann mit einem primitiven regulären d{11} 11 Ziffern hintereinander herausziehen:

ITN

Hier ist es etwas komplizierter, da die TIN (in unserem Land) 10-stellig (für juristische Personen) oder 12-stellig (für natürliche Personen) sein kann. Wenn Sie besonders nichts auszusetzen haben, dann ist es durchaus möglich, mit dem Regelmäßigen zufrieden zu sein d{10,12}, aber streng genommen werden alle Zahlen von 10 bis 12 Zeichen herausgezogen, dh und fälschlicherweise 11 Ziffern eingegeben. Richtiger wäre es, zwei Muster zu verwenden, die durch einen logischen ODER-Operator verbunden sind | (senkrechter Balken):

Bitte beachten Sie, dass wir in der Abfrage zuerst nach 12-Bit-Zahlen suchen und erst dann nach 10-Bit-Zahlen. Wenn wir unseren regulären Ausdruck andersherum schreiben, dann werden für alle, auch lange 12-Bit-TINs, nur die ersten 10 Zeichen herausgezogen. Das heißt, nachdem die erste Bedingung ausgelöst wurde, wird keine weitere Überprüfung mehr durchgeführt:

Dies ist der grundlegende Unterschied zwischen dem Operator | aus einer standardmäßigen Excel-Logikfunktion OR (Oder), wobei die Neuanordnung der Argumente das Ergebnis nicht ändert.

Produkt-SKUs

In vielen Unternehmen werden Waren und Dienstleistungen eindeutige Identifikatoren zugewiesen – Artikel, SAP-Codes, SKUs usw. Wenn ihre Schreibweise logisch ist, können sie mit regulären Ausdrücken leicht aus jedem Text herausgezogen werden. Wenn wir beispielsweise wissen, dass unsere Artikel immer aus drei englischen Großbuchstaben, einem Bindestrich und einer anschließenden dreistelligen Zahl bestehen, dann gilt:

Die Logik hinter der Vorlage ist einfach. [AZ] – bedeutet alle Großbuchstaben des lateinischen Alphabets. Der nächste Quantifizierer 3 {} sagt, dass es für uns wichtig ist, dass es genau drei solcher Buchstaben gibt. Nach dem Bindestrich warten wir auf drei Ziffern, also fügen wir am Ende hinzu d{3}

Barbeträge

Ähnlich wie im vorigen Absatz können Sie auch Preise (Kosten, Mehrwertsteuer …) aus der Warenbeschreibung herausziehen. Werden z. B. Geldbeträge mit einem Bindestrich gekennzeichnet, dann gilt:

Schnittmuster d mit Quantifizierer + sucht nach einer beliebigen Zahl bis zu einem Bindestrich, und d{2} wird nach Pennys (zweistellig) suchen.

Wenn Sie nicht Preise, sondern die Mehrwertsteuer extrahieren müssen, können Sie das dritte optionale Argument unserer RegExpExtract-Funktion verwenden, das die Ordnungszahl des zu extrahierenden Elements angibt. Und natürlich können Sie die Funktion ersetzen ERSATZ (ERSATZ) Trennen Sie in den Ergebnissen das Standard-Dezimaltrennzeichen mit einem Bindestrich und fügen Sie am Anfang ein doppeltes Minus hinzu, damit Excel die gefundene Mehrwertsteuer als normale Zahl interpretiert:

Kfz-Kennzeichen

Wenn Sie keine Sonderfahrzeuge, Anhänger und andere Motorräder mitnehmen, wird die Standardwagennummer nach dem Prinzip „Buchstabe – drei Zahlen – zwei Buchstaben – Regionalcode“ analysiert. Darüber hinaus kann der Regionalcode zwei- oder dreistellig sein und als Buchstaben werden nur diejenigen verwendet, die im Aussehen dem lateinischen Alphabet ähneln. Daher hilft uns der folgende reguläre Ausdruck, Zahlen aus dem Text zu extrahieren:

Uhrzeit

Um die Uhrzeit im Format HH:MM zu extrahieren, eignet sich folgender regulärer Ausdruck:

Nach Doppelpunktfragment [0-5]d, wie leicht zu erkennen ist, legt eine beliebige Zahl im Bereich von 00 bis 59 fest. Vor dem Doppelpunkt in Klammern funktionieren zwei Muster, die durch ein logisches ODER (Pipe) getrennt sind:

• [0-1]d – eine beliebige Zahl im Bereich 00-19
• 2[0-3] – eine beliebige Zahl im Bereich 20-23

Auf das erhaltene Ergebnis können Sie zusätzlich die Standard-Excel-Funktion anwenden ZEIT- (MANNSCHAFT)in ein für das Programm verständliches und für weitere Berechnungen geeignetes Zeitformat umzuwandeln.

Passwortüberprüfung

Angenommen, wir müssen die Liste der von Benutzern erfundenen Passwörter auf Korrektheit überprüfen. Gemäß unseren Regeln dürfen Passwörter nur englische Buchstaben (Klein- oder Großbuchstaben) und Zahlen enthalten. Leerzeichen, Unterstriche und andere Satzzeichen sind nicht erlaubt.

Die Überprüfung kann mit dem folgenden einfachen regulären Ausdruck organisiert werden:

Tatsächlich verlangen wir bei einem solchen Muster, dass zwischen dem Anfang (^) und Ende ($) in unserem Text gab es nur Zeichen aus der in eckigen Klammern angegebenen Menge. Wenn Sie auch die Länge des Passworts überprüfen müssen (z. B. mindestens 6 Zeichen), dann den Quantifizierer + kann in der Form durch das Intervall „sechs oder mehr“ ersetzt werden {6,}:

Stadt von Adresse

Nehmen wir an, wir müssen die Stadt aus der Adressleiste ziehen. Das reguläre Programm hilft dabei, den Text aus „g“ zu extrahieren. bis zum nächsten Komma:

Schauen wir uns dieses Muster genauer an.

Wenn Sie den obigen Text gelesen haben, dann haben Sie bereits verstanden, dass einige Zeichen in regulären Ausdrücken (Punkte, Sternchen, Dollarzeichen usw.) eine besondere Bedeutung haben. Wenn Sie nach diesen Zeichen selbst suchen müssen, wird ihnen ein umgekehrter Schrägstrich vorangestellt (manchmal auch als Abschirmung). Daher wird bei der Suche nach dem Fragment „g.“ wir müssen in regulären Ausdrücken schreiben Herr wenn wir nach einem Plus suchen, dann + usw.

Die nächsten beiden Zeichen in unserer Vorlage, der Punkt und der Quantifizierer Sternchen, stehen für beliebig viele beliebige Zeichen, also beliebige Städtenamen.

Am Ende der Vorlage steht ein Komma, weil wir nach Text von „g“ suchen. zu einem Komma. Aber im Text können mehrere Kommas stehen, oder? Nicht nur nach der Stadt, sondern auch nach der Straße, den Häusern usw. Auf welchen von ihnen wird unsere Bitte aufhören? Dafür steht das Fragezeichen. Ohne sie würde unser regulärer Ausdruck die längstmögliche Zeichenfolge herausziehen:

In Bezug auf reguläre Ausdrücke ist ein solches Muster „gierig“. Zur Korrektur wird ein Fragezeichen benötigt – es macht den Quantor, hinter dem es steht, „geizig“ – und unsere Abfrage nimmt den Text nur bis zum ersten Komma nach „g.“:

Dateiname aus vollständigem Pfad

Eine weitere sehr häufige Situation besteht darin, den Dateinamen aus dem vollständigen Pfad zu extrahieren. Hier hilft ein einfacher regulärer Ausdruck der Form:

Der Trick dabei ist, dass die Suche tatsächlich in die entgegengesetzte Richtung erfolgt – vom Ende zum Anfang, denn am Ende unserer Vorlage steht $, und wir suchen nach allem davor bis zum ersten umgekehrten Schrägstrich von rechts. Der umgekehrte Schrägstrich wird wie der Punkt im vorherigen Beispiel maskiert.

PS

„Gegen Ende“ möchte ich klarstellen, dass all dies nur ein kleiner Teil aller Möglichkeiten ist, die reguläre Ausdrücke bieten. Es gibt viele Sonderzeichen und Regeln für ihre Verwendung, und ganze Bücher wurden zu diesem Thema geschrieben (ich empfehle zumindest dieses für den Anfang). In gewisser Weise ist das Schreiben regulärer Ausdrücke fast eine Kunst. Fast immer kann ein erfundener regulärer Ausdruck verbessert oder ergänzt werden, wodurch er eleganter wird oder mit einer größeren Bandbreite an Eingabedaten arbeiten kann.

Um die regulären Ausdrücke anderer zu analysieren und zu parsen oder Ihre eigenen zu debuggen, gibt es mehrere praktische Online-Dienste: RegEx101, RegExr und vieles mehr

Leider werden nicht alle Funktionen klassischer regulärer Ausdrücke in VBA unterstützt (z. B. Rückwärtssuche oder POSIX-Klassen) und können mit Kyrillisch arbeiten, aber ich denke, dass das, was vorhanden ist, für das erste Mal ausreicht, um Ihnen zu gefallen.

Wenn das Thema für Sie nicht neu ist und Sie etwas mitzuteilen haben, hinterlassen Sie in den Kommentaren unten reguläre Ausdrücke, die beim Arbeiten in Excel nützlich sind. Ein Geist ist gut, aber zwei Stiefel sind ein Paar!

• Ersetzen und Bereinigen von Text mit der SUBSTITUTE-Funktion
• Suche und Hervorhebung lateinischer Zeichen im Text
• Suche nach dem nächsten ähnlichen Text (Ivanov = Ivonov = Ivanof usw.)