Wenn Sie mit Textdaten in Excel arbeiten, müssen Sie möglicherweise den Text vor oder nach einem bestimmten Zeichen oder einer bestimmten Textzeichenfolge entfernen.
Wenn Sie beispielsweise die Namens- und Bezeichnungsdaten von Personen haben, möchten Sie möglicherweise die Bezeichnung nach dem Komma entfernen und nur den Namen beibehalten (oder umgekehrt, wenn Sie die Bezeichnung beibehalten und den Namen entfernen).
Manchmal kann dies mit einer einfachen Formel oder einem schnellen Suchen und Ersetzen erfolgen, und manchmal sind komplexere Formeln oder Problemumgehungen erforderlich.
In diesem Tutorial zeige ich Ihnen, wie Sie den Text vor oder nach einem bestimmten Zeichen in Excel entfernen (anhand verschiedener Beispiele).
Beginnen wir also mit einigen einfachen Beispielen.
Entfernen Sie Text nach einem Zeichen mit Suchen und Ersetzen
Wenn Sie den gesamten Text nach einer bestimmten Textzeichenfolge (oder vor einer Textzeichenfolge) schnell entfernen möchten, können Sie dies mithilfe von Suchen und Ersetzen und Platzhalterzeichen tun.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Bezeichnung nach dem Komma entfernen und den Text vor dem Komma beibehalten.
Im Folgenden sind die Schritte dazu aufgeführt:
- Kopieren Sie die Daten von Spalte A in Spalte B und fügen Sie sie ein (damit werden auch die Originaldaten beibehalten)
- Wenn die Zellen in Spalte B ausgewählt sind, klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Bearbeiten auf die Option Suchen & Auswählen
- Klicken Sie in den Optionen, die in der Dropdown-Liste angezeigt werden, auf die Option Ersetzen. Dadurch wird das Dialogfeld Suchen und Ersetzen geöffnet
- Geben Sie in das Feld „Suchen nach“ ein ,* (d. h. Komma gefolgt von einem Sternchen)
- Lassen Sie das Feld „Ersetzen durch“ leer
- Klicken Sie auf die Schaltfläche Alle ersetzen
Die obigen Schritte würden das Komma im Datensatz finden und den gesamten Text nach dem Komma (einschließlich des Kommas) entfernen.
Da dies den Text aus den ausgewählten Zellen ersetzt, ist es eine gute Idee, den Text in eine andere Spalte zu kopieren und dann diesen Such- und Ersetzungsvorgang durchzuführen oder eine Sicherungskopie Ihrer Daten zu erstellen, damit die Originaldaten intakt sind
Wie funktioniert das?
* (Sternchen) ist ein Platzhalterzeichen, das eine beliebige Anzahl von Zeichen darstellen kann.
Wenn ich es nach dem Komma verwende (im Feld "Suchen nach") und dann auf die Schaltfläche Alle ersetzen klicke, findet es das erste Komma in der Zelle und betrachtet es als Übereinstimmung.
Dies liegt daran, dass das Sternchen (*) als Übereinstimmung mit der gesamten Textzeichenfolge angesehen wird, die auf das Komma folgt.
Wenn Sie also auf die Schaltfläche "Alle ersetzen" klicken, werden das Komma und der gesamte folgende Text ersetzt.
Notiz: Diese Methode funktioniert gut, wenn Sie in jeder Zelle nur ein Komma haben (wie in unserem Beispiel). Falls Sie mehrere Kommas haben, würde diese Methode immer das erste Komma finden und dann alles danach entfernen. Sie können diese Methode also nicht verwenden, wenn Sie den gesamten Text nach dem zweiten Komma ersetzen und den ersten unverändert beibehalten möchten.Falls Sie alle Zeichen vor dem Komma entfernen möchten, ändern Sie den Eintrag im Feld Suchen nach, indem Sie das Sternzeichen vor dem Komma (* statt ,*) setzen.
Text mit Formeln entfernen
Wenn Sie mehr Kontrolle darüber benötigen, wie Sie Text vor oder nach einem bestimmten Zeichen suchen und ersetzen, ist es besser, die integrierten Textformeln in Excel zu verwenden.
Angenommen, Sie haben den folgenden Datensatz, in dem Sie den gesamten Text nach dem Komma entfernen möchten.
Unten ist die Formel, um dies zu tun:
=LINKS(A2,SUCHEN(",",A2)-1)
Die obige Formel verwendet die Funktion FIND, um die Position des Kommas in der Zelle zu finden.
Diese Positionsnummer wird dann von der LEFT-Funktion verwendet, um alle Zeichen vor dem Komma zu extrahieren. Da ich kein Komma als Teil des Ergebnisses haben möchte, habe ich 1 vom Ergebniswert der Find-Formel abgezogen.
Dies war ein einfaches Szenario.
Nehmen wir ein etwas komplexes.
Angenommen, ich habe diesen Datensatz unten, in dem ich den gesamten Text nach dem zweiten Komma entfernen möchte.
Hier ist die Formel, die dies tut:
=LINKS(A2,SUCHEN("!",ERSETZEN(A2,",","!",2))-1)
Da dieser Datensatz mehrere Kommas enthält, kann ich die Funktion FIND nicht verwenden, um die Position des ersten Kommas zu ermitteln und alles links davon zu extrahieren.
Ich muss irgendwie die Position des zweiten Kommas herausfinden und dann alles extrahieren, was links vom zweiten Komma steht.
Dazu habe ich die Funktion SUBSTITUTE verwendet, um das zweite Komma in ein Ausrufezeichen zu ändern. Das gibt mir jetzt einen einzigartigen Charakter in der Zelle. Ich kann jetzt die Position des Ausrufezeichens verwenden, um alles links vom zweiten Komma zu extrahieren.
Diese Position des Ausrufezeichens wird in der LEFT-Funktion verwendet, um alles vor dem zweiten Komma zu extrahieren.
So weit, ist es gut!
Aber was ist, wenn der Datensatz eine inkonsistente Anzahl von Kommas enthält.
Im folgenden Datensatz haben beispielsweise einige Zellen zwei Kommas und einige Zellen drei Kommas, und ich muss den gesamten Text vor dem letzten Komma extrahieren.
In diesem Fall muss ich irgendwie die Position des letzten Vorkommens des Kommas herausfinden und dann alles links davon extrahieren.
Unten ist die Formel, die das tut
=LINKS(A2,FINDEN("!",ERSETZEN(A2,",","!",LEN(A2)-LEN(ERSETZEN(A2,",",""))))-1)
Die obige Formel verwendet die LEN-Funktion, um die Gesamtlänge des Textes in der Zelle sowie die Länge des Textes ohne Komma zu ermitteln.
Wenn ich diese beiden Werte subtrahiere, erhalte ich die Gesamtzahl der Kommas in der Zelle.
Also würde ich 3 für Zelle A2 und 2 für Zelle A4 geben.
Dieser Wert wird dann innerhalb der SUBSTITUTE-Formel verwendet, um das letzte Komma durch ein Ausrufezeichen zu ersetzen. Und dann können Sie mit der linken Funktion alles extrahieren, was sich links vom Ausrufezeichen befindet (wo früher das letzte Komma war)
Wie Sie in den Beispielen sehen können, können Sie mit einer Kombination von Textformeln viele verschiedene Situationen bewältigen.
Da das Ergebnis mit den Originaldaten verknüpft ist, wird das Ergebnis automatisch aktualisiert, wenn Sie die Originaldaten ändern.
Text mit Flash Fill entfernen
Flash Fill ist ein Tool, das in Excel 2013 eingeführt wurde und in allen Versionen danach verfügbar ist.
Es funktioniert, indem es Muster identifiziert, wenn Sie die Daten manuell eingeben und dann extrapolieren, um die Daten für die gesamte Spalte zu erhalten.
Wenn Sie also den Text vor oder nach einem bestimmten Zeichen entfernen möchten, müssen Sie Flash Fairy nur zeigen, wie das Ergebnis aussehen würde (indem Sie es ein paar Mal manuell eingeben), und Flash Fill würde das Muster automatisch verstehen und Ihnen geben die Ergebnisse.
Lassen Sie mich Ihnen dies an einem Beispiel zeigen.
Unten habe ich den Datensatz, in dem ich den gesamten Text nach dem Komma entfernen möchte.
Hier sind die Schritte, um dies mit Flash Fill zu tun:
- Geben Sie in Zelle B2, der angrenzenden Spalte unserer Daten, manuell "Jeffery Haggins" ein (das ist das erwartete Ergebnis).
- Geben Sie in Zelle B3 "Tim Scott" ein (das ist das erwartete Ergebnis für die zweite Zelle)
- Wählen Sie den Bereich B2: B10
- Klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Bearbeiten auf die Dropdown-Option Füllen
- Klicken Sie auf Flash Fill
Die obigen Schritte würden Ihnen das Ergebnis wie unten gezeigt liefern:
Sie können auch die Tastenkombination Flash Fill verwenden Strg + E nach Auswahl der Zellen in der Ergebnisspalte (Spalte B in unserem Beispiel)
Flash Fill ist ein wunderbares Werkzeug und in den meisten Fällen ist es in der Lage, das Muster zu erkennen und Ihnen das richtige Ergebnis zu liefern. In einigen Fällen kann es jedoch das Muster möglicherweise nicht richtig erkennen und Ihnen falsche Ergebnisse liefern.
Stellen Sie also sicher, dass Sie Ihre Flash Fill-Ergebnisse noch einmal überprüfen
Und genau wie wir den gesamten Text nach einem bestimmten Zeichen mit Flashfill entfernt haben, können Sie die gleichen Schritte verwenden, um den Text vor einem bestimmten Zeichen zu entfernen. zeige einfach flashfill wie das Ergebnis wie mein Internet aussehen soll manuell in der nebenstehenden Spalte, und es würde den Rest erledigen.
Text mit VBA entfernen (benutzerdefinierte Funktion)
Das gesamte Konzept, den Text vor oder nach einem bestimmten Zeichen zu entfernen, hängt davon ab, die Position dieses Zeichens zu finden.
Wie oben gesehen, bedeutet, das letzte Vorkommen dieses Zeichens als gut zu finden, eine Mischung aus Formeln zu verwenden.
Wenn dies häufig erforderlich ist, können Sie diesen Vorgang vereinfachen, indem Sie eine benutzerdefinierte Funktion mit VBA erstellen (sogenannte benutzerdefinierte Funktionen).
Einmal erstellt, können Sie diese Funktion immer wieder verwenden. Es ist auch viel einfacher und benutzerfreundlicher (da die meisten schweren Arbeiten vom VBA-Code im Backend erledigt werden).
Unterhalb des VBA-Codes, mit dem Sie die benutzerdefinierte Funktion in Excel erstellen können:
Function LastPosition(rCell As Range, rChar As String) 'Diese Funktion liefert die letzte Position des angegebenen Zeichens 'Dieser Code wurde von Sumit Bansal (https://trumpexcel.com) entwickelt Dim rLen As Integer rLen = Len(rCell) For i = rLen To 1 Step -1 If Mid(rCell, i - 1, 1) = rChar Then LastPosition = i - 1 Exit Function End If Next i End Function
Sie müssen den VBA-Code in einem regulären Modul im VB-Editor oder in der Personal Macro Workbook platzieren. Sobald Sie es dort haben, können Sie es wie jede andere normale Arbeitsblattfunktion in der Arbeitsmappe verwenden.
Diese Funktion benötigt 2 Argumente:
- Der Zellbezug, für den Sie das letzte Vorkommen eines bestimmten Zeichens oder einer Textzeichenfolge suchen möchten
- Das Zeichen oder die Textzeichenfolge, deren Position Sie finden müssen
Angenommen, Sie haben jetzt den folgenden Datensatz und möchten den gesamten Text nach dem letzten Komma entfernen und nur den Text vor dem letzten Komma haben.
Unten ist die Formel, die das tut:
=LINKS(A2,LetztePosition(A2,",")-1)
In der obigen Formel habe ich angegeben, um die Position des letzten Kommas zu finden. Falls Sie die Position eines anderen Zeichens oder einer anderen Textzeichenfolge ermitteln möchten, sollten Sie dies als zweites Argument in der Funktion verwenden.
Wie Sie sehen können, ist dies im Vergleich zur Langtextformel, die wir im vorherigen Abschnitt verwendet haben, viel kürzer und einfacher zu verwenden
Wenn Sie den VBA-Code in ein Modul einer Arbeitsmappe einfügen, können Sie diese benutzerdefinierte Funktion nur in dieser bestimmten Arbeitsmappe verwenden. Wenn Sie dies in allen Arbeitsmappen auf Ihrem System verwenden möchten, müssen Sie diesen Code kopieren und in die persönliche Makroarbeitsmappe einfügen.
Dies sind also einige der E-Mails, mit denen Sie den Text vor oder nach einem bestimmten Zeichen in Excel schnell entfernen können.
Wenn es sich um eine einfache einmalige Sache handelt, können Sie dies mit Suchen und Ersetzen tun. Was ist, wenn es etwas komplexer ist, dann müssen Sie eine Kombination aus integrierten Excel-Formeln verwenden oder sogar Ihre eigene benutzerdefinierte Formel mit VBA erstellen.
Ich hoffe, Sie fanden dieses Tutorial nützlich.
