So nutzen Sie REGEX-Funktionen in Excel, um Datenoperationen zu verbessern

Wenn Sie regelmäßig mit Daten in Excel arbeiten, kann das Erlernen der Verwendung von REGEX-Funktionen Ihre Fähigkeit, Daten zu analysieren und zu organisieren, erheblich verbessern. Mit REGEX oder regulären Ausdrücken steht Ihnen ein leistungsstarkes Werkzeug zum effizienten Auffinden von Mustern, Formatieren und Extrahieren von Informationen aus Texten zur Verfügung. Mithilfe dieser Funktionen in Excel können Sie viele komplexe Vorgänge wie das Bereinigen von Daten und das Extrahieren relevanter Informationen aus Textzellen vereinfachen. In diesem Artikel zeigen wir, wie Sie REGEX-Funktionen in Excel anwenden, um die Datenverwaltung zu verbessern und Ihre täglichen Aufgaben effizienter zu gestalten.

Reguläre Ausdrücke (oder REGEX) sind Suchmuster, die verwendet werden können, um zu prüfen, ob eine Textzeichenfolge mit einem bestimmten Muster übereinstimmt, und um Textzeichenfolgen zu extrahieren oder zu ersetzen, die mit einem bestimmten Muster übereinstimmen. Aufgrund ihrer Komplexität bietet dieser Artikel vereinfachte Zusammenfassungen und Beispiele für ihre Verwendung in Excel.

ملاحظة
REGEX-Funktionen stehen Personen zur Verfügung, die Excel für Microsoft 365 unter Windows oder Mac verwenden, sowie Personen, die Excel für das Web verwenden.

WIE MAN REGEXTEST VERWENDET

Diese Funktion testet, ob eine Textzeichenfolge mit einem bestimmten Muster übereinstimmt, und gibt basierend auf diesem Test TRUE oder FALSE zurück. Dies ist eine großartige Möglichkeit zu testen, ob Ihre Daten einem Muster folgen.

Syntax

REGEXTEST(a,b,c)

Wo

• a (erforderlich) ist der Text, Wert oder Zellverweis, der den Text enthält, den Sie testen möchten.
• b (erforderlich) ist das Muster, das zum Ausführen des Tests verwendet wird, und
• c (Optional) Entweder 0, wenn beim Test die Groß-/Kleinschreibung beachtet werden soll, oder 1, wenn nicht.

Beispiel für die Verwendung von REGEXTEST

Diese Tabelle enthält eine Liste von Produktcodes, die einer strengen Struktur folgen müssen.

Der richtige Code enthält:

• Eine kleine Darstellung der Produktgröße („xs“ für extra klein, „s“ für klein, „m“ für mittel usw.),
• Eine ein- oder zweistellige Zahl, die das Produktmaterial angibt,
• Drei Großbuchstaben geben an, wo das Produkt hergestellt wurde, und
• Ein Bindestrich zwischen jedem der drei oben gezeigten Teile.
• Ich möchte testen, ob alle Produktcodes dieser Struktur entsprechen.

Also schreibe ich in Zelle B2:

=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)

Wo

• [@Code] ist ein strukturierter Verweis auf die Spalte, in der sich die Codes befinden, die ich testen möchte.
• [xs|s|m|l|xl] ist der erste Teil des Produktcodes, den ich testen möchte, wobei die vertikalen Linien „oder“ bedeuten.
• [0-9]{1,2} ist der zweite Teil des Produktcodes, den ich testen möchte, wobei [0-9] eine beliebige einzelne Ziffer darstellt und {1,2} bedeutet, dass es eine oder zwei einzelne Ziffern geben kann Ziffern,
• [AZ]{3} ist der dritte Teil des Produktcodes, den ich testen möchte, wobei [AZ] einen beliebigen Großbuchstaben darstellt und {3} bedeutet, dass es genau drei dieser Buchstaben geben sollte.
• Die drei Teile des Codes, die ich testen möchte, werden durch einen Bindestrich und getrennt
• 0 ist das letzte Argument in der Formel, das Excel mitteilt, dass beim Test die Groß-/Kleinschreibung beachtet wird.

Wenn ich die Eingabetaste drücke, um diese Formel auf alle Zeilen in Spalte B anzuwenden, zeigt das Ergebnis, dass nur zwei der Codes gültig (WAHR) sind.

m-2-UK ist ungültig (angezeigt durch ein Ergebnis von FALSE), weil der Ländercode nur zwei Großbuchstaben enthält, xl-714-AUS ist ungültig, weil der Materialcode drei Ziffern enthält, und S-5-USA ist ungültig, weil der Der Größencode ist in Großbuchstaben angegeben.

ملاحظة
Dieses Beispiel enthält die Verwendung von Zeichen wie [ ] und { }. Es gibt jedoch viele andere Zeichen (auch Symbole genannt), die ebenfalls zur Identifizierung des zur Durchführung des Tests verwendeten Musters verwendet werden können, von denen ich einige in den folgenden Beispielen verwenden werde.

REGEXTRACT: Suche nach bestimmten Textteilen
Diese Funktion gibt Textteile in einer Zelle gemäß einem angegebenen Muster zurück. Beispielsweise möchten Sie möglicherweise Zahlen und Text trennen.

Syntax

REGEXTRAKT(d,e,f,g)

Wo

• d (erforderlich) ist der Text, der Wert oder die Zellreferenz, die den Text enthält, aus dem Sie extrahieren möchten.
• e (erforderlich) ist das Muster, das Sie extrahieren möchten.
• f (optional) ist gleich 0, wenn Sie nur die erste Übereinstimmung extrahieren möchten, 1, um alle anwendbaren Übereinstimmungen als Array zu extrahieren, 2, um Tupel aus der ersten Übereinstimmung zu extrahieren, und
• g (Optional) Entspricht entweder 0, wenn bei der Extraktion die Groß-/Kleinschreibung beachtet werden soll, oder 1, wenn dies nicht der Fall ist.

ملاحظة
Da formatierte Excel-Tabellen keine übergelaufenen Arrays verarbeiten können, stellen Sie sicher, dass Ihre Daten explizit formatiert sind, wenn Sie die Übereinstimmungen als Array im f-Argument extrahieren möchten.

Beispiel für die Verwendung von REGEXTRACT

In diesem Beispiel möchte ich die Vor- und Nachnamen sowie Telefonnummern von Kunden in drei separate Spalten extrahieren.

Konzentrieren wir uns zunächst auf die Namen. In Zelle B2 tippe ich Folgendes ein:

=REGEXEXTRACT(A2,"[AZ][az]+",1)

Wo

• A2 ist die Zelle, die die Daten enthält, die ich extrahieren möchte.
• [AZ][az]+ teilt Excel mit, dass ich alle Wörter extrahieren möchte, die mit einem Großbuchstaben gefolgt von Kleinbuchstaben beginnen, wobei das „+“ angibt, dass ich in jedem Muster einen oder mehrere Kleinbuchstaben zurückgeben möchte, und
• 1 gibt an, dass ich jedes Beispiel des obigen Musters als Array in einzelne Zellen aufteilen möchte (mit anderen Worten, der Vorname befindet sich in Zelle B2, der zweite Name in Zelle C2). Wenn Sie dieses Argument weglassen, gibt Excel nur die erste Übereinstimmung (Vorname) in Zelle B2 zurück.

Wenn ich die Eingabetaste drücke, führt Excel die Extraktion erfolgreich durch und fügt eine hellblaue Linie um Zelle C2 hinzu, um mich daran zu erinnern, dass es sich um eine Streumatrix handelt.

Wenn Zelle B2 ausgewählt ist, kann ich jetzt den Füllpunkt in der unteren rechten Ecke der Zelle verwenden, um diese relative Formel auf die verbleibenden Detailzeilen zu duplizieren.

Jetzt muss ich eine ähnliche REGEXTRACT-Formel verwenden, um Kundentelefonnummern zu extrahieren. In Zelle D2 tippe ich Folgendes ein:

=REGEXEXTRACT(A2,"[0-9()]+ [0-9-]+")

Wo

• A2 ist die Zelle, die die Daten enthält, die ich extrahieren möchte.
• [0-9()]+ extrahiert die Zahlen von null bis neun in gerundeten Klammern, wobei „+“ für eine oder mehrere Zahlen in diesem Muster extrahiert wird, und
• [0-9-]+ extrahiert die restlichen Zahlen aus der Zeichenfolge, wobei das zweite „-“ den Bindestrich darstellt, der die beiden Teile der Telefonnummer trennt, und das „+“ Excel mitteilt, dass ich eine oder mehrere Zahlen extrahieren möchte, wenn die Zeichenfolge enthält sie.

Da es in jeder Zelle in Spalte A nur eine Instanz dieses Musters gibt, muss ich keine weiteren Argumente hinzufügen. Sobald ich überprüft habe, dass diese Formel das erwartete Ergebnis liefert, kann ich sie mit dem Füllpunkt in den verbleibenden Zellen in Spalte D wiederholen.

ملاحظة
Es gibt in Excel andere Möglichkeiten, Daten zu extrahieren und ähnliche Ergebnisse zu erzielen, beispielsweise die Verwendung der TEXTSPLIT-Funktion oder des Flash Fill-Tools von Excel.

Datenmanipulation mit REGEXREPLACE

Diese Funktion übernimmt den Text in einer Zelle und erstellt eine neue Kopie dieser Daten in einer anderen Zelle. Obwohl diese Funktion REGEXREPLACE heißt, ersetzt sie nicht den Originaltext an seinem ursprünglichen Speicherort.

Syntax

REGEXREPLACE(h,i,j,k,l)

Wo

• h (erforderlich) ist der Text, Wert oder Zellverweis, der den Text enthält, den Sie ersetzen möchten.
• i (erforderlich) ist das Muster, das Sie ersetzen möchten.
• j (erforderlich) ist der Ersatz, den Sie erstellen möchten.
• k (optional) ist die Häufigkeit des Musters, das Sie ersetzen möchten, und
• l (Optional) Entweder 0, wenn beim Ersetzen die Groß-/Kleinschreibung beachtet werden soll, oder 1, wenn nicht.

Beispiel für die Verwendung von REGEXREPLACE

Unten sehen Sie eine Liste der Namen in Spalte A. Mein Ziel ist es, diese Namen in Spalte B neu zu erstellen, jedoch im Format „Nachname, Vorname“, einschließlich des Kommas, das die Namen trennt.

In Zelle B2 schreibe ich:

=REGEXREPLACE([@Client name],"([AZ][az]+) ([AZ][az]+)","$2, $1")

Wo

• [@Client name] zeigt auf die Spalte mit den Daten, die ich beeinflussen möchte.
• [AZ][az]+ doppelt in die Formel eingebettet (und durch ein Leerzeichen getrennt) teilt Excel mit, dass ich die beiden Textzeichenfolgen verwenden möchte, die einen Großbuchstaben gefolgt von einem oder mehreren Kleinbuchstaben enthalten, und
• „$2, $1“ teilt Excel mit, dass ich die Reihenfolge der beiden durch ein Komma und ein Leerzeichen getrennten Textzeichenfolgen umkehren möchte. Wenn ich keine Dollarsymbole einbeziehe, gibt Excel als Ergebnis in jeder Zelle einfach „2, 1“ zurück.

Ich habe die Argumente k und l in der obigen Formel nicht angesprochen, weil ich möchte, dass Excel alle Vorkommen ersetzt (Standard für k) und ich möchte, dass beim Ersetzen die Groß-/Kleinschreibung beachtet wird (Standard für l).

Da ich eine formatierte Tabelle verwende, wird die Formel beim Drücken der Eingabetaste auf die verbleibenden Zellen in Spalte B angewendet.

Die Verwendung regulärer Ausdrücke ist nicht auf Excel beschränkt. Tatsächlich können Sie REGEX verwenden, um andere Aufgaben auf Ihrem Computer zu automatisieren, z. B. das Reparieren von kopiertem und eingefügtem PDF-Text, das Umbenennen von in großen Mengen heruntergeladenen Dateien, das Formatieren der Währung, das Entfernen von HTML-Tags und mehr.