Regex, um bestimmte Zeichen oder Text in Excel zu entfernen

Haben Sie jemals gedacht, wie leistungsfähig Excel wäre, wenn jemand seine Toolbox mit regelmäßigen Ausdrücken bereichern könnte? Wir haben nicht nur darüber nachgedacht, sondern auch daran gearbeitet :) Und jetzt können Sie diese wundervolle Regex -Funktion Ihrer eigenen Arbeitsmappen hinzufügen und Substrings auslöschen, die in kürzester Zeit ein Muster entsprechen!

Letzte Woche haben wir uns angesehen, wie man reguläre Ausdrücke verwendet, um Saiten in Excel zu ersetzen. Dafür haben wir eine benutzerdefinierte Regex -Ersatzfunktion erstellt. Wie sich herausstellte, geht die Funktion über ihren primären Gebrauch hinaus und kann nicht nur Zeichenfolgen ersetzen, sondern sie auch entfernen. Wie könnte das sein? In Bezug auf Excel ist das Entfernen eines Wertes nichts anderes als das Ersetzen durch eine leere Zeichenfolge, etwas, in dem unsere Regex -Funktion sehr gut ist!

• VBA Regexp -Funktion zum Entfernen von Substrings
• Excel Regex Beispiele entfernen
  • Löschen Sie alle Übereinstimmungen oder spezifischen Übereinstimmungen
  • Bestimmte Zeichen streifen
  • Sonderzeichen entfernen
  • Nicht numerische Zeichen löschen
  • Zeichnen Sie alles nach dem Weltraum aus
  • Text nach einem bestimmten Zeichen entfernen
  • Alles vor dem Weltraum reinigen
  • Entfernen Sie alles vor dem Charakter
  • Entfernen Sie alles außer
  • Regelmäßiger Ausdruck zum Entfernen von HTML -Tags
• Fähigbits regex entfernen Tool

VBA Regexp -Funktion zum Entfernen von Substrings in Excel

Wie wir alle wissen, werden regelmäßige Ausdrücke standardmäßig nicht in Excel unterstützt. Um sie zu aktivieren, müssen Sie Ihre eigene benutzerdefinierte Funktion erstellen. Die gute Nachricht ist, dass eine solche Funktion bereits geschrieben, getestet und verwendet werden. Alles, was Sie tun müssen, ist, diesen Code zu kopieren, ihn in Ihren VBA-Editor einzufügen und Ihre Datei als makro-fähiger Arbeitsmappe (.xlsm) zu speichern.

Die Funktion hat die folgende Syntax:

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

Die ersten drei Argumente sind erforderlich, die letzten beiden sind optional.

Wo:

• Text - Die Suchzeichenfolge.
• Muster - Der reguläre Ausdruck, nach dem man suchen sollte.
• Austausch - Der zu ersetzende Text. Verwenden Sie zum Entfernen von Substrings , die dem Muster entsprechen, eine leere Zeichenfolge ("") für den Austausch.
• Instance_num (optional) - Die zu ersetzende Instanz. Wenn es weggelassen wird, werden alle gefundenen Übereinstimmungen ersetzt (Standard).
• Match_case (optional) - Ein Booleaner Wert, der angibt, ob sie einen Textfall übereinstimmen oder ignorieren sollen. Verwenden Sie für Fallsensitive Übereinstimmungen True (Standard). für Fall -unempfindlich - Falsch.

Weitere Informationen finden Sie in der Regexpreplace -Funktion.

Tipp. In einfachen Fällen können Sie bestimmte Zeichen oder Wörter aus Zellen mit Excel -Formeln entfernen. Damit sind regelmäßige Ausdrücke dafür jedoch viel mehr Optionen.

So entfernen Sie Zeichenfolgen mit regelmäßigen Ausdrücken - Beispiele

Wie oben erwähnt, müssen Sie diese, um Teile des Textes zu entfernen, das einem Muster entspricht, sie durch eine leere Zeichenfolge ersetzen. Eine generische Formel nimmt diese Form an:

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

Die folgenden Beispiele zeigen verschiedene Implementierungen dieses grundlegenden Konzepts.

Entfernen Sie alle Übereinstimmungen oder spezifischen Übereinstimmungen

Die Regexpreplace -Funktion ist so konzipiert, dass alle Substrings mit einem bestimmten Regex entsprechen. Das zu entfernene Ereignisse wird durch das ^4. optionale Argument namens Instance_Num gesteuert.

Die Standardeinstellung ist "alle Übereinstimmungen" - wenn das Argument für Instance_num weggelassen wird, werden alle gefundenen Übereinstimmungen entfernt. Definieren Sie die Instanznummer, um eine bestimmte Übereinstimmung zu löschen.

Nehmen wir in den folgenden Zeichenfolgen an, Sie möchten die erste Bestellnummer löschen. Alle diese Zahlen beginnen mit dem Hash -Zeichen (#) und enthalten genau 5 Ziffern. Wir können sie also mit diesem Regex identifizieren:

Muster : #\ d {5} \ b

Die Wortgrenze \ b gibt an, dass ein passendes Substring nicht Teil einer größeren Zeichenfolge wie #10000001 sein kann.

Um alle Übereinstimmungen zu entfernen, ist das Argument von Instance_num nicht definiert:

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

Um nur das erste Ereignis auszurotten, setzen wir das Argument für Instance_num auf 1:

=RegExpReplace(A5, "#\d{5}\b", "", 1)

Regex, um bestimmte Zeichen zu entfernen

Um bestimmte Zeichen aus einer Zeichenfolge abzuziehen, schreiben Sie einfach alle unerwünschten Zeichen auf und trennen Sie sie mit einer vertikalen Balken | das wirkt als ODER OR OPERATOR in Regexes.

Um beispielsweise Telefonnummern in verschiedenen Formaten zu standardisieren, werden wir zunächst bestimmte Zeichen wie Klammern, Bindestriche, Punkte und Whitespaces los.

Muster : \ (| \) |-| \. | \ S

=RegExpReplace(A5, "\(|\)|-|\.|\s", "")

Das Ergebnis dieser Operation ist eine 10-stellige Zahl wie "1234567890".

Für den Einfachheit halber können Sie in die Regex eintreten, die eine separate Zelle ist, und sich mit einer absoluten Referenz wie 2 $ 2 auf diese Zelle beziehen:

=RegExpReplace(A5, $A$2, "")

Und dann können Sie die Formatierung der gewünschten Art und Weise standardisieren, indem Sie den Verkettungsoperator (&) und Textfunktionen wie rechts, mitten und links verwenden.

Um beispielsweise alle Telefonnummern in das Format (123) 456-7890 zu schreiben, lautet die Formel:

="("&LEFT(B5, 3)&") "&MID(B5, 4, 3)&"-"&RIGHT(B5, 4)

Wobei B5 die Ausgabe der Regexpreplace -Funktion ist.

Entfernen Sie Sonderzeichen mit Regex

In einem unserer Tutorials haben wir uns angesehen, wie unerwünschte Zeichen in Excel mit eingebauten und benutzerdefinierten Funktionen entfernt werden. Regelmäßige Ausdrücke machen es viel einfacher! Anstatt alle Zeichen zu löschen, geben Sie einfach die an, die Sie behalten möchten :)

Das Muster basiert auf negierten Charakterklassen - eine Pflege wird in eine Charakterklasse [^] eingefügt, um ein einzelnes Zeichen zu entsprechen, das nicht in Klammern ist. Der Quantifizierer erzwingt es, aufeinanderfolgende Zeichen als einzelne Übereinstimmung zu betrachten, so dass ein Ersatz für ein passendes Substring und nicht für jeden einzelnen Charakter durchgeführt wird.

Wählen Sie je nach Ihren Bedürfnissen einen der folgenden Regexes aus.

Um nicht alphanumerische Zeichen zu entfernen, dh alle Zeichen außer Buchstaben und Ziffern:

Muster : [^0-9a-za-Z]

Alle Zeichen außer Buchstaben , Ziffern und Räumen reinigen:

Muster : [^0-9a-za-Z]

Um alle Zeichen außer Buchstaben , Ziffern und Unterstrichen zu löschen, können Sie \ W verwenden, das für ein Zeichen steht, das kein alphanumerischer Charakter oder Unterstrich ist:

Muster : \ w

Wenn Sie einige andere Charaktere behalten möchten, z. B. Interpunktionszeichen, legen Sie sie in die Klammern.

Verwenden Sie beispielsweise einen anderen Charakter als einen Buchstaben, eine Ziffer, Periode, ein Komma oder einen Platz, verwenden Sie die folgenden Regex:

Muster : [^0-9a-za-z \.,]

Dies eliminiert erfolgreich alle Sonderfiguren, aber zusätzliche Whitespace bleibt bestehen.

Um dies zu beheben, können Sie die obige Funktion in eine andere nisten, die mehrere Räume durch einen einzelnen Speicherzeichen ersetzt.

=RegExpReplace(RegExpReplace(A5,$A$2,""), " ", " ")

Oder verwenden Sie einfach die native Trimmfunktion mit dem gleichen Effekt:

=TRIM(RegExpReplace(A5, $A$2, ""))

Regex, um nicht numerische Zeichen zu entfernen

Um alle nicht numerischen Zeichen aus einer Zeichenfolge zu löschen, können Sie entweder diese lange Formel oder eine der sehr einfachen Regexes verwenden, die unten aufgeführt sind.

Passen Sie zu jedem Charakter, der keine Ziffer ist:

Muster : \ d

Streifen Sie nicht numerische Zeichen mit negierten Klassen:

Muster : [^0-9]

Muster : [^\ d]

Tipp. Wenn Ihr Ziel es ist, Text zu entfernen und die verbleibenden Zahlen in getrennte Zellen zu verschütten oder sie alle in einer Zelle zu platzieren, die mit einem angegebenen Trennzeichen getrennt ist, verwenden Sie die Regexpextract -Funktion, wie in der Art und Weise, wie man Zahlen aus der Zeichenfolge extrahiert, mit regulären Ausdrücken erläutert.

Regex, um alles nach dem Raum zu entfernen

Um alles nach einem Raum auszulöschen, verwenden Sie entweder den Space () oder die Whitespace (\ s) -Scharakter, um den ersten Raum zu finden, und*, um alle Zeichen danach zu entsprechen.

Wenn Sie einzelne Zeichenfolgen haben, die nur normale Räume enthalten (Wert 32 im 7-Bit-ASCII-System), spielt es keine Rolle, welche der folgenden Regexes Sie verwenden. Bei Multi-Line-Zeichenfolgen macht es einen Unterschied.

Verwenden Sie diese Regex, um alles nach einem Speicherzeichen zu entfernen:

Muster : " .*"

=RegExpReplace(A5, " .*", "")

Diese Formel wird nach dem ersten Raum in jeder Zeile alles ausziehen. Damit die Ergebnisse korrekt angezeigt werden können, stellen Sie sicher, dass Sie den Text einschalten.

Um alles nach einer Whitespace (einschließlich eines Platzes, einer Registerkarte, einer Kutschenrendite und einer neuen Linie) auszuziehen, ist der Regex:

Muster : \ s.*

=RegExpReplace(A5, "\s.*", "")

Da \ s ein paar verschiedenen Whitespace -Typen entspricht, darunter eine neue Zeile (\ n), löscht diese Formel alles nach dem ersten Raum in einer Zelle, egal wie viele Zeilen darin enthalten sind.

Regex zum Entfernen von Text nach einem bestimmten Zeichen

Mit den Methoden aus dem vorherigen Beispiel können Sie Text nach jedem von Ihnen angegebenen Zeichen ausrotten.

Um jede Zeile separat zu behandeln:

Generisches Muster : Char.*

In einzelnen Zeichenfolgen wird dies alles nach dem Zeichen entfernen. In Multi-Line-Zeichenfolgen wird jede Zeile einzeln verarbeitet, da im VBA Regex-Geschmack eine Periode (.) Mit Ausnahme einer neuen Linie übereinstimmt.

Um alle Zeilen als einzelne Zeichenfolge zu verarbeiten:

Generisches Muster : char (. | \ N)*

Um etwas nach einem bestimmten Zeichen zu löschen, einschließlich neuer Zeilen, wird \ n zum Muster hinzugefügt.

Probieren Sie beispielsweise den Text nach dem ersten Komma in einer Zeichenfolge aus, um diese regulären Ausdrücke zu probieren:

Muster : ,.*

Muster :, (. | \ N)*

Im folgenden Screenshot können Sie untersuchen, wie sich die Ergebnisse unterscheiden.

Regex, um alles vor dem Raum zu entfernen

Bei der Arbeit mit langen Textzeichenfolgen möchten Sie sie möglicherweise kürzer machen, indem Sie denselben Teil der Informationen in allen Zellen entfernen. Im Folgenden werden wir zwei solcher Fälle besprechen.

Entfernen Sie alles vor dem letzten Platz

Wie beim vorherigen Beispiel hängt ein regulärer Ausdruck von Ihrem Verständnis eines "Raums" ab.

Um irgendetwas bis zum letzten Raum zu entsprechen, wird dieser Regex (Anführungszeichen werden hinzugefügt, um nach einem Sternchen einen Platz zu schaffen).

Muster : ".* "

Verwenden Sie diesen regulären Ausdruck, um etwas vor dem letzten Whitespace (einschließlich eines Platzes, einer Registerkarte, einer Kutschenrendite und einer neuen Linie) zu entsprechen.

Muster :.*\ S

Der Unterschied macht sich besonders bei Multi-Line-Saiten bemerkbar.

Streifen Sie alles vor den ersten Raum aus

Um irgendetwas bis zum ersten Raum in einer Zeichenfolge abzustimmen, können Sie diesen regulären Ausdruck verwenden:

Muster : ^ [ ^]*

Ab dem Beginn einer String ^ stimmen wir mit Null oder mehr Nicht-Space-Zeichen [ ^]* überein, auf die unmittelbar ein oder mehrere Leerzeichen gefolgt werden. Der letzte Teil wird hinzugefügt, um potenzielle führende Räume in den Ergebnissen zu verhindern.

Um den Text vor dem ersten Platz in jeder Zeile zu entfernen, ist die Formel im Standardmodus "Alle Übereinstimmungen" ( Instance_num weggelassen) geschrieben:

=RegExpReplace(A5, "^[^ ]* ", "")

Um den Text vor dem ersten Raum in der ersten Zeile zu löschen und alle anderen Zeilen intakt zu lassen, wird das Argument von Instance_Num auf 1 gesetzt:

=RegExpReplace(A5, "^[^ ]* ", "", 1)

Regex, um alles vor dem Charakter auszuziehen

Der einfachste Weg, den gesamten Text vor einem bestimmten Zeichen zu entfernen, besteht darin, einen Regex wie diesen zu verwenden:

Generisches Muster : ^[ ^char]*char

In eine menschliche Sprache übersetzt, heißt es: "Von Beginn einer von ^verankerten Zeichenfolge, die von ^0 oder mehr Zeichen mit Ausnahme von Char [ ^char]* bis zum ersten Ereignis von char verankert ist.

Verwenden Sie diesen regulären Ausdruck beispielsweise, um den gesamten Text vor dem ersten Dickdarm zu löschen:

Muster : ^[ ^:]*:

Fügen Sie ein Whitespace -Zeichen \ s* zum Ende hinzu, um das Ende von Leerzeichen zu vermeiden. Dadurch wird alles vor dem ersten Dickdarm entfernt und gleich danach alle Räume geschnitten:

Muster : ^[ ^:]*: \ S*

=RegExpReplace(A5, "^[^:]*:\s*", "")

Tipp. Neben regelmäßigen Ausdrücken hat Excel seine eigenen Mittel, um Text nach Position oder Übereinstimmung zu entfernen. Um zu erfahren, wie Sie die Aufgabe mit nativen Formeln erledigen können, finden Sie in Excel, wie Sie den Text vor oder nach einem Zeichen in Excel entfernen.

Regex, um alles außer zu entfernen, außer

Verwenden Sie negierte Zeichenklassen, um alle Zeichen aus einer Zeichenfolge auszusetzen, außer denjenigen, die Sie behalten möchten.

Um beispielsweise alle Zeichen außer Kleinbuchstaben und Punkten zu entfernen, ist der Regex:

Muster : [^az \.]

Tatsächlich könnten wir hier auf den Quantifizierer verzichten, da unsere Funktion alle gefundenen Übereinstimmungen ersetzt. Der Quantifizierer macht es nur etwas schneller - anstatt jeden einzelnen Charakter zu behandeln, ersetzen Sie ein Substring.

=RegExpReplace(A5, "[^az\.] ", "")

Regex zum Entfernen von HTML -Tags in Excel

Zunächst einmal ist zu beachten, dass HTML keine reguläre Sprache ist. Es ist daher nicht der beste Weg, sie mit regulären Ausdrücken zu analysieren. Trotzdem können Regexes definitiv helfen, Tags aus Ihren Zellen zu streifen, um Ihren Datensatz sauberer zu machen.

Angesichts der Tatsache, dass HTML -Tags immer in Winkelklammern platziert sind, finden Sie sie mit einem der folgenden Regexes.

Negierte Klasse:

Muster : ]*>

Hier stimmen wir zu einer Öffnungswinkelklammer, gefolgt von Null oder mehr Vorkommen eines jeden Zeichens, mit Ausnahme der Schließwinkelhalterung [^>]* bis zur nächsten Schlusswinkelhalterung.

Faule Suche:

Muster : <.>

Hier passen wir alles von der ersten Eröffnungsklasse bis zur ersten Abschlussklasse. Die Fragezeichen erfordern.* Mit so wenigen Charakteren wie möglich übereinstimmen, bis sie eine Schließklammer findet.

Welches Muster Sie wählen, das Ergebnis ist absolut das gleiche.

Um beispielsweise alle HTML -Tags aus einer Zeichenfolge in A5 zu entfernen und Text zu verlassen, lautet die Formel:

=RegExpReplace(A5, "]*>", "")

Oder Sie können den faulen Quantifizierer wie im Screenshot gezeigt verwenden:

Diese Lösung eignet sich perfekt für einzelne Text (Zeilen 5 - 9). Für mehrere Texte (Zeilen 10 - 12) sind die Ergebnisse fragwürdig - Texte aus verschiedenen Tags werden zu einem zusammengeführt. Ist das richtig oder nicht? Ich fürchte, es kann nicht leicht entschieden werden - alles hängt von Ihrem Verständnis des gewünschten Ergebnisses ab. Zum Beispiel wird in B11 das Ergebnis "A1" erwartet; Während in B10 möchten Sie, dass "Data1" und "Data2" mit einem Raum getrennt werden können.

Um HTML -Tags zu entfernen und die verbleibenden Texte mit Leerzeichen zu trennen, können Sie auf diese Weise fortfahren:

1. Ersetzen Sie Tags durch Leerzeichen "", keine leeren Zeichenfolgen:

   =RegExpReplace(A5, "]*>", " ")

2. Reduzieren Sie mehrere Räume auf einen einzelnen Raumcharakter:

   =RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " ")

3. Trimm führende und nachverfolgende Räume:

   =TRIM(RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " "))

Das Ergebnis sieht ungefähr so ​​aus:

Fähigbits regex entfernen Tool

Wenn Sie die Chance hatten, unsere Ultimate Suite für Excel zu verwenden, haben Sie wahrscheinlich bereits die neuen Regex -Tools entdeckt, die mit der jüngsten Veröffentlichung eingeführt wurden. Das Schöne an diesen .NET-basierten Regex-Funktionen ist, dass sie zunächst die Syntax mit vollemakfvollem Expression frei von VBA Regexp-Einschränkungen unterstützen und zweitens keinen VBA-Code in Ihren Arbeitsmappen einfügen müssen, da alle Code-Integration von uns im Backend durchgeführt wird.

Ihr Teil der Aufgabe ist es, einen regelmäßigen Ausdruck zu konstruieren und ihm der Funktion zu dienen :) Lassen Sie mich Ihnen zeigen, wie man das auf einem praktischen Beispiel macht.

So entfernen Sie Text in Klammern und Klammern mit Regex

In langen Textketten werden häufig weniger wichtige Informationen in [Klammern] und (Klammern) eingeschlossen. Wie entfernen Sie diese irrelevanten Details, die alle anderen Daten behalten?

Tatsächlich haben wir bereits eine ähnliche Regex zum Löschen von HTML -Tags, dh Text in Winkelklammern, gebaut. Offensichtlich funktionieren die gleichen Methoden auch für quadratische und runde Klammern.

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

Der Trick besteht darin, einen faulen Quantifizierer (*?) Zu verwenden, um dem kürzestmöglichen Substring zu entsprechen. Die erste Gruppe (\ (.*? \)) Entspricht etwas von einer Öffnungsenthülle bis zur ersten Schließklammerin. Die zweite Gruppe (\ [.*? \]) Entspricht etwas von einer Eröffnungsklasse bis zur ersten Schließklasse. Eine vertikale Balken | fungiert als ODER OPER.

Lassen Sie uns mit dem Muster zu unserer Regex -Entfernungsfunktion "füttern". So wie: wie:

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

   

2. Wählen Sie im Bereich Regex Tools Ihre Quellzeichenfolgen, geben Sie Ihre Regex ein, wählen Sie die Option Entfernen und drücken Sie das Entfernen .

   Um die Ergebnisse als Formeln und nicht als Werte zu erhalten, wählen Sie das Kontrollkästchen als Formel einfügen .

   Um Text in Klammern von Zeichenfolgen in A2: A5 zu entfernen, konfigurieren wir die Einstellungen wie folgt:

   

Als Ergebnis wird die Funktion "fungbitSregexremove" in einer neuen Spalte neben Ihren Originaldaten eingefügt.

Die Funktion kann auch über das Dialogfeld Standardeinfügungsfunktion direkt in eine Zelle eingegeben werden, in der sie unter fungritbitsudfs kategorisiert ist.

Da es sich um handelbitsRegexremove handelt, müssen Text nur zwei Argumente entfernen - die Quellzeichenfolge und Regex. Beide Parameter können direkt in einer Formel definiert oder in Form von Zellreferenzen geliefert werden. Bei Bedarf kann diese benutzerdefinierte Funktion zusammen mit nativen verwendet werden.

Um beispielsweise zusätzliche Räume in den resultierenden Zeichenfolgen zu schneiden, können Sie die Trimmfunktion als Wrapper verwenden:

=TRIM(AblebitsRegexRemove(A5, $A$2))

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

Verfügbare Downloads

Zeichenfolgen mit Regex - Beispiele (.xlsm -Datei) Ultimate Suite - Testversion (.exe -Datei) entfernen

Das obige ist der detaillierte Inhalt vonRegex, um bestimmte Zeichen oder Text in Excel zu entfernen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!