Beispiele für Excel Regex: Verwenden regelmäßiger Ausdrücke in Formeln

Kann nie verstehen, warum regelmäßige Ausdrücke in Excel -Formeln nicht unterstützt werden? Jetzt sind sie :) Mit unseren benutzerdefinierten Funktionen können Sie leicht Zeichenfolgen finden, ersetzen, extrahieren und entfernen, die einem bestimmten Muster entsprechen.

Auf den ersten Blick hat Excel alles, was Sie jemals für Text -Zeichenfolge -Manipulationen brauchen könnten. Hmm ... was ist mit regulären Ausdrücken? Hoppla, es gibt keine integrierten Regex-Funktionen in Excel. Aber niemand sagt, wir können unsere eigenen nicht erschaffen :)

• Was ist Regex?
• Gibt es Regex -Funktionen in Excel?
• Excel Regex -Cheat -Blatt
• Benutzerdefinierte VBA Regex -Funktionen
• REGEX -Übereinstimmungsfunktion
• Regex -Extraktfunktion
• Regex ersetzen die Funktion
• Regex -Tools - reguläre Ausdrücke in Excel ohne VBA

Was ist regulärer Ausdruck?

Ein regulärer Ausdruck (auch bekannt als Regex oder Regexp ) ist eine speziell codierte Reihenfolge von Zeichen, die ein Suchmuster definiert. Mit diesem Muster finden Sie eine Matching -Zeichenkombination in einer Zeichenfolge oder validieren Sie die Dateneingabe. Wenn Sie mit einer Wildcard -Notation vertraut sind, können Sie sich Regexes als eine erweiterte Version von Wildcards vorstellen.

Regelmäßige Ausdrücke haben ihre eigene Syntax, die aus Sonderzeichen, Operatoren und Konstrukten besteht. Zum Beispiel entspricht [0-5] eine einzelne Ziffer von 0 bis 5.

Regelmäßige Ausdrücke werden in vielen Programmiersprachen verwendet, einschließlich JavaScript und VBA. Letzteres verfügt über ein spezielles Regexp -Objekt, das wir verwenden werden, um unsere benutzerdefinierten Funktionen zu erstellen.

Unterstützt Excel Regex?

Bedauerlicherweise gibt es in Excel keine eingebauten Regex -Funktionen. Um regelmäßige Ausdrücke in Ihren Formeln verwenden zu können, müssen Sie Ihre eigene benutzerdefinierte Funktion (VBA oder .NET-basiert) erstellen oder Tools von Drittanbietern installieren, die Regexes unterstützen.

Excel Regex -Cheat -Blatt

Unabhängig davon, ob ein Regex -Muster sehr einfach oder extrem ausgefeilt ist, wird es unter Verwendung der gemeinsamen Syntax erstellt. Dieses Tutorial zielt nicht darauf ab, Ihnen regelmäßige Ausdrücke beizubringen. Dafür gibt es online viele Ressourcen, von kostenlosen Tutorials für Anfänger bis hin zu Premium -Kursen für fortgeschrittene Benutzer.

Im Folgenden geben wir einen kurzen Hinweis auf die Hauptregex -Muster, die Ihnen helfen, die Grundlagen zu erfassen. Es kann auch als Cheat -Blatt funktionieren, wenn Sie weitere Beispiele studieren.

Wenn Sie sich mit regulären Ausdrücken wohl fühlen, können Sie direkt zu den Regexp -Funktionen springen.

Charaktere

Dies sind die am häufigsten verwendeten Muster, die bestimmte Zeichen entsprechen.

Muster	Beschreibung	Beispiel	Übereinstimmungen
.	Wildcard -Charakter: Übereinstimmung mit einem einzelnen Charakter außer einer Zeilenpause entspricht	.ot	Punkt , heiß , Pot , @ot
\D	Ziffernzeichen: Jede einzelne Ziffer von 0 bis 9	\D	In A1B übereinstimmt 1
\D	Jeder Charakter, der keine Ziffer ist	\D	In A1B übereinstimmt a und b
\S	Whitespace -Charakter: Platz, Tab, neue Linie und Kutschenrendite	.\S.	In 3 Cent übereinstimmt 3 c
\S	Jeder Nicht-Whitespace-Charakter	\S	In 30 Cent übereinstimmt 30 und Cent
\ w	Wortcharakter: Jeder ASCII -Buchstaben, jede Ziffer oder Untersteuert	\ w	In 5_cats *** stimmt 5_cats überein
\ W	Jeder Charakter, der kein alphanumerischer Charakter oder unterstreicht	\ W	In 5_cats *** Matches ***
\T	Tab
\N	Neue Linie	\ n \ d	In der folgenden Zwei-Linie-Zeichenfolge übereinstimmt 10 5 Katzen 10 Hunde
\	Entkommt besondere Bedeutung eines Charakters, sodass Sie danach suchen können	\. \ W \.	Es entgeht einer Zeit, damit Sie das wörtliche finden können ". Charakter in einer Schnur Mr. , Mrs. , Prof.

Charakterklassen

Mit diesen Mustern können Sie Elemente verschiedener Zeichensätze übereinstimmen.

Muster	Beschreibung	Beispiel	Übereinstimmungen
[Charaktere]	Entspricht jedem einzelnen Charakter in den Klammern	D [oi] g	Hund und graben
[^Zeichen]	Entspricht einem einzelnen Charakter nicht in den Klammern	d [^oi] g	Passt DAG, Dug , D1g nicht zu Hund und Graben entspricht
[von -to]	Entspricht jedem Charakter im Bereich zwischen den Klammern	[0-9] [AZ] [AZ]	Jede einzelne Ziffer von 0 bis 9 beliebig ein einzelner Kleinbuchstaben in jedem einzelnen Großbuchstaben

Quantifizierer

Quantifizierer sind spezielle Ausdrücke, die die Anzahl der übereinstimmenden Zeichen angeben. Ein Quantifizierer gilt immer für den Charakter.

Muster	Beschreibung	Beispiel	Übereinstimmungen
*	Null oder mehr Ereignisse	1a*	1, 1a , 1aa, 1aaa usw. etc.
	Ein oder mehrere Ereignisse	po	In Pot entspricht PO in Armen , passt Poo
?	Null oder ein Vorkommen	Straße	Straße, Rute
*?	Null oder mehr Ereignisse, aber so weniger wie möglich	1a*?	In 1a , 1aa und 1aaa , Matches 1a
?	Ein oder mehrere Ereignisse, aber so weniger wie möglich	po?	In Topf und Armen passen Sie PO an
?	Null oder ein Ereignis, aber so weniger wie möglich	Roa ??	In Straße und Rod entspricht RO
{N}	Entspricht dem vorhergehenden Muster n mal	\ d {3}	Genau 3 Ziffern
{N,}	Entspricht dem vorhergehenden Muster n oder öfter Male	\ d {3,}	3 oder mehr Ziffern
{n, m}	Entspricht dem vorhergehenden Muster zwischen N und M -Zeiten	\ d {3,5}	Von 3 bis 5 Ziffern

Gruppierung

Gruppierungskonstrukte werden verwendet, um ein Substring aus der Quellzeichenfolge zu erfassen, sodass Sie einen Betriebsvorgang damit ausführen können.

Syntax	Beschreibung	Beispiel	Übereinstimmungen
(Muster)	Gruppe erfassen	(\D )	In 5 Katzen und 10 Hunden erfasst 5 (Gruppe 1) und 10 (Gruppe 2)
(?:Muster)	Nicht-Kauf-Gruppe: Übereinstimmung mit einer Gruppe, fängt sie jedoch nicht ein	(\ d) (?: Hunde)	In 5 Katzen und 10 Hunden fängt 10 10 ein
\ 1	Inhalt der Gruppe 1	(\ d) \ (\ d) = \ 2 \ \ 1	Übereinstimmung mit 5 10 = 10 5 und erfasst 5 und 10 , die in den Erfassungsgruppen enthalten sind
\ 2	Inhalt der Gruppe 2

Anker

Anker geben eine Position in der Eingangszeichenfolge an, in der nach einer Übereinstimmung gesucht werden soll.

Anker	Beschreibung	Beispiel	Übereinstimmungen
^	Beginn der String HINWEIS: [^Innenklammern] bedeutet "nicht"	^\ d	Eine beliebige Anzahl von Ziffern zu Beginn der Zeichenfolge. In 5 Katzen und 10 Hunden Spiele 5
$	Ende der String	\ d $	Eine beliebige Anzahl von Ziffern am Ende der Zeichenfolge. In 10 plus 5 gibt 15 Spiele 15
\B	Wortgrenze	\ bjoy \ b	Entspricht Freude als separates Wort, aber nicht angenehm .
\B	Keine Wortgrenze	\ Bjoy \ b	Entspricht Freude in Spaß , aber nicht als separates Wort.

Wechsel (oder) Konstrukt

Der Alternationsoperand ermöglicht die oder Logik, sodass Sie entweder dieses oder dieses Elements übereinstimmen können.

Konstrukt	Beschreibung	Beispiel	Übereinstimmungen
|	Entspricht jedem einzelnen Element, das durch die vertikale Balken getrennt ist	(s | sh) ells	In sie verkauft Sea-Shells, Spiele verkauft und Muscheln

Look-Arounds

Aussehende Konstrukte sind hilfreich, wenn Sie etwas anpassen möchten, das von etwas anderem befolgt wird oder nicht vorausgeht. Diese Ausdrücke werden manchmal als "Null-Width-Behauptungen" oder "Null-Width-Match" bezeichnet, da sie eher einer Position als einer tatsächlichen Zeichen entsprechen.

Notiz. Im VBA Regex -Geschmack werden nicht unterstützt.

Muster	Beschreibung	Beispiel	Übereinstimmungen
(? =)	Positiver Lookahead	X (? = Y)	Entspricht Ausdruck X, wenn y folgt (dh wenn es Y vor x vorhanden ist)
(?!)	Negativer Lookahead	X (?! Y)	Entspricht dem Ausdruck X, wenn es nicht von y folgt
(?	Positives Aussehen	(?	Entspricht dem Ausdruck X, wenn es Y vorausgeht (dh, wenn Y hinter X liegt)
(?	Negatives Aussehen	(?	Entspricht dem Ausdruck X

Nachdem Sie das Nötigste kennen, gehen wir zum interessantesten Teil über, um Regexes für reale Daten zu verwenden, um Zeichenfolgen zu analysieren und die erforderlichen Informationen zu finden. Wenn Sie weitere Details zur Syntax benötigen, kann sich die Microsoft -Leitfaden zur regulären Ausdruckssprache als hilfreich erweisen.

Benutzerdefinierte Regex -Funktionen für Excel

Wie bereits erwähnt, hat Microsoft Excel keine integrierten Regex-Funktionen. Um reguläre Ausdrücke zu ermöglichen, haben wir drei benutzerdefinierte VBA-Funktionen (auch bekannt als benutzerdefinierte Funktionen) erstellt. Sie können die Codes auf den unterliegenden Seiten oder aus unserer Beispiel-Arbeitsmappe kopieren und dann in Ihre eigenen Excel-Dateien einfügen.

Wie VBA Regexp -Funktionen funktionieren

Dieser Abschnitt erklärt die innere Mechanik und kann für diejenigen interessant sein, die genau wissen möchten, was im Backend passiert.

Um reguläre Ausdrücke in VBA zu verwenden, müssen Sie entweder die Regex -Objektreferenzbibliothek aktivieren oder die Funktion createObject verwenden. Um Ihnen die Mühe zu sparen, die Referenz im VBA -Editor festzulegen, haben wir uns für den letzteren Ansatz entschieden.

Das Regexp -Objekt hat 4 Eigenschaften:

• Muster - ist das Muster , das in der Eingangszeichenfolge übereinstimmt.
• Global - Kontrolliert, ob alle Übereinstimmungen in der Eingabezeichenfolge oder nur in der ersten finden sollen. In unseren Funktionen ist es auf wahr eingestellt, alle Übereinstimmungen zu erhalten.
• Multiline - Bestimmt, ob das Muster über Linienbrüche in Multi -Line -Zeichenfolgen oder nur in der ersten Zeile übereinstimmt. In unseren Codes wird es auf treu eingestellt, um in jeder Zeile zu suchen.
• IgnoreCase -definiert, ob ein regulärer Expression Fall-sensitiv (Standard) oder Fall-unempfindlich ist (auf TRUE eingestellt). In unserem Fall hängt dies davon ab, wie Sie den optionalen Match_case -Parameter konfigurieren. Standardmäßig sind alle Funktionen Fallempfindlichkeit .

VBA Regexp -Einschränkungen

Excel VBA implementiert die wesentlichen Regex -Muster, aber es fehlen viele erweiterte Funktionen, die in .NET, Perl, Java und anderen Regex -Motoren verfügbar sind. Zum Beispiel unterstützt VBA Regexp keine Inline-Modifikatoren wie (? I) für die von Fall unempfindliche Übereinstimmung oder (? M) für Multi-Line-Modus, LookBehinds, POSIX-Klassen, um nur einige zu nennen.

Excel Regex Match -Funktion

Die RegexpMatch -Funktion sucht nach einem Eingangszeichenfolge nach Text, der einem regulären Ausdruck entspricht und true zurückgibt, wenn eine Übereinstimmung gefunden wird, ansonsten falsch.

Regexpmatch (Text, Muster, [match_case])

Wo:

• Text (Erforderlich) - Ein oder mehrere Zeichenfolgen, die Sie suchen müssen.
• Muster (erforderlich) - Der reguläre Ausdruck, der übereinstimmt.
• Match_case (optional) - Übereinstimmungstyp. Wahr oder weggelassen - Fallempfindlich; Falsch - Fall -unempfindlich

Der Code der Funktion ist hier.

Beispiel: So verwenden Sie reguläre Ausdrücke, um Saiten zu entsprechen

Nehmen wir im folgenden Datensatz an, Sie möchten die Einträge mit SKU -Codes identifizieren.

Angesichts der Tatsache, dass jeder SKU mit 2 Großbuchstaben beginnt, gefolgt von einem Bindestrich, gefolgt von 4 Ziffern, können Sie sie unter Verwendung des folgenden Ausdrucks übereinstimmen.

Muster : \ b [Az] {2}-\ d {4} \ b

Wobei [Az] {2} 2 Großbuchstaben von A bis Z und \ d {4} bezeichnet 4 Ziffern von 0 bis 9. Eine Wortgrenze \ b zeigt an, dass ein SKU ein separates Wort und kein Teil einer größeren Zeichenfolge ist.

Mit dem festgelegten Muster tippen Sie eine Formel wie gewohnt, und der Name der Funktion wird in der Liste angezeigt, die von Excels automatischer Vervollständigung vorgeschlagen wird:

Unter der Annahme, dass die ursprüngliche Zeichenfolge in A5 ist, lautet die Formel wie folgt:

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

Aus Gründen der Einschätzung können Sie den regulären Ausdruck in einer separaten Zelle eingeben und eine absolute Referenz ($ A $ 2) für das Musterargument verwenden. Dies stellt sicher, dass die Zelladresse unverändert bleibt, wenn Sie die Formel in andere Zellen kopieren:

=RegExpMatch(A5, $A$2)

So zeigen Sie Ihre eigenen Textbezeichnungen anstelle von True und Falsch an, Nest RegexpMatch in der IF -Funktion und geben Sie die gewünschten Texte in den Argumenten Value_if_true und Value_if_false an:

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

Weitere Beispiele für Formel finden Sie unter:

• Wie man mit regelmäßigen Ausdrücken Saiten entspricht
• Excel -Datenvalidierung mit Regexes

Excel Regex Extract -Funktion

Die Regexpextract -Funktion sucht nach Substrings, die einem regulären Ausdruck entsprechen und alle Übereinstimmungen oder spezifischen Übereinstimmungen extrahiert.

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

Wo:

• Text (Erforderlich) - Die Suchzeichenfolge.
• Muster (erforderlich) - Der reguläre Ausdruck, der übereinstimmt.
• 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 der Textfall übereinstimmen (wahr oder weggelassen) oder (false) ignorieren soll.

Hier können Sie den Code der Funktion erhalten.

Beispiel: So extrahieren Sie Zeichenfolgen mit regulären Ausdrücken

Lassen Sie uns Rechnungsnummern extrahieren. Dafür werden wir eine sehr einfache Regex verwenden, die jeder 7-stelligen Nummer entspricht:

Muster : \ b \ d {7} \ b

Stellen Sie das Muster in A2 ein und Sie werden die Arbeit mit dieser kompakten und eleganten Formel erledigen:

=RegExpExtract(A5, $A$2)

Wenn ein Muster übereinstimmt, extrahiert die Formel eine Rechnungsnummer, wenn keine Übereinstimmung gefunden wird - wird nichts zurückgegeben.

Weitere Beispiele finden Sie unter: So extrahieren Sie Strings in Excel mit Regex.

Excel Regex Ersetzen Sie die Funktion ersetzen

Die Regexpreplace -Funktion ersetzt die Werte, die mit dem von Ihnen angegebenen Text übereinstimmen.

Regexpreplace (Text, Muster, Ersatz, [Instance_num], [Match_Case])

Wo:

• Text (Erforderlich) - Die Suchzeichenfolge.
• Muster (erforderlich) - Der reguläre Ausdruck, der übereinstimmt.
• Ersatz (Erforderlich) - Der Text zum Ersetzen der passenden Substrings durch.
• Instance_num (optional) - Die zu ersetzende Instanz. Die Standardeinstellung ist "alle Übereinstimmungen".
• MATCH_CASE (optional) - Steuert, ob er übereinstimmt (wahr oder weggelassen) oder ignorieren (falsche) Textfall.

Der Code der Funktion ist hier verfügbar.

Beispiel: So ersetzen oder entfernen Sie Zeichenfolgen mit Regexes

Einige unserer Unterlagen enthalten Kreditkartennummern. Diese Informationen sind vertraulich und Sie möchten sie möglicherweise durch etwas ersetzen oder insgesamt löschen. Beide Aufgaben können mit Hilfe der Regexpreplace -Funktion erledigt werden. Wie? In einem zweiten Szenario werden wir durch eine leere Zeichenfolge ersetzt.

In unserer Stichprobentabelle haben alle Kartennummern 16 Ziffern, die in 4 mit Räumen getrennten Gruppen geschrieben sind. Um sie zu finden, replizieren wir das Muster mit diesem regulären Ausdruck:

Muster : \ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

Zum Ersatz wird die folgende Zeichenfolge verwendet:

Ersatz : xxxx xxxx xxxx xxxx

Und hier ist eine vollständige Formel, um Kreditkartennummern durch unempfindliche Informationen zu ersetzen :

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

Mit dem Regex- und Ersatztext in separaten Zellen (A2 und B2) funktioniert die Formel gleich gut:

In Excel ist "Entfernen" ein besonderer Fall von "Ersetzen". Um Kreditkartennummern zu entfernen , verwenden Sie einfach eine leere Zeichenfolge ("") für das Ersatzargument :

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

Tipp. Um in den Ergebnissen leere Leitungen zu rüsten, können Sie eine andere Regexpreplace -Funktion verwenden, wie in diesem Beispiel gezeigt: So entfernen Sie leere Linien mit Regex.

Weitere Informationen finden Sie unter:

• So ersetzen Sie Strings in Excel mit Regex
• So entfernen Sie Strings mit Regex
• Wie man Weißespace mit Regexes auszieht

Regex -Tools übereinstimmen, extrahieren, ersetzen und entfernen Substrings

Die Benutzer unserer ultimativen Suite können die Leistung regulärer Ausdrücke erhalten, ohne eine einzige Codezeile in ihre Arbeitsmappen einzuführen. Alle erforderlichen Code werden von unseren Entwicklern geschrieben und Smoothy in Ihrem Excel während der Installation integriert.

Im Gegensatz zu den oben diskutierten VBA -Funktionen sind die Funktionen der Ultimate Suite auf .NET basiert, was zwei Hauptvorteile bietet:

1. Sie können reguläre Ausdrücke in normalen .xlsx-Arbeitsmappen verwenden, ohne VBA-Code hinzuzufügen und sie als makro-fähige Dateien zu speichern.
2. Die .NET Regex-Engine unterstützt klassische reguläre Ausdrücke mit vollem Einfachen, mit dem Sie anspruchsvollere Muster erstellen können.

So verwenden Sie Regex in Excel

Bei der installierten ultimativen Suite ist die Verwendung regulärer Ausdrücke in Excel so einfach wie diese beiden Schritte:

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

   

2. Führen Sie im Bereich Regex Tools Folgendes Folgendes aus:
   • Wählen Sie die Quelldaten aus.
   • Geben Sie Ihr Regex -Muster ein.
   • Wählen Sie die gewünschte Option: Übereinstimmen , extrahieren , entfernen oder ersetzen .
   • Um das Ergebnis als Formel und nicht als Wert zu erhalten, wählen Sie das Kontrollkästchen als Formel einfügen .
   • Klicken Sie auf die Aktionstaste.

   Um beispielsweise Kreditkartennummern aus den Zellen A2: A6 zu entfernen, konfigurieren wir diese Einstellungen:

   

In einem Trice wird eine Fähigkeitsregex -Funktion in einer neuen Spalte rechts von Ihren Originaldaten eingefügt. In unserem Fall lautet die Formel:

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

Sobald die Formel vorhanden ist, können Sie sie wie jede native Formel bearbeiten, kopieren oder verschieben.

So setzen Sie eine Regex -Formel direkt in eine Zelle ein

Die Funktionen für fungbitsRegex können auch direkt in eine Zelle eingefügt werden, ohne die Schnittstelle des Add-Ins zu verwenden. So wie: wie:

1. Klicken Sie in der Formelleiste auf die FX -Schaltfläche oder fügen Sie auf der Registerkarte Formeln ein.
2. Wählen Sie im Dialogfeld Funktion einfügenfunktion die Kategorie "fungbitsudfs" , wählen Sie die Interesse aus und klicken Sie auf OK.

   

3. Definieren Sie die Argumente der Funktion wie normalerweise und klicken Sie auf OK. Erledigt!

Weitere Informationen finden Sie in Regex Tools für Excel.

So verwenden Sie regelmäßige Ausdrücke, um Text in Excel -Zellen zu übereinstimmen, zu extrahieren, zu ersetzen und zu entfernen. 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 - Formel Beispiele (.xlsm -Datei) Ultimate Suite - Testversion (.exe -Datei)

Das obige ist der detaillierte Inhalt vonBeispiele für Excel Regex: Verwenden regelmäßiger Ausdrücke in Formeln. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!