Excel verfügt über eine Reihe von TEXT-Funktionen, die Wunder bewirken können. Mit diesen Funktionen können Sie alle Arten von Text-Slice- und Dice-Operationen durchführen.
Eine der häufigsten Aufgaben für Personen, die mit Textdaten arbeiten, besteht darin, eine Teilzeichenfolge in Excel zu extrahieren (d. h. psrt des Textes aus einer Zelle zu erhalten).
Leider gibt es in Excel keine Teilzeichenfolgenfunktion, die dies problemlos tun kann. Dies kann jedoch weiterhin mit Textformeln sowie einigen anderen integrierten Excel-Funktionen erfolgen.
Schauen wir uns zunächst einige der Textfunktionen an, die wir in diesem Tutorial verwenden werden.
Excel TEXT-Funktionen
Excel verfügt über eine Reihe von Textfunktionen, die es wirklich einfach machen, eine Teilzeichenfolge aus dem Originaltext in Excel zu extrahieren. Hier sind die Excel-Textfunktionen, die wir in diesem Tutorial verwenden werden:
- RIGHT-Funktion: Extrahiert die angegebene Anzahl von Zeichen rechts von der Textzeichenfolge.
- LEFT-Funktion: Extrahiert die angegebene Anzahl von Zeichen links von der Textzeichenfolge.
- MID-Funktion: Extrahiert die angegebene Anzahl von Zeichen von der angegebenen Startposition in einer Textzeichenfolge.
- FIND-Funktion: Findet die Anfangsposition des angegebenen Textes in der Textzeichenfolge.
- LEN-Funktion: Gibt die Anzahl der Zeichen in der Textzeichenfolge zurück.
Extrahieren einer Teilzeichenfolge in Excel mithilfe von Funktionen
Angenommen, Sie haben einen Datensatz wie unten gezeigt:
Dies sind einige zufällige (aber superheldenhafte) E-Mail-IDs (außer meiner), und in den folgenden Beispielen zeige ich Ihnen, wie Sie den Benutzernamen und den Domänennamen mit den Textfunktionen in Excel extrahieren.
Beispiel 1 – Extrahieren von Benutzernamen aus E-Mail-IDs
Bei der Verwendung von Textfunktionen ist es wichtig, ein Muster (falls vorhanden) zu erkennen. Das macht es wirklich einfach, eine Formel zu konstruieren. Im obigen Fall ist das Muster das @-Zeichen zwischen dem Benutzernamen und dem Domänennamen, und wir verwenden es als Referenz, um die Benutzernamen zu erhalten.
Hier ist die Formel, um den Benutzernamen zu erhalten:
=LINKS(A2,SUCHEN("@",A2)-1)
Die obige Formel verwendet die LEFT-Funktion, um den Benutzernamen zu extrahieren, indem die Position des @-Zeichens in der ID identifiziert wird. Dies geschieht mit der Funktion FIND, die die Position des @ zurückgibt.
Im Fall von [email protected] würde FIND(“@”,A2) beispielsweise 11 zurückgeben, was seine Position in der Textzeichenfolge ist.
Jetzt verwenden wir die LEFT-Funktion, um 10 Zeichen links vom String zu extrahieren (eins weniger als der von der LEFT-Funktion zurückgegebene Wert).
Beispiel 2 – Extrahieren des Domänennamens aus E-Mail-IDs
Die gleiche Logik wie im obigen Beispiel kann verwendet werden, um den Domänennamen zu erhalten. Ein kleiner Unterschied besteht darin, dass wir die Zeichen rechts von der Textzeichenfolge extrahieren müssen.
Hier ist die Formel, die dies tut:
=RECHTS(A2,LEN(A2)-FINDEN("@",A2))
In der obigen Formel verwenden wir dieselbe Logik, passen sie jedoch an, um sicherzustellen, dass wir die richtige Zeichenfolge erhalten.
Nehmen wir noch einmal das Beispiel [email protected]. Die Funktion FIND gibt die Position des @-Zeichens zurück, die in diesem Fall 11 ist. Jetzt müssen wir alle Zeichen nach dem @ extrahieren. Also ermitteln wir die Gesamtlänge des Strings und ziehen die Anzahl der Zeichen bis zum @ ab. Es gibt uns die Anzahl der Zeichen, die den Domainnamen auf der rechten Seite abdecken.
Jetzt können wir einfach die RECHTE Funktion verwenden, um den Domänennamen zu erhalten.
Beispiel 3 – Extrahieren des Domänennamens aus E-Mail-IDs (ohne .com)
Um eine Teilzeichenfolge aus der Mitte einer Textzeichenfolge zu extrahieren, müssen Sie die Position der Markierung direkt vor und nach der Teilzeichenfolge identifizieren.
Um beispielsweise im Beispiel unten den Domänennamen ohne den .com-Teil abzurufen, wäre die Markierung @ (der direkt vor dem Domänennamen steht) und . (was gleich danach ist).
Hier ist die Formel, die nur den Domänennamen extrahiert:
=MITTEL(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1)
Die Excel-MID-Funktion extrahiert die angegebene Anzahl von Zeichen aus der angegebenen Startposition. In diesem Beispiel oben gibt FIND(“@”,A2)+1 die Startposition an (die direkt nach dem @ ist) und FIND(“.”,A2)-FIND(“@”,A2)-1 identifiziert die Anzahl der Zeichen zwischen den '@' und das '.‘
Aktualisieren: Einer der Leser William19 erwähnte, dass die obige Formel nicht funktionieren würde, wenn die E-Mail-ID einen Punkt (.) enthält (zum Beispiel [email protected]). Hier ist also die Formel, um mit solchen Fällen umzugehen:
=MITTEL(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)
Verwenden von Text in Spalten zum Extrahieren einer Teilzeichenfolge in Excel
Die Verwendung von Funktionen zum Extrahieren einer Teilzeichenfolge in Excel hat den Vorteil, dass sie dynamisch ist. Wenn Sie den Originaltext ändern, aktualisiert die Formel die Ergebnisse automatisch.
Wenn Sie dies möglicherweise nicht benötigen, können Sie den Text mithilfe der Funktion „Text in Spalten“ schnell und einfach in Teilzeichenfolgen basierend auf angegebenen Markierungen aufteilen.
So geht's:
- Wählen Sie die Zellen aus, in denen sich der Text befindet.
- Gehen Sie zu Daten -> Datentools -> Text in Spalten.
- Wählen Sie im Schritt 1 des Text-in-Spalten-Assistenten die Option Getrennt aus, und klicken Sie auf Weiter.
- Aktivieren Sie in Schritt 2 die Option Andere und geben Sie @ in das Feld rechts daneben ein. Dies ist unser Trennzeichen, das Excel verwenden würde, um den Text in Teilzeichenfolgen aufzuteilen. Sie können die Datenvorschau unten sehen. Klicken Sie auf Weiter.
- In Schritt 3 funktioniert die allgemeine Einstellung in diesem Fall einwandfrei. Sie können jedoch ein anderes Format wählen, wenn Sie Zahlen/Daten aufteilen. In der Zielzelle befinden sich standardmäßig die Originaldaten. Wenn Sie die Originaldaten intakt halten möchten, ändern Sie dies in eine andere Zelle.
- Klicken Sie auf Fertig stellen.
Dadurch erhalten Sie sofort zwei Sätze von Teilzeichenfolgen für jede in diesem Beispiel verwendete E-Mail-ID.
Wenn Sie den Text weiter aufteilen möchten (z. B. batman.com in batman und com aufteilen), wiederholen Sie den gleichen Vorgang damit.
Verwenden von FIND und REPLACE zum Extrahieren von Text aus einer Zelle in Excel
SUCHEN und ERSETZEN kann eine leistungsstarke Technik sein, wenn Sie mit Text in Excel arbeiten. In den folgenden Beispielen erfahren Sie, wie Sie FINDEN und ERSETZEN mit Platzhalterzeichen verwenden, um in Excel erstaunliche Dinge zu tun.
Siehe auch: Erfahren Sie alles über Platzhalterzeichen in Excel.
Nehmen wir die gleichen Beispiele für E-Mail-IDs.
Beispiel 1 – Extrahieren von Benutzernamen aus E-Mail-IDs
Hier sind die Schritte zum Extrahieren von Benutzernamen aus E-Mail-IDs mithilfe der Funktion Suchen und Ersetzen:
- Kopieren Sie die Originaldaten und fügen Sie sie ein. Da Suchen und Ersetzen funktioniert und die Daten, auf die es angewendet wird, ändert, ist es am besten, eine Sicherungskopie der Originaldaten zu erstellen.
- Wählen Sie die Daten aus und gehen Sie zu Home -> Bearbeiten -> Suchen & Auswählen -> Ersetzen (oder verwenden Sie die Tastenkombination Strg + H).
- Geben Sie im Dialogfeld Suchen und Ersetzen Folgendes ein:
- Finde was: @*
- Ersetzen durch: (lassen Sie dieses Feld leer)
- Klicken Sie auf Alle ersetzen.
Dadurch wird sofort der gesamte Text vor dem @ in den E-Mail-IDs entfernt. Sie erhalten das Ergebnis wie unten gezeigt:
Wie funktioniert das?? - Im obigen Beispiel haben wir eine Kombination aus @ und * verwendet. Ein Sternchen (*) ist ein Platzhalterzeichen, das eine beliebige Anzahl von Zeichen darstellt. @* würde also eine Textzeichenfolge bedeuten, die mit @ beginnt und beliebig viele Zeichen dahinter haben kann. In [email protected] wäre @* beispielsweise @batman.com. Wenn wir @* durch ein Leerzeichen ersetzen, werden alle Zeichen nach @ (einschließlich @) entfernt.
Beispiel 2 – Extrahieren des Domänennamens aus E-Mail-IDs
Mit derselben Logik können Sie die Kriterien „Suchen nach“ ändern, um den Domänennamen zu erhalten.
Hier sind die Schritte:
- Wählen Sie die Daten aus.
- Gehen Sie zu Home -> Bearbeiten -> Suchen & Auswählen -> Ersetzen (oder verwenden Sie die Tastenkombination Strg + H).
- Geben Sie im Dialogfeld Suchen und Ersetzen Folgendes ein:
- Finde was: *@
- Ersetzen durch: (lassen Sie dieses Feld leer)
- Klicken Sie auf Alle ersetzen.
Dadurch wird sofort der gesamte Text vor dem @ in den E-Mail-IDs entfernt. Sie erhalten das Ergebnis wie unten gezeigt:
