Regex, um Saiten in Excel zu extrahieren (eine oder alle Übereinstimmungen)

In diesem Tutorial lernen Sie, wie Sie reguläre Ausdrücke in Excel verwenden, um Substrings zu finden und zu extrahieren, die einem bestimmten Muster entsprechen.

Microsoft Excel bietet eine Reihe von Funktionen, um Text aus Zellen zu extrahieren. Diese Funktionen können mit den meisten Herausforderungen der String -Extraktion in Ihren Arbeitsblättern fertig werden. Die meisten, aber nicht alle. Wenn der Text stolpert, kommen regelmäßige Ausdrücke zur Rettung. Warten Sie ... Excel hat keine Regex -Funktionen! Es stimmt, keine eingebauten Funktionen. Aber es gibt nichts, was dich daran hindert, deine eigenen zu benutzen :)

• VBA Regexp Extract -Funktion
  • Nummer aus der Zeichenfolge extrahieren
  • Holen Sie sich alle Spiele
  • Holen Sie sich eine E -Mail -Adresse aus der Zeichenfolge
  • E -Mail -Domain extrahieren
  • Telefonnummern extrahieren
  • Datum extrahieren
  • Zeit auswählen
• .NET Regex Extract -Funktion
  • Text zwischen zwei Zeichen extrahieren
  • Text zwischen zwei Saiten extrahieren
  • Holen Sie sich den Domain -Namen von der URL

Excel VBA Regex -Funktion zum Extrahieren von Zeichenfolgen

So fügen Sie Ihrem Excel eine benutzerdefinierte Regex -Extraktfunktion hinzu. Fügen Sie den folgenden Code in den VBA -Editor ein. Um reguläre Ausdrücke in VBA zu ermöglichen, verwenden wir das integrierte Microsoft Regexp-Objekt. Der Code wird von unserem Excel -Spezialisten Alex Frolov geschrieben.

Public Function RegExpExtract(text As String , pattern As String , Optional instance_num As Integer = 0, Optional match_case As Boolean = True ) Dim text_matches() As String Dim matches_index As Integer On Error GoTo ErrHandl RegExpExtract = "" Set regex = CreateObject ( "VBScript.RegExp" ) regex.pattern = pattern regex.Global = True regex.multiline = true if true = match_case , dann regex.ignorecase = false sonst regex.ignorecase = true end if set Matches = regex.execute (text ) if 0 Als nächstes übereinstimmt_index regexpextrakt = text_matches else regexpextract = übereinstimmt.Item (Instance_num - 1) Ende der Endfunktion ENGE -FUNKTION : REGEXPEXRACT = CVERR (XLERRVAL) Endfunktion Endfunktion

Wenn Sie nur wenig Erfahrung mit VBA haben, kann sich eine schrittweise Benutzerhandbuch als hilfreich erweisen: So fügen Sie VBA-Code in Excel ein.

Notiz. Um die Funktion zu funktionieren, speichern Sie Ihre Datei als makro-fähige Arbeitsmappe (.xlsm).

Regexpextract -Syntax

Die Regexpextract -Funktion durchsucht nach einem Eingangszeichenfolge nach Werten, die einem regulären Ausdruck übereinstimmen und einen oder alle Übereinstimmungen extrahiert.

Die Funktion hat die folgende Syntax:

Regexpextract (text, muster, [instance_num], [match_case])

Wo:

• Text (Erforderlich) - Die Suchzeichenfolge.
• Muster (erforderlich) - Der reguläre Ausdruck, der übereinstimmt. Wenn das Muster direkt in einer Formel geliefert wird, sollte er in doppelten Anführungszeichen eingeschlossen werden.
• Instance_num (optional) - Eine Seriennummer, die angibt, welche Instanz extrahieren soll. Wenn es weggelassen wird, gibt alle gefundenen Übereinstimmungen zurück (Standard).
• Match_case (optional) - Definiert, ob Textfall übereinstimmt oder ignoriert werden soll. Wenn wahr oder ausgelassen (Standard), wird eine Fallempfindlichkeit durchgeführt. wenn falsch - Fall -unempfindlich.

Die Funktion funktioniert in allen Versionen von Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 und Excel 2010.

4 Dinge, die Sie über Regexpextract wissen sollten

Um die Funktion in Ihrem Excel effektiv zu verwenden, müssen einige wichtige Dinge beachten:

1. Standardmäßig gibt die Funktion alle gefundenen Übereinstimmungen in benachbarte Zellen zurück, wie in diesem Beispiel gezeigt. Um ein spezifisches Ereignis zu erhalten, liefern Sie das Argument von Instance_Num eine entsprechende Zahl.
2. Standardmäßig ist die Funktion Fallempfindlichkeit . Setzen Sie für den Fall-unempfindlichen Matching das Argument match_case auf false. Aufgrund der VBA-Einschränkungen funktioniert das von Fall unempfindliche Konstrukt (? I) nicht.
3. Wenn kein gültiges Muster gefunden wird , gibt die Funktion nichts zurück (leere Zeichenfolge).
4. Wenn das Muster ungültig ist , ein #Value! Fehler treten auf.

Bevor Sie diese benutzerdefinierte Funktion in Ihren Arbeitsblättern verwenden, müssen Sie verstehen, wozu sie fähig ist, oder? Die folgenden Beispiele decken einige häufige Anwendungsfälle ab und erklären, warum sich das Verhalten in Dynamic Array Excel (Microsoft 365 und Excel 2021) und traditioneller Excel (2019 und ältere Versionen) unterscheiden kann.

Notiz. Out Regex -Beispiele werden für faire einfache Datensätze geschrieben. Wir können nicht garantieren, dass sie in Ihren echten Arbeitsblättern einwandfrei arbeiten. Diejenigen, die Erfahrung mit Regex haben, würden sich einig sein, dass das Schreiben regelmäßiger Ausdrücke ein unendlicher Weg zur Perfektion ist - fast immer gibt es eine Möglichkeit, es eleganter oder in der Lage zu machen, eine größere Reihe von Eingabedaten zu bearbeiten.

Regex, um die Zahl aus der Zeichenfolge zu extrahieren

Nach der grundlegenden Maxime des Unterrichts "von einfach zu komplex" beginnen wir mit einem sehr einfachen Fall: Anzahl der Anzahl aus der Zeichenfolge.

Das erste, was Sie entscheiden können, ist welche Nummer abgerufen werden: Erstens, letztes, spezifisches Ereignis oder alle Zahlen.

Erste Zahl extrahieren

Dies ist so einfach wie Regex. Da \ D eine Ziffer von 0 bis 9 und ein oder mehrmals bedeutet, nimmt unser regulärer Ausdruck diese Form an:

Muster : \ d

Setzen Sie Instance_Num auf 1 und Sie erhalten das gewünschte Ergebnis:

=RegExpExtract(A5, "\d ", 1)

Wobei A5 die ursprüngliche Zeichenfolge ist.

Aus Gründen der Einschätzung können Sie das Muster in eine vordefinierte Zelle ($ A $ 2) eingeben und seine Adresse mit dem $ sign: sperren:

=RegExpExtract(A5, $A$2, 1)

Holen Sie sich die letzte Nummer

Um die letzte Zahl in einer Zeichenfolge zu extrahieren, ist hier das zu verwendende Muster:

Muster : (\ d) (?!.*\ D)

In eine menschliche Sprache übersetzt, heißt es: Finden Sie eine Nummer, die (überall, nicht nur sofort) von einer anderen Nummer befolgt wird. Um dies auszudrücken, verwenden wir eine negative Lookahead (?!.*\ D), was bedeutet, dass es rechts vom Muster keine andere Ziffer (\ d) geben sollte, unabhängig davon, wie viele andere Zeichen vor ihm sind.

=RegExpExtract(A5, "(\d )(?!.*\d)")

Tipps:

• Verwenden Sie \ D für Muster und eine geeignete Seriennummer für instance_num , um ein spezifisches Vorkommen zu erhalten.
• Die Formel zum Extrahieren aller Zahlen wird im nächsten Beispiel diskutiert.

Regex, um alle Übereinstimmungen zu extrahieren

Wenn Sie unser Beispiel ein wenig weiter drücken, möchten Sie alle Zahlen aus einer Zeichenfolge erhalten, nicht nur eines.

Wie Die Standardeinstellung ist alles übereinstimmt, sodass Sie diesen Parameter einfach weglassen:

=RegExpExtract(A2, "\d ")

Die Formel funktioniert wunderbar für eine einzelne Zelle, aber das Verhalten unterscheidet sich in dynamischen Array-Excel- und nicht-dynamischen Versionen.

Excel 365 und Excel 2021

Aufgrund der Unterstützung von dynamischen Arrays verschüttet eine reguläre Formel automatisch in so viele Zellen, wie es benötigt wird, um alle berechneten Ergebnisse anzuzeigen. In Bezug auf Excel wird dies als verschütteter Bereich bezeichnet:

Excel 2019 und niedriger

In der vor-dynamischen Excel würde die obige Formel nur eine Übereinstimmung zurückgeben. Um mehrere Übereinstimmungen zu erhalten, müssen Sie es zu einer Array -Formel machen. Wählen Sie hierzu einen Zellbereich aus, geben Sie die Formel ein und drücken Sie die Strg -Eingabetaste, um sie zu vervollständigen.

Ein Nachteil dieses Ansatzes ist ein Haufen #N/A -Fehler, die in "zusätzlichen Zellen" auftreten. Bedauerlicherweise kann nichts dagegen unternommen werden (weder Ifror noch IFNA können es reparieren, leider).

Extrahieren Sie alle Übereinstimmungen in einer Zelle

Bei der Verarbeitung einer Datenspalte funktioniert der obige Ansatz offensichtlich nicht. In diesem Fall würde eine ideale Lösung alle Übereinstimmungen in einer einzelnen Zelle zurückgeben. Um dies erledigt zu haben, liefern Sie die Ergebnisse von Regexpextract für die Textjoin -Funktion und trennen Sie sie mit jedem Trennzeichen, den Sie mögen, sagen Sie ein Komma und einen Raum:

=TEXTJOIN(", ", TRUE, RegExpExtract(A5, "\d "))

Notiz. Da die Textjoin -Funktion nur für Microsoft 365, Excel 2021 und Excel 2019 in Excel verfügbar ist, funktioniert die Formel in älteren Versionen nicht.

Regex zum Extrahieren von Text aus der Zeichenfolge

Das Extrahieren von Text aus einer alphanumerischen Zeichenfolge ist in Excel eine herausfordernde Aufgabe. Bei Regex wird es so einfach wie Kuchen. Verwenden Sie einfach eine negierte Klasse, um alles zu entsprechen, was keine Ziffer ist.

Muster : [^\ d]

Um Substrings in einzelnen Zellen (Verschüttungsbereich) zu erhalten, lautet die Formel:

=RegExpExtract(A5, "[^\d] ")

Um alle Übereinstimmungen in eine Zelle auszugeben, nisten Sie die Regexpextradenfunktion in Textjoin wie folgt:

=TEXTJOIN("", TRUE, RegExpExtract(A5, "[^\d] "))

Regex zum Extrahieren von E -Mail -Adresse aus der Zeichenfolge

Um eine E -Mail -Adresse aus einer Zeichenfolge mit vielen verschiedenen Informationen herauszuholen, schreiben Sie einen regulären Ausdruck, der die E -Mail -Adressstruktur repliziert.

Muster : [\ W \. \-] @[a-za-z0-9 \. \-] \. [A-za-z] {2,24}

Wenn wir diese Regex brechen, bekommen wir:

• [\ w \. \-] ist ein Benutzername, der 1 oder mehr alphanumerische Zeichen, Unterstriche, Punkte und Bindestriche umfassen kann.
• @ Symbol
• [A-Za-Z0-9 \. \-] ist ein Domänenname, der aus: Groß- und Kleinbuchstaben, Ziffern, Bindestrichen und Punkten (im Fall von Subdomänen) besteht. Unterstriche sind hier nicht zulässig, daher werden 3 verschiedene Zeichensätze (wie Az AZ und 0-9) anstelle von \ w verwendet, die zu jedem Buchstaben, der Ziffern oder Unterstriche entspricht.
• \. [A-za-z] {2,24} ist eine Domäne auf der obersten Ebene. Besteht aus einem Punkt, gefolgt von Groß- und Kleinbuchstaben. Die meisten Domänen auf höchster Ebene sind 3 Buchstaben lang (z. B. .com .org, .edu usw.), aber theoretisch kann es 2 bis 24 Buchstaben (die am längsten registrierte TLD) enthalten.

Unter der Annahme, dass die Zeichenfolge in A5 und das Muster in A2 befindet, lautet die Formel zum Extrahieren einer E -Mail -Adresse:

=RegExpExtract(A5, $A$2)

Regex, um Domain aus der E -Mail zu extrahieren

Wenn es darum geht, eine E -Mail -Domain zu extrahieren, wird der erste Gedanke, der in den Sinn kommt, mit einer erfassenden Gruppe einnutzt, um Text zu finden, der unmittelbar dem @ -Zeichen folgt.

Muster : @([a-za-z0-9 \. \-] \. [A-za-z] {2,24})

Servieren Sie es unserer Regexp -Funktion:

=RegExpExtract(A5, "@([A-Za-z0-9\.\-] \.[A-Za-z]{2,24})")

Und Sie erhalten dieses Ergebnis:

Mit klassischen regulären Ausdrücken ist alles außerhalb einer erfassenden Gruppe nicht in die Extraktion enthalten. Niemand weiß, warum VBA Regex unterschiedlich funktioniert und "@" auch erfasst. Um es loszuwerden, können Sie das erste Zeichen aus dem Ergebnis entfernen, indem Sie es durch eine leere Zeichenfolge ersetzen.

=REPLACE(RegExpExtract(A5, "@([az\d][az\d\-\.]*\.[az]{2,})", 1, FALSE), 1, 1, "")

Regelmäßiger Ausdruck zum Extrahieren von Telefonnummern

Telefonnummern können auf viele verschiedene Arten geschrieben werden, weshalb es so gut wie unmöglich ist, eine Lösung zu finden, die unter allen Umständen funktioniert. Trotzdem können Sie alle in Ihrem Datensatz verwendeten Formate aufschreiben und versuchen, sie anzupassen.

In diesem Beispiel werden wir eine Regex erstellen, die in jedem dieser Formate Telefonnummern extrahiert:

(123) 345-6789 (123) 345 6789 (123) 3456789 123-345-6789

123.345.6789 123 345 6789 1233456789

Muster : \ (? \ D {3} [-\. \)]*\ D {3} [-\. ]? \ d {4} \ b

• Der erste Teil \ (? \ D {3} entspricht der Klammung mit Null oder einer Öffnung, gefolgt von drei Ziffern D {3}.
• Der [-\. \).
• Als nächstes haben wir wieder drei Ziffern d {3}, gefolgt von einem Bindestrich, einer Periode oder einem Raum [-\. ]? 0 oder 1 Mal erscheinen.
• Danach gibt es eine Gruppe von vier Ziffern \ d {4}.
• Schließlich gibt es eine Wortgrenze, die feststellt, dass eine Telefonnummer, nach der wir suchen, nicht Teil einer größeren Nummer sein kann.

Die vollständige Formel nimmt diese Form an:

=RegExpExtract(A5, "\(?\d{3}[-\. \)]*\d{3}[-\. ]?\d{4}\b")

Bitte beachten Sie, dass der obige Regex einige falsch positive Ergebnisse wie 123 zurückgeben kann) 456 7899 oder (123 456 7899. Die folgende Version behebt diese Probleme. Diese Syntax funktioniert jedoch nur in VBA Regexp-Funktionen, nicht in klassischen regulären Ausdrücken.

Muster : (\ (\ d {3} \) | \ d {3}) [-\. ]? \ d {3} [-\. ]? \ d {4} \ b

Regex zum Extrahieren von Datum aus der Zeichenfolge

Ein regelmäßiger Ausdruck zum Extrahieren eines Datums hängt vom Format ab, in dem das Datum in einer Zeichenfolge erscheint. Zum Beispiel:

Um Daten wie 1/1/21 oder 01.01.2021 zu extrahieren, ist der Regex: \ d {1,2} \/\ d {1,2} \/(\ d {4} | \ d {2})

Es sucht nach einer Gruppe von 1 oder 2 Ziffern d {1,2}, gefolgt von einem Schrägstrich, gefolgt von einer anderen Gruppe von 1 oder 2 Ziffern, gefolgt von einem Schrägstrich, gefolgt von einer Gruppe von 4 oder 2 Ziffern (\ d {4} | \ d {2}). Bitte beachten Sie, dass wir zuerst nach 4-stelligen Jahren und erst dann nach 2-stelligen Jahren suchen. Wenn wir es in die andere Richtung schreiben, werden nur die ersten zwei Ziffern aus allen Jahren ausgewählt. Dies liegt daran, dass nach der ersten Bedingung in der Abwechslung oder im Konstrukt übereinstimmt, die verbleibenden Bedingungen nicht überprüft werden.

Um Daten wie 1-Jan-21 oder 01-Jan-2021 abzurufen, ist das Muster: \ d {1,2}-[a-za-z] {3}-\ d {2,4}

Es sucht nach einer Gruppe von 1 oder 2 Ziffern, gefolgt von einem Bindestrich, gefolgt von einer Gruppe von 3 Groß- oder Kleinbuchstaben, gefolgt von einem Bindestrich, gefolgt von einer Gruppe von 4 oder 2 Ziffern.

Nachdem wir die beiden Muster miteinander kombiniert haben, erhalten wir die folgende Regex:

Muster : \ b \ d {1,2} [\/-] (\ d {1,2} | [a-za-z] {3}) [\/-] (\ d {4} | \ d {2}) \ b

Wo:

• Der erste Teil ist 1 oder 2 Ziffern: \ d {1,2}
• Der zweite Teil ist entweder 1 oder 2 Ziffern oder 3 Buchstaben: (\ d {1,2} | [a-za-z] {3})
• Der dritte Teil ist eine Gruppe von 4 oder 2 Ziffern: (\ d {4} | \ d {2})
• Der Abgrenzer ist entweder ein Vorwärts-Schrägstrich oder ein Bindestrich: [\/-]
• Eine Wortgrenze \ b wird auf beiden Seiten platziert, um klar zu machen, dass ein Datum ein separates Wort ist und nicht Teil einer größeren Zeichenfolge.

Wie Sie im Bild unten sehen können, zieht es erfolgreich Daten aus und lässt Substrings wie den 22.11.333 aus. Es gibt jedoch immer noch falsche positive Ergebnisse zurück. In unserem Fall stimmt der Substring 11-ABC-2222 in A9 technisch mit dem Datumsformat DD-MMM-YJJJ zusammen und wird daher extrahiert.

Um Fehlalarme zu beseitigen, können Sie den Teil [a-za-z] {3} durch eine vollständige Liste der Abkürzungen mit 3-Buchstabenmonat ersetzen:

Muster : \ b \ d {1,2} [\/-] (\ d {1,2} | (Jan | feb | mar | Apr | May | jul | aug | sep | oct | nov | dec)) [\/-] (\ d {4} | {2 {2}) \ b

Um den Brieffall zu ignorieren, setzen wir das letzte Argument unserer benutzerdefinierten Funktion auf false:

=RegExpExtract(A5, $A$2, 1, FALSE)

Und diesmal erhalten wir ein perfektes Ergebnis:

Regex, um die Zeit aus String zu extrahieren

Um Zeit im HH: MM oder HH: MM: SS -Format zu bekommen, wirkt der folgende Ausdruck einen Genuss.

Muster : \ b (0? [0-9] | 1 [0-2]): [0-5] \ D (: [0-5] \ D)? \ S? (Am | PM) \ B | \ B ([0-9] | [0-1] \ D | 2 [0-3]): [0-5] \ D (: [0-0-5] \ D)?

Wenn Sie diese Regex abbauen, können Sie 2 Teile von | getrennt sehen Das ermöglicht die oder Logik. Mit anderen Worten, wir suchen nach einem Substring, das einem der folgenden Ausdrücke entspricht.

Ausdruck 1 : \ B (0? [0-9] | 1 [0-2]): [0-5] \ D (: [0-5] \ D)? \ S? (Am | PM) \ B

Ruft die Zeiten mit AM/PM ab.

Die Stunde kann eine beliebige Zahl von 0 bis 12 sein. Um es zu erhalten, verwenden wir das oder Konstrukt ([0-9] | 1 [0-2]), wobei:

• [0-9] entspricht einer beliebigen Zahl von 0 bis 9
• 1 [0-2] übereinstimmen eine beliebige Zahl von 10 bis 12

Minute [0-5] \ D ist eine beliebige Zahl von 00 bis 59.

Zweitens (: [0-5] \ d)? ist auch eine Zahl von 00 bis 59. die? Quantifizierer bedeutet Null oder ein Ereignis, da Sekunden im Zeitwert enthalten sein können oder nicht.

Ausdruck 2 : \ B ([0-9] | [0-1] \ D | 2 [0-3]): [0-5] \ D (: [0-5] \ D)? (?! :) :) :) :) :) :)

Extrahiert Zeiten ohne AM/PM.

Der Stundenteil kann eine beliebige Zahl von 0 bis 32 sein. Um es zu erhalten, wird ein anderes Konstrukt verwendet ([0-9] | [0-1] \ D | 2 [0-3]), wobei:

• [0-9] entspricht einer beliebigen Zahl von 0 bis 9
• [0-1] \ D entspricht einer beliebigen Zahl von 00 bis 19
• 2 [0-3] entspricht einer beliebigen Zahl von 20 bis 23

Die Minute und die zweiten Teile sind die gleichen wie in Expression 1 oben.

Das negative Lookahead (?! :) wird hinzugefügt, um Strings wie 20:30:80 zu überspringen.

Da PM/AM entweder Großbuchstaben oder Kleinbuchstaben sein kann, machen wir die Funktionsfall-unempfindlich:

=RegExpExtract(A5, $A$2, 1, FALSE)

Hoffentlich gaben Ihnen die obigen Beispiele einige Ideen zur Verwendung regelmäßiger Ausdrücke in Ihren Excel -Arbeitsblättern. Leider werden in VBA nicht alle Funktionen klassischer regulärer Ausdrücke unterstützt. Wenn Ihre Aufgabe mit VBA Regexp nicht erledigt werden kann, empfehle ich Ihnen, den nächsten Teil zu lesen, in dem viel leistungsstärkere .NET -Regex -Funktionen diskutiert werden.

Benutzerdefinierte .NET -basierte Regex -Funktion zum Extrahieren von Text in Excel

Im Gegensatz zu VBA Regexp -Funktionen, die von jedem Excel -Benutzer verfasst werden können, ist .NET Regex der Bereich des Entwicklers. Das Microsoft .NET-Framework unterstützt die mit Perl 5 kompatibele reguläre Expressions-Syntax mit vollem Einfachen. Dieser Artikel wird Ihnen nicht weiterleiten, wie Sie solche Funktionen schreiben (ich bin kein Programmierer und habe keine geringste Idee, wie es geht :)

Vier leistungsstarke Funktionen, die von der Standard .NET Regex -Engine verarbeitet wurden, werden bereits von unseren Entwicklern verfasst und in Ultimate Suite enthalten. Im Folgenden werden wir einige praktische Verwendungen der Funktion demonstrieren, die speziell für das Extrahieren von Text in Excel entwickelt wurde.

Tipp. Informationen zur .NET Regex -Syntax finden Sie in der regulären Ausdruckssprache .NET.

Wie man mit regelmäßigen Ausdrücken Strings in Excel extrahiert

Angenommen, Sie haben die neueste Version von Ultimate Suite installiert, wobei das Extrahieren von Text mit regulären Ausdrücken auf diese beiden Schritte hinausging:

1. Klicken Sie auf der Registerkarte "AbleTbits" in der Textgruppe auf Regex -Tools .

   

2. Wählen Sie im Bereich Regex Tools die Quelldaten aus, geben Sie Ihr Regex -Muster ein und wählen Sie die Option Extract . Um das Ergebnis als benutzerdefinierte Funktion und nicht als Wert zu erhalten, wählen Sie das Kontrollkästchen Einfügen als Formel . Wenn Sie fertig sind, klicken Sie auf die Schaltfläche Extrahieren .

   

Die Ergebnisse werden in einer neuen Spalte rechts von Ihren ursprünglichen Daten angezeigt:

FungbitsRegexextrakt Syntax

Unsere benutzerdefinierte Funktion hat die folgende Syntax:

FungbitsRegexextract (Referenz, regulär_expression)

Wo:

• Referenz (Erforderlich) - Eine Referenz auf die Zelle, die die Quellzeichenfolge enthält.
• Regulal_expression (erforderlich) - Das zu übereinstimmende Regex -Muster.

Wichtiger Hinweis! Die Funktion funktioniert nur auf den Maschinen mit ultimativer Suite für Excel installiert.

Nutzungsnotizen

Um Ihre Lernkurve und Ihre Erfahrung angenehmer zu gestalten, achten Sie bitte auf diese Punkte:

1. Um eine Formel zu erstellen, können Sie unsere Regex -Tools oder den Dialogfeld "Einfügen von Excel einfügen" verwenden oder den vollständigen Funktionsnamen in einer Zelle eingeben. Sobald die Formel eingefügt wurde, können Sie sie wie jede native Formel verwalten (bearbeiten, kopieren oder verschieben).
2. Das Muster, das Sie in den Bereich Regex Tools eingeben, geht zum ^2. Argument. Es ist auch möglich, eine regelmäßige Expression in einer separaten Zelle zu behalten. Verwenden Sie in diesem Fall einfach eine Zellreferenz für das ^2. Argument.
3. Die Funktion extrahiert die erste gefundene Übereinstimmung .
4. Standardmäßig ist die Funktion Fallempfindlichkeit . Verwenden Sie für Fall-unempfindliche Übereinstimmungen das Muster (? I).
5. Wenn keine Übereinstimmung gefunden wird, wird ein #n/ein Fehler zurückgegeben.

Regex zum Extrahieren von String zwischen zwei Zeichen

Um einen Text zwischen zwei Zeichen zu erhalten, können Sie entweder eine erfassende Gruppe oder eine Look-Arounds verwenden.

Nehmen wir an, Sie möchten Text zwischen Klammern extrahieren. Eine Erfassungsgruppe ist der einfachste Weg.

Muster 1 : \ [(.*?) \]

Mit einem positiven Aussehen und Lookahead wird das Ergebnis genau das gleiche sein.

Muster 2 : (?

Bitte achten Sie darauf, dass unsere Erfassungsgruppe (*?) Eine faule Suche nach Text zwischen zwei Klammern durchführt - vom ersten [zum ersten]. Eine erfassende Gruppe ohne Fragezeichen (.*) Würde eine gierige Suche durchführen und alles von Anfang an [bis zum letzten] erfassen.

Mit dem Muster in A2 lautet die Formel wie folgt:

=AblebitsRegexExtract(A5, $A$2)

Wie man alle Übereinstimmungen bekommt

Wie bereits erwähnt, kann die Funktion "ferleBitSregexextrakt) nur eine Übereinstimmung extrahieren. Um alle Übereinstimmungen zu erhalten, können Sie die VBA -Funktion verwenden, die wir zuvor besprochen haben. Es gibt jedoch eine Einschränkung - VBA Regexp unterstützt keine Gruppen, sodass das obige Muster auch die "Grenze" -Scharaktere in unserem Fall zurückgeben.

=TEXTJOIN(" ", TRUE, RegExpExtract(A5, $A$2))

Um die Klammern loszuwerden, ersetzen Sie sie durch diese Formel durch leere Saiten (""):

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ", TRUE, RegExpExtract(A5, $A$2)), "]", ""),"[","")

Für eine bessere Lesbarkeit verwenden wir ein Komma für den Trennzeichen.

Regex zum Extrahieren von Text zwischen zwei Zeichenfolgen

Der Ansatz, den wir zum Ausziehen von Text zwischen zwei Zeichen herausgearbeitet haben, wird auch zum Extrahieren von Text zwischen zwei Zeichenfolgen arbeiten.

Verwenden Sie beispielsweise alles zwischen "Test 1" und "Test 2", um den folgenden regulären Ausdruck zu verwenden.

Muster : Test 1 (.*?) Test 2

Die vollständige Formel lautet:

=AblebitsRegexExtract(A5, "test 1(.*?)test 2")

Regex, um Domäne aus der URL zu extrahieren

Selbst bei regulären Ausdrücken ist das Extrahieren von Domainnamen aus URLs keine triviale Aufgabe. Das Schlüsselelement, das den Trick ausmacht, sind nicht kaufende Gruppen. Wählen Sie abhängig von Ihrem ultimativen Ziel eine der folgenden Regexes.

Um einen vollständigen Domainnamen einschließlich Subdomains zu erhalten

Muster : (?: Https? \: |^| \ S) \/\/((?: [A-za-z \ d \-\.] {2,255} \.)? [A-za-z \ d \-] {1,63} \.

Eine Domäne auf zweiter Ebene ohne Subdomänen zu bekommen

Muster : (?: Https? \: |^| \ S) \/\/(?: [A-za-z \ d \-\.] {2,255} \.)?

Lassen Sie uns nun sehen, wie diese regulären Ausdrücke auf einem Beispiel "https://www.mobile.ablebits.com" als Beispiel -URL funktionieren:

• (?: https? \: |^| \ s) - Nicht -Kreis -Gruppe. Es übereinstimmt, fängt aber nicht ein Substring erfasst, dem eines der folgenden: https, http, Start von String (^), ein Whitespace -Zeichen (\ s). Die letzten beiden Elemente sind enthalten, um Protokoll-Relativ-URLs wie "//google.com" zu verarbeiten.
• \/ \/ ​​- Zwei Vorwärts -Schrägstriche (jeweils wird ein Backslash vorgegangen, um der besonderen Bedeutung des Vorwärts -Schrägs zu entkommen und ihn buchstäblich zu interpretieren).
• (?: [A-za-z \ d \-\.] {2,255} \.)? -Nicht-Kauf-Gruppe zur Identifizierung von Domänen auf der dritten Stufe, der vierten Ebene usw., falls vorhanden ( Mobile in unserer Stichproben-URL). Im ersten Muster wird es innerhalb einer größeren Erfassungsgruppe platziert, um alle derartigen Subdomänen in die Extraktion einzubeziehen. Eine Subdomäne kann 2 bis 255 Zeichen lang sein, daher der {2.255} Quantifizierer.
• ([A-za-z \ d \-] {1,63} \. [A-za-z] {2,24})-Gruppe erfassen, um die Domäne der zweiten Ebene ( Ab können ) und die Domain der obersten Ebene ( Com ) zu extrahieren. Die maximale Länge einer Domäne auf der zweiten Ebene beträgt 63 Zeichen. Die derzeit existierende Domäne auf der obersten Ebene enthält 24 Zeichen.

Je nachdem, welcher reguläre Ausdruck in A2 eingegeben wird, führt die folgende Formel zu unterschiedlichen Ergebnissen:

=AblebitsRegexExtract(A5, $A$2)

Regex, um den vollständigen Domain -Namen mit allen Subdomänen zu extrahieren:

Regex, um eine Domäne der zweiten Ebene ohne Subdomänen zu extrahieren:

So extrahieren Sie mit regelmäßigen Ausdrücken Teile des Textes in Excel. Ich danke Ihnen für das Lesen und freue mich darauf, Sie nächste Woche in unserem Blog zu sehen!

Verfügbare Downloads

Excel Regex extrahieren Beispiele (.xlsm -Datei) Ultimate Suite -Testversion (.exe -Datei)

Das obige ist der detaillierte Inhalt vonRegex, um Saiten in Excel zu extrahieren (eine oder alle Übereinstimmungen). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!