Excel verfügt über eine Reihe von Textfunktionen, die Wunder bewirken können. Mit diesen Funktionen können Sie alle Arten von Textscheiben-und Würfeloperationen ausführen.

Eine der häufigsten Aufgaben für Personen, die mit Textdaten arbeiten, besteht darin, eine Teilzeichenfolge in Excel zu extrahieren (dh psrt des Textes aus einer Zelle abzurufen).

Leider gibt es in Excel keine Teilzeichenfolgefunktion, 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-Textfunktionen

Excel verfügt über eine Reihe von Textfunktionen, die es wirklich einfach machen würden, einen Teilstring aus dem ursprünglichen Text in Excel zu extrahieren. Hier sind die Excel-Textfunktionen, die wir in diesem Tutorial verwenden werden:

• RECHTE Funktion: Extrahiert die angegebene Anzahl von Zeichen rechts von der Textzeichenfolge.
• LINKE Funktion: Extrahiert die angegebene Anzahl von Zeichen aus der linken Seite der Textzeichenfolge.,
• MID-Funktion: Extrahiert die angegebene Anzahl von Zeichen aus der angegebenen Startposition in einer Textzeichenfolge.
• Funktion SUCHEN: Findet die Startposition des angegebenen Textes in der Textzeichenfolge.
• LEN-Funktion: Gibt die Anzahl der Zeichen in der Textzeichenfolge zurück.,

Extrahieren Sie einen Teilstring in Excel mithilfe der Funktionen

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Dies sind einige zufällige (aber Superhelden -) E-Mail-IDs (außer meinen), und in den folgenden Beispielen zeige ich Ihnen, wie Sie den Benutzernamen und den Domainnamen mithilfe der Textfunktionen in Excel extrahieren.

Beispiel 1-Extrahieren von Benutzernamen aus E-Mail-IDs

Bei Verwendung von Textfunktionen ist es wichtig, ein Muster (falls vorhanden) zu identifizieren. Das macht es wirklich einfach, eine Formel zu konstruieren., Im obigen Fall ist das Muster das @ – Zeichen zwischen dem Benutzernamen und dem Domainnamen, und wir verwenden es als Referenz, um die Benutzernamen abzurufen.

Hier ist die Formel zum Abrufen des Benutzernamens:

=LEFT(A2,FIND("@",A2)-1)

Die obige Formel verwendet die LINKE 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.

Zum Beispiel bei [email protected], FIND ( “ @ “ , A2) würde 11 zurückgeben, was seine Position in der Textzeichenfolge ist.,

Jetzt verwenden wir die LINKE Funktion, um 10 Zeichen links von der Zeichenfolge zu extrahieren (eines weniger als der von der LINKEN Funktion zurückgegebene Wert).

Beispiel 2-Extrahieren des Domainnamens aus E-Mail-IDs

Dieselbe Logik, die im obigen Beispiel verwendet wird, kann zum Abrufen des Domainnamens verwendet werden. Ein kleiner Unterschied besteht darin, dass wir die Zeichen rechts von der Textzeichenfolge extrahieren müssen.,

Hier ist die Formel, die dies tun wird:

=RIGHT(A2,LEN(A2)-FIND("@",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 von [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. So identifizieren wir die Gesamtlänge der Zeichenfolge und subtrahieren die Anzahl der Zeichen bis zum @. Es gibt uns die Anzahl der Zeichen, die den Domainnamen auf der rechten Seite abdecken.,

Jetzt können wir einfach die RICHTIGE Funktion verwenden, um den Domainnamen zu erhalten.

Beispiel 3-Extrahieren des Domainnamens aus E-Mail-IDs (ohne .com)

Um einen Teilstring aus der Mitte einer Textzeichenfolge zu extrahieren, müssen Sie die Position des Markers direkt vor und nach dem Teilstring identifizieren.

Um beispielsweise im folgenden Beispiel den Domänennamen ohne den .com-Teil abzurufen, lautet die Markierung @ (direkt vor dem Domänennamen) und . (was gleich danach ist).,

Hier ist die Formel, die nur den Domainnamen extrahiert:

=MID(A2,FIND("@",A2)+1,FIND(".",A2)-FIND("@",A2)-1) 

Excel MID extrahiert die angegebene Anzahl von Zeichen aus der angegebenen Startposition. In diesem Beispiel oben, FIND ( “ @“, A2)+1 gibt die Startposition (die direkt nach dem@), und FIND („.“, A2) – FIND(„@“,A2) -1 identifiziert die Anzahl der Zeichen zwischen ‚@‘ und ‚.‘

Update: Einer der Leser William19 erwähnte, dass die obige Formel nicht funktionieren würde, falls es einen Punkt gibt(.) in der E-Mail-ID (z. B. bruce.wayne@batman.,com). Hier ist also die Formel, um solche Fälle zu behandeln:

=MID(A1,FIND("@",A1)+1,FIND(".",A1,FIND("@",A1))-FIND("@",A1)-1)

Verwenden von Text in Spalten zum Extrahieren eines Teilstrings in Excel

Verwenden von Funktionen zum Extrahieren eines Teilstrings in Excel hat den Vorteil, dynamisch zu sein. Wenn Sie den Originaltext ändern, aktualisiert die Formel automatisch die Ergebnisse.

Wenn Sie dies möglicherweise nicht benötigen, kann die Verwendung der Funktion Text zu Spalten eine schnelle und einfache Möglichkeit sein, den Text basierend auf bestimmten Markierungen in Teilzeichenfolgen aufzuteilen.

So geht das:

• Wählen Sie die Zellen aus, in denen Sie den Text haben.,
• Gehen Sie zu Daten –> Datentools –> Text in Spalten.
• Wählen Sie im Assistenten für Text in Spalte Schritt 1 Trennzeichen aus und drücken Sie Weiter.
• Aktivieren Sie in Schritt 2 die andere Option und geben Sie @ in das Feld direkt daneben ein. Dies ist unser Trennzeichen, mit dem Excel den Text in Teilzeichenfolgen aufteilt. 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. Standardmäßig befindet sich die Zielzelle dort, wo Sie die Originaldaten haben. Wenn Sie die Originaldaten intakt halten möchten, ändern Sie diese 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 teilen möchten (z. B. teilen batman.com um Batman und com), wiederholen Sie den gleichen Vorgang mit ihm.,

Verwenden von SUCHEN und ERSETZEN 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 FIND und REPLACE mit Platzhalterzeichen verwenden, um erstaunliche Dinge in Excel zu tun.

See Also: Learn All about Wildcard Characters in Excel.

Nehmen wir die gleichen E-Mail-IDs Beispiele.

Beispiel 1-Extrahieren von Benutzernamen aus E-Mail-IDs

Hier sind die Schritte zum Extrahieren von Benutzernamen aus E-Mail-IDs mithilfe der Such-und Ersetzungsfunktion:

• Kopieren und Einfügen der Originaldaten., Da Find and Replace funktioniert und die Daten ändert, auf die es angewendet wird, ist es am besten, eine Sicherung der Originaldaten zu haben.
• Wählen Sie die Daten aus und gehen Sie nach Hause –> Bearbeiten –> Suchen Sie & Auswählen –> Ersetzen (oder verwenden Sie die Tastenkombination Strg + H).,
• Geben Sie im Dialogfeld Suchen und Ersetzen Folgendes ein:
  • Finden Sie 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:

Beispiel 2 – Extrahieren des Domain-Namens aus E-Mail-IDs

Mit derselben Logik können Sie die Kriterien „Suchen nach Was“ ändern, um den Domain-Namen zu erhalten.,

Hier sind die Schritte:

Dadurch wird sofort der gesamte Text vor dem @ in den E-Mail-IDs entfernt. Sie erhalten das Ergebnis wie unten gezeigt:

Möglicherweise mögen Sie auch die folgenden Excel-Tutorials:

• So zählen Sie Zellen, die Textzeichenfolgen enthalten.
• Extrahieren Sie Benutzernamen aus E-Mail-IDs in Excel .
• Excel-Funktionen (Beispiele + Videos).
• Holen Sie mehr aus Finden und Ersetzen in Excel.
• Großschreibung des ersten Buchstabens einer Textzeichenfolge in Excel
• So extrahieren Sie das erste Wort aus einer Textzeichenfolge in Excel