Text extrahieren nach dem letzten Vorkommen eines bestimmten Zeichens
Wenn Sie eine Liste komplexer Textzeichenfolgen haben, die mehrere Trennzeichen enthalten (siehe folgender Screenshot als Beispiel – er zeigt Bindestriche, Kommas und Leerzeichen innerhalb einer Zelle) und nun die Position des letzten Vorkommens des Bindestrichs ermitteln sowie den darauf folgenden Teilstring extrahieren möchten, stellt dieser Artikel einige Formeln zur Lösung dieser Aufgabe vor.
- Formel 1: Extrahieren des Teilstrings nach dem letzten Vorkommen eines bestimmten Trennzeichens
- Formel 2: Extrahieren des Teilstrings nach dem letzten Vorkommen eines bestimmten Trennzeichens
Formel 1: Extrahieren des Teilstrings nach dem letzten Vorkommen eines bestimmten Trennzeichens
In Excel lässt sich die RECHTS-Funktion in Kombination mit LÄNGE, SUCHEN und WECHSELN nutzen, um eine passende Formel für diese Aufgabe zu erstellen.
1. Um den Teilstring nach dem letzten Vorkommen des Bindestrichs zu extrahieren, geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin:
2. Ziehen Sie anschließend den Ausfüllknauf auf die Zellen, auf die Sie diese Formel anwenden möchten, und erhalten Sie das Ergebnis wie im folgenden Screenshot gezeigt:
Erläuterung der Formel:
1. LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„")): Dieser Teil ermittelt, wie viele Bindestriche sich in Zelle A2 befinden.
- LÄNGE(A2): Diese LÄNGE-Funktion gibt die Gesamtanzahl der Zeichen in Zelle A2 zurück – das Ergebnis lautet: 44.
- WECHSELN(A2;"-";„"): Mit dieser WECHSELN-Funktion werden alle Bindestriche durch nichts ersetzt. Das Ergebnis lautet: „InsertDeleterows, sheets, images, formulas“.
- LÄNGE(WECHSELN(A2;"-";„")): Gibt die Gesamtlänge der Textzeichenfolge in Zelle A2 ohne Bindestriche zurück.
- LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„")): Subtrahiert die Länge der Textzeichenfolge ohne Bindestriche von der Gesamtlänge, um die Anzahl der Bindestriche zu ermitteln. Das Ergebnis lautet: 2.
2. WECHSELN(A2;"-";„#";LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„"))): Diese WECHSELN-Funktion ersetzt das letzte Vorkommen des Bindestrichs – das durch den ersten Teil der Formel ermittelt wurde – durch ein #-Zeichen. Das Ergebnis lautet: „Insert-Delete#rows, sheets, images, formulas“.
3. SUCHEN(„#";WECHSELN(A2;"-";„#";LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„"))))=SUCHEN("#"; „Insert-Delete#rows, sheets, images, formulas"): Diese SUCHEN-Funktion gibt die Position des #-Zeichens in der von der WECHSELN-Funktion zurückgegebenen Textzeichenfolge zurück – das Ergebnis lautet: 14.
4. LÄNGE(A2)-SUCHEN(„#";WECHSELN(A2;"-";„#";LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„")))): Dieser Teil der Formel ermittelt, wie viele Zeichen nach dem letzten Bindestrich stehen – das Ergebnis lautet: 30.
5. RECHTS(A2;LÄNGE(A2)-SUCHEN(„#";WECHSELN(A2;"-";„#";LÄNGE(A2)-LÄNGE(WECHSELN(A2;"-";„")))))=RECHTS(A2; 30): Schließlich extrahiert die RECHTS-Funktion die letzten 30 Zeichen – wie von der Formel aus Schritt 4 berechnet – von der rechten Seite der Textzeichenfolge in Zelle A2.
Hinweise:
1. Möchten Sie den Text nach dem letzten Vorkommen eines anderen Trennzeichens extrahieren, ersetzen Sie einfach das Bindestrichzeichen durch Ihr gewünschtes Trennzeichen.
2. Wenn das angegebene Trennzeichen in der Textzeichenfolge nicht enthalten ist, liefert die obige Formel einen Fehlerwert (siehe Screenshot):
Um diesen Fehler zu beheben, betten Sie die obige Formel einfach in die WENNFEHLER-Funktion ein – verwenden Sie dazu folgende Formel:
Formel 2: Extrahieren des Teilstrings nach dem letzten Vorkommen eines bestimmten Trennzeichens
Hier ist eine weitere einfache Formel, die mithilfe der Funktionen GLÄTTEN, RECHTS, WECHSELN, WIEDERHOLEN und LÄNGE diese Aufgabe in Excel ebenfalls meistert.
1. Kopieren Sie die folgende Formel in eine leere Zelle, in der Sie das Ergebnis erhalten möchten, oder geben Sie sie manuell ein:
2. Ziehen Sie anschließend den Ausfüllknauf auf die Zellen, auf die Sie diese Formel anwenden möchten. Alle Teilstrings nach dem letzten Bindestrich wurden wie im folgenden Screenshot gezeigt extrahiert:
Erläuterung der Formel:
1. LÄNGE(A2): Die LÄNGE-Funktion gibt die Gesamtanzahl der Zeichen in Zelle A2 zurück. Dieser Wert wird als Argument „Anzahl_Zeichen“ in der RECHTS-Funktion verwendet – das Ergebnis lautet: 44.
2. WECHSELN(A2;"-";WIEDERHOLEN(„ ";LÄNGE(A2))):
- WIEDERHOLEN(„ ";LÄNGE(A2)): Mit dieser WIEDERHOLEN-Funktion erzeugen Sie eine bestimmte Anzahl von Leerzeichen – basierend auf der Länge des Inhalts in Zelle A2.
- WECHSELN(A2;"-";WIEDERHOLEN(„ ";LÄNGE(A2))): Mit dieser WECHSELN-Funktion werden alle Bindestriche in Zelle A2 durch Leerzeichen ersetzt – und zwar so viele, wie die WIEDERHOLEN-Funktion aufgrund der Zeichenlänge von A2 liefert.
Dieser Formelteil dient als Argument „Text“ in der RECHTS-Funktion.
3. RECHTS(WECHSELN(A2;"-";WIEDERHOLEN(„ ";LÄNGE(A2)));LÄNGE(A2)): Diese RECHTS-Funktion extrahiert den Text von der rechten Seite der Zeichenfolge, die von der WECHSELN-Funktion zurückgegeben wird.
4. GLÄTTEN(): Die GLÄTTEN-Funktion entfernt alle überflüssigen Leerzeichen und lässt zwischen den Wörtern jeweils nur ein einzelnes Leerzeichen.
Verwendete verwandte Funktionen:
- RIGHT:
- Die RECHTS-Funktion extrahiert eine festgelegte Anzahl von Zeichen vom Ende der Textzeichenfolge.
- SEARCH:
- Die SUCHEN-Funktion hilft Ihnen, die Position eines bestimmten Zeichens oder Teilstrings im angegebenen Text zu ermitteln.
- LEN:
- Die LÄNGE-Funktion gibt die Anzahl der Zeichen in einer Textzeichenfolge zurück.
- SUBSTITUTE:
- Die WECHSELN-Funktion in Microsoft Excel ersetzt Text oder Zeichen innerhalb einer Textzeichenfolge durch anderen Text oder andere Zeichen.
- TRIM:
- Die GLÄTTEN-Funktion entfernt alle überflüssigen Leerzeichen aus der Textzeichenfolge und behält zwischen Wörtern jeweils nur ein einzelnes Leerzeichen bei.
- REPT:
- Die WIEDERHOLEN-Funktion dient dazu, Zeichen eine festgelegte Anzahl von Malen zu wiederholen.
Weitere Artikel:
- Mehrere Zeilen aus einer Zelle extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen haben, die durch Zeilenumbrüche getrennt sind (entstanden durch Drücken von Alt + Enter beim Eingeben des Textes), und diese Textzeilen wie im folgenden Screenshot gezeigt in mehrere Zellen extrahieren möchten – wie lösen Sie diese Aufgabe mit einer Excel-Formel?
- N-tes Wort aus einer Textzeichenfolge in Excel extrahieren
- Wenn Sie eine Liste von Textzeichenfolgen oder Sätzen haben und das spezifische n-te Wort daraus extrahieren möchten – wie im folgenden Screenshot gezeigt –, stellt dieser Artikel mehrere Methoden vor, um diese Aufgabe in Excel zu lösen.
- Text extrahieren zwischen Klammern aus einer Textzeichenfolge
- Wenn ein Teil des Textes innerhalb einer Textzeichenfolge von Klammern umgeben ist und Sie nun den gesamten Text zwischen diesen Klammern – wie im folgenden Screenshot gezeigt – extrahieren möchten, wie können Sie diese Aufgabe in Excel schnell und einfach erledigen?
- Teilstring aus einer Textzeichenfolge in Excel extrahieren
- Das Extrahieren von Teilstrings aus Textzeichenfolgen ist eine häufige Aufgabe. Excel bietet hierfür zwar keine direkte Funktion, doch mit den Funktionen LINKS, RECHTS, TEIL und SUCHEN können Sie genau die Teilstrings extrahieren, die Sie benötigen.
Die besten Office-Produktivitätswerkzeuge
Kutools für Excel – Hilft Ihnen, sich von der Masse abzuheben
Kutools für Excel bietet über 300 Funktionen und stellt sicher, dass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab – Aktiviert tabbasiertes Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- Mit einem Klick zwischen Dutzenden geöffneter Dokumente wechseln!
- Reduziert täglich Hunderte Mausklicks für Sie – verabschieden Sie sich von der Maus-Hand.
- Steigert Ihre Produktivität um 50 %, wenn Sie mehrere Dokumente gleichzeitig anzeigen und bearbeiten.
- Bringt effiziente Tabs in Office (inklusive Excel) – genau wie bei Chrome, Edge und Firefox.