Excel-Tutorial – Text oder Zahl anhand einer bestimmten Position extrahieren

In vielen Fällen möchten Sie möglicherweise nur den nützlichen Inhalt aus einem Satz oder einer Textzeichenfolge in einer Zelle extrahieren, wie zum Beispiel das Bundesland aus einer Adresse, die E-Mail-Adresse aus einem Satz, die Sendungsnummer aus einer Unterhaltung und so weiter. Dieses Tutorial beschränkt die Extraktion auf eine bestimmte Position in einer Zelle und stellt verschiedene Methoden zusammen, um dabei zu helfen, Text oder Zahlen anhand einer bestimmten Position in einer Zelle in Excel zu extrahieren.

Text nach Position extrahieren

Dieser Abschnitt fasst häufige Positionen zusammen, aus denen Text aus einer Zelle extrahiert werden kann, und bietet entsprechende Methoden, um sie schrittweise zu behandeln. Sie können weitere Details durchsuchen.

1. Extrahieren Sie die Anzahl der Zeichen von links oder rechts

Um die Anzahl der Zeichen von der linken oder rechten Seite einer Zeichenfolge zu extrahieren, können Sie eine der folgenden Methoden ausprobieren.

1.1 Extrahieren der ersten oder letzten N Zeichen mit Formeln

Angenommen, Sie haben eine Liste von Textstrings in Spalte B, wie im Screenshot unten gezeigt. Um die ersten 2 Zeichen und die letzten 2 Zeichen von jedem String zu extrahieren, können Sie die folgenden Formeln anwenden.

Die ersten N Zeichen aus einer Textzeichenfolge extrahieren

Die LINKS-Funktion kann dabei helfen, ganz einfach die ersten N Zeichen aus einer Textzeichenfolge in Excel zu extrahieren.

Allgemeine Formel

=LINKS(Textzeichenfolge,[Anzahl_Zeichen])

Argumente

Jetzt können Sie diese Formel anwenden, um die ersten 2 Zeichen aus den Zellen in Spalte B zu extrahieren.

1. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das erste Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie den Ausfüllkästchen-Handler nach unten, um die Formel auf andere Zellen anzuwenden.

=LEFT(B5,2)

Sie haben nun die ersten 2 Zeichen in jeder Zelle des angegebenen Bereichs extrahiert.

Die letzten N Zeichen aus einer Textzeichenfolge extrahieren

Hier wenden wir die RECHTS-Funktion an, um die letzten N Zeichen aus einer Textzeichenfolge in Excel zu extrahieren.

Allgemeine Formel

=RECHTS(Textzeichenfolge;[Anzahl_Zeichen])

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie dann diese Ergebniszelle aus und ziehen Sie den AutoAusfüllGriff nach unten, um weitere Ergebnisse zu erhalten.

=RIGHT(B5,2)

1.2 Extrahieren Sie die ersten oder letzten N Zeichen mit einem erstaunlichen Tool

Obwohl die oben genannten Formeln einfach sind, müssen Sie zum Extrahieren der ersten oder letzten n Zeichen aus einer langen Liste von Textzeichenfolgen dennoch den AutoFüllgriff von oben nach unten ziehen, was etwas zeitaufwendig sein kann. Hier empfehlen wir das Hilfsprogramm „Text extrahieren“ von „Kutools für Excel“, um die ersten oder letzten N Zeichen aus einer Liste von Textzeichenfolgen in großen Mengen zu extrahieren.

1. Wählen Sie die Textzeichenfolgenliste im Voraus aus, aus der Sie Text extrahieren möchten, und klicken Sie auf „Kutools“ > „Text“ > „Text extrahieren“.

2. Im aufgehenden Dialogfeld „Text extrahieren“ müssen Sie wie folgt konfigurieren:

Hinweis: Um das Ergebnis dynamisch zu gestalten, wenn sich die Textzeichenfolge ändert, können Sie das Kontrollkästchen „Als Formel einfügen“ aktivieren.

3. Wählen Sie im nächsten sich öffnenden Dialogfeld „Text extrahieren“ eine Zelle aus, um die extrahierten Zeichen auszugeben, und klicken Sie dann auf „OK“.

Dann werden die ersten N oder letzten N Zeichen aus den ausgewählten Zellen in großem Umfang extrahiert.

Klicken Sie hier, um mehr über diese Funktion zu erfahren.

2. Text vor oder nach einem bestimmten Zeichen/Wort extrahieren

Um Text vor oder nach einem bestimmten Zeichen oder Wort zu extrahieren, werden die verschiedenen Szenarien in diesem Abschnitt Ihren Anforderungen gerecht.

2.1 Text vor oder nach dem ersten Trennzeichen (einem Zeichen) extrahieren

Wie im folgenden Screenshot gezeigt, können Sie einen der unten aufgeführten Methoden anwenden, um Text vor oder nach dem ersten Trennzeichen aus jeder Zelle im Bereich B4:B10 zu extrahieren.

2.1.1 Text vor dem ersten Trennzeichen mit Formel extrahieren

Die Anwendung einer Formel basierend auf den Funktionen LINKS und FINDEN kann helfen, Text vor dem ersten Trennzeichen aus einer Zelle zu extrahieren. Sie können den folgenden Schritten folgen, um dies zu erledigen.

Allgemeine Formel

=LINKS(Textzeichenfolge;FINDEN("Trennzeichen";Textzeichenfolge;1)-1)

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein oder geben Sie sie ein und drücken Sie die Eingabetaste, um das erste Ergebnis zu erhalten. Wählen Sie die Zelle mit dem ersten Ergebnis aus und ziehen Sie den AutoAusfüllgriff nach unten, um die Texte vor dem ersten Trennzeichen anderer Zellen zu erhalten.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Text nach dem ersten Trennzeichen mit Formel extrahieren

Die folgende Formel hilft dabei, Text nach dem ersten Trennzeichen aus einer Zelle in Excel zu extrahieren.

Allgemeine Formel

=TEIL(text_string;FINDEN("Trennzeichen";text_string)+1;LÄNGE(text_string))

Argumente

=MID(B5,FIND("-",B5)+1,LEN(B5))

2.1.3 Text vor oder nach dem ersten Trennzeichen mit einem erstaunlichen Tool extrahieren

Hier empfehlen wir die Funktion "Text extrahieren" von "Kutools für Excel" sehr. Mit dieser Funktion können Sie ganz einfach Texte vor oder nach dem ersten Trennzeichen aus einem Bereich von Zellen in großen Mengen extrahieren.

1. Wählen Sie den Zellbereich aus, aus dem Sie den Text extrahieren möchten, und klicken Sie dann auf „Kutools“ > „Text“ > „Text extrahieren“.

2. Im Dialogfeld „Text extrahieren“ müssen Sie wie folgt konfigurieren:

Hinweis: Um das Ergebnis dynamisch zu gestalten, wenn sich die Textzeichenfolge ändert, können Sie das Kontrollkästchen Als Formel einfügen aktivieren.

3. Anschließend öffnet sich ein weiteres Dialogfeld „Text extrahieren“. Wählen Sie eine Zelle aus, um die Ergebnisse auszugeben, und klicken Sie auf „OK“.

Dann werden die Texte vor oder nach dem ersten Trennzeichen sofort aus den ausgewählten Zellen extrahiert.

Um mehr über diese Funktion zu erfahren, besuchen Sie bitte: Schnelles Extrahieren bestimmter Texte aus Zellen in Excel.

2.2 Text vor oder nach dem letzten Trennzeichen (einem Zeichen) extrahieren

In den obigen Schritten haben wir die Methoden zum Extrahieren von Text vor oder nach dem ersten Trennzeichen aus einer Zelle kennengelernt. Wie im Screenshot unten gezeigt, wird dieser Abschnitt Ihnen zwei Formeln zeigen, um Text vor oder nach dem letzten Trennzeichen aus einer Zelle zu extrahieren. Sie können den folgenden Schritten folgen, um dies zu erledigen.

2.2.1 Text vor dem letzten Trennzeichen mit Formel extrahieren

Um Text vor dem letzten Trennzeichen aus einer Zelle zu extrahieren, können Sie die Funktionen SUCHEN, LÄNGE und ERSETZEN innerhalb der LINKS-Funktion verwenden.

Allgemeine Formel

=LINKS(Textzeichenfolge;SUCHEN("#";WECHSELN(Textzeichenfolge;"Trennzeichen";"#";LÄNGE(Textzeichenfolge)-LÄNGE(WECHSELN(Textzeichenfolge;"Trennzeichen";""))))-1)

Argumente

Wählen Sie eine Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den AutoAusfüllgriff nach unten, um die Texte aus anderen Textzeichenfolgen in derselben Spalte zu extrahieren.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Text nach dem letzten Trennzeichen mit Formel extrahieren

Nachdem Sie den Text vor dem letzten Trennzeichen aus einer Zelle extrahiert haben, können Sie die folgende Formel anwenden, um den Text nach dem letzten Trennzeichen zu extrahieren, wie Sie es benötigen.

Allgemeine Formel

=RECHTS(text_string;LÄNGE(text_string)-SUCHEN("#";WECHSELN(text_string;"Trennzeichen";"#";LÄNGE(text_string)-LÄNGE(WECHSELN(text_string;"Trennzeichen";"")))))

Argumente

Wählen Sie eine Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den AutoAusfüllgriff nach unten, um die Texte aus anderen Textzeichenfolgen in der gleichen Spalte zu extrahieren.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 Text nach dem n-ten Zeichen extrahieren

Sehen Sie sich das folgende Beispiel an: Es gibt eine Liste von Textzeichenfolgen im Bereich B4:B10. Um den Text nach dem dritten Zeichen aus jeder Zelle zu extrahieren, können Sie eine Formel basierend auf der MID-Funktion und der LEN-Funktion anwenden.

Allgemeine Formel

=TEIL(text_string,nth_char+1,LÄNGE(text_string))

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den Ausfüllkursor nach unten, um weitere Ergebnisse zu erhalten.

=MID(B5,3+1,LEN(B5))

2.4 Das n-te Wort aus einer Textzeichenfolge extrahieren

Angenommen, Sie haben eine Liste von Textzeichenfolgen, wie im Screenshot unten gezeigt, und möchten nur das n-te Wort aus der Textzeichenfolge extrahieren. Dieser Abschnitt bietet Ihnen drei Methoden, um dies zu erreichen.

2.4.1 Das n-te Wort mit einer Formel extrahieren

Sie können die Funktionen GLÄTTEN, TEIL, ERSETZEN, WIEDERHOLEN und LÄNGE kombinieren, um das n-te Wort aus einer Textzeichenfolge in einer Zelle zu extrahieren.

Allgemeine Formel

=GLÄTTEN(TEIL(WECHSELN(text_string," ",WIEDERHOLEN(" ",LÄNGE((text_string))), (N-1)*LÄNGE((text_string)+1, LÄNGE((text_string)))

Argumente

In diesem Fall enthält der Bereich B5:B10 die Textzeichenfolgen, D5:D10 enthält die Zahlen, die das n-te Wort darstellen. Wenden wir diese Formel an, um das n-te Wort aus der Textzeichenfolge zu extrahieren.

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das erste Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den AutoAusfüllgriff nach unten, um das n-te Wort anderer Zellen zu erhalten.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Hinweis: Sie können die n-te Zahl direkt in die Formel wie folgt eingeben.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Extrahieren des n-ten Wortes mit einer benutzerdefinierten Funktion

Neben der oben genannten Formel können Sie auch eine benutzerdefinierte Funktion anwenden, um das n-te Wort aus einer Zelle in Excel zu extrahieren.

1. Drücken Sie die Tasten „Alt“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie im Fenster „Microsoft Visual Basic for Applications“ auf „Einfügen“ > „Modul“ und kopieren Sie dann das folgende VBA in das Codefenster.

VBA-Code: Das n-te Wort aus einer Textzeichenfolge in einer Zelle extrahieren

Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = ""
Else
    FindWord = arr(Position - 1)
End If
End Function

3. Drücken Sie die Tasten „Alt“ + „Q“, um das Fenster „Microsoft Visual Basic for Applications“ zu schließen.

4. Gehen Sie zurück zum Arbeitsblatt, das die Textzeichenfolgen enthält, aus denen Sie das n-te Wort extrahieren möchten. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das n-te Wort zu erhalten.

=FindWord(B5,D5)

Oder

=FindWord(B5,2)

Hinweis: In der Formel ist D5 die Zelle, die eine Zahl enthält, die das n-te Wort darstellt. Alternativ können Sie den Zellbezug auch direkt durch eine Zahl ersetzen.

5. Wählen Sie die Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um das n-te Wort aus den Textzeichenfolgen anderer Zellen zu extrahieren.

2.4.3 Extrahieren Sie das n-te Wort mit einem erstaunlichen Tool

Wenn Sie keine Formel oder benutzerdefinierte Funktion manuell anwenden möchten, empfehlen wir hier die Funktion "Das N-te Wort aus einer Zelle extrahieren" von Kutools für Excel. Mit dieser Funktion können Sie ganz einfach das n-te Wort aus einer Textzeichenfolge in einer Zelle mit nur wenigen Klicks extrahieren.

1. Wählen Sie eine Zelle aus, um das Ergebnis einzufügen, und klicken Sie auf „Kutools“ > „Formelhelfer“ > „Text“ > „Extrahieren“ > „Das n-te Wort in einer Zelle“. Siehe Screenshot:

2. Im Dialogfeld „Formelhelfer“ müssen Sie wie folgt konfigurieren:

3. Dann wird das n-te (zweite) Wort aus der Textzeichenfolge in Zelle B5 extrahiert, und Sie können sehen, dass gleichzeitig eine Formel erstellt wird. Wählen Sie diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um das n-te Wort aus anderen Textzeichenfolgen zu erhalten.

Klicken Sie hier, um mehr über diese Funktion zu erfahren.

2.5 Text vor oder nach dem n-ten Vorkommen eines Trennzeichens extrahieren

Angenommen, Sie haben eine Liste von Textzeichenfolgen, wie im Screenshot unten dargestellt. Um den Text vor oder nach dem zweiten Auftreten eines Leerzeichens zu extrahieren, bietet dieser Abschnitt zwei Formeln, um Ihnen dabei zu helfen.

2.5.1 Text vor dem n-ten Vorkommen eines Trennzeichens extrahieren

Sie können die Funktion LINKS zusammen mit den Funktionen ERSETZEN und FINDEN verwenden, um Text vor dem n-ten Vorkommen eines Trennzeichens aus einer Zelle in Excel zu extrahieren.

Allgemeine Formel

=LINKS(WECHSELN(text_string,"Trennzeichen",ZEICHEN(9),n),FINDEN(ZEICHEN(9),WECHSELN(text_string,"Trennzeichen",ZEICHEN(9),n),1)-1)

Argumente

Wählen Sie eine Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den AutoAusfüllgriff nach unten, um weitere Ergebnisse in der Liste zu erhalten.

=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)

Hinweis: In der Formel ist B5 die Zelle, die die Textzeichenfolge enthält, aus der Sie den Text extrahieren möchten; „ “ steht hier für ein Leerzeichen und die Zahl 2 steht für das zweite Auftreten eines Leerzeichens. Sie können diese Werte je nach Bedarf ändern.

2.5.2 Text nach dem n-ten Vorkommen eines Trennzeichens extrahieren

Um Text nach dem n-ten Vorkommen eines Trennzeichens zu extrahieren, können Sie die RECHTS-Funktion zusammen mit den Funktionen ERSETZEN, LÄNGE und FINDEN anwenden.

Allgemeine Formel

=RECHTS(WECHSELN(text_string, "Trennzeichen", ZEICHEN(9), n), LÄNGE(text_string)- FINDEN(ZEICHEN(9), WECHSELN(text_string, "Trennzeichen", ZEICHEN(9), n), 1) + 1)

Argumente

Sie können diese Formel nun anwenden, um den Text nach dem zweiten Auftreten des Leerzeichens aus jeder Zelle im Bereich B5:B10 wie folgt zu extrahieren.

Wählen Sie eine Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie den AutoAusfüllgriff nach unten, um weitere Ergebnisse zu erhalten.

=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)

2.6 Text vor oder nach einem Zeilenumbruch extrahieren

Angenommen, Sie haben eine Bestellliste in Spalte B und möchten nur den Datums- und den Produktnummer-Teil aus jeder Zelle extrahieren. Sie können die folgenden Excel-Formeln verwenden, um dies zu erledigen.

2.6.1 Text vor dem ersten Zeilenumbruch mit Formel extrahieren

Wie Sie im obigen Screenshot sehen können, befindet sich der Datumsabschnitt vor dem ersten Zeilenumbruch innerhalb der Zelle. In diesem Abschnitt wird eine LINKS-Funktion zusammen mit der SUCHEN-Funktion vorgestellt, um Ihnen zu helfen, den Text vor dem ersten Zeilenumbruch innerhalb einer Zelle zu extrahieren.

Allgemeine Formel

=LINKS(Zelle; SUCHEN(ZEICHEN(10); Zelle)-1)

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel hinein oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoAusfüllgriff nach unten, um diese Formel auf andere Zellen anzuwenden.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Dann können Sie sehen, dass der Text vor dem ersten Zeilenumbruch in jeder Zelle im Bereich B5:B8 extrahiert wird, wie im Screenshot unten dargestellt.

Hinweis: In der Formel steht CHAR(10) für einen Zeilenumbruch unter Windows.

2.6.2 Text nach dem letzten Zeilenumbruch mit Formel extrahieren

Im vorherigen Schritt haben wir darüber gesprochen, wie man Text vor dem ersten Zeilenumbruch innerhalb einer Zelle extrahiert. Dieser Teil zeigt Ihnen, wie Sie Text nach dem letzten Zeilenumbruch innerhalb einer Zelle mit einer anderen Formel extrahieren.

Allgemeine Formel

=GLÄTTEN(RECHTS(WECHSELN(Zelle;ZEICHEN(10);WIEDERHOLEN(" ";200));200))

Argumente

Wählen Sie eine leere Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann deren AutoFill-Handle nach unten, um die Formel auf andere Zellen anzuwenden.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Dann wird der Produktnummer-Teil jeder Zelle in der Liste wie im obigen Screenshot gezeigt extrahiert.

Hinweis: In der Formel steht CHAR(10) für einen Zeilenumbruch unter Windows.

  2.7 Text vor oder nach einem Wort extrahieren

In den vorherigen Abschnitten haben wir gelernt, wie man Text vor oder nach einem Zeichen oder Trennzeichen extrahiert. Was sollten Sie tun, um Text vor oder nach einem ganzen Wort zu extrahieren? Dieser Abschnitt stellt drei Methoden vor, die Ihnen helfen, diese Aufgabe zu erledigen.

2.7.1 Text vor einem bestimmten Wort mit Formel extrahieren

Die folgende Formel hilft Ihnen, Text vor einem bestimmten Wort innerhalb einer Zelle in Excel zu extrahieren.

Allgemeine Formel

=WENNFEHLER(LINKS(Zelle;FINDEN(Wort;Zelle)-1);Zelle)

Argumente

Wählen Sie eine leere Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann deren AutoFill-Handle nach unten, um diese Formel auf andere Zellen anzuwenden.

In diesem Beispiel werden wir alle Texte vor dem Wort „Excel“ extrahieren, daher geben wir das Wort direkt in die Formel ein und setzen es in doppelte Anführungszeichen. Alternativ können Sie auch auf eine Zelle verweisen, die das Wort „Excel“ enthält.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Hinweise:

2.7.2 Text nach einem bestimmten Wort mit Formel extrahieren

Um Text nach einem bestimmten Wort zu extrahieren, können Sie die folgende Formel anwenden, um dies zu erledigen.

Allgemeine Formel

=GLÄTTEN(TEIL(Zelle;SUCHEN(Wort;Zelle)+LÄNGE(Wort);255))

Argumente

Wählen Sie eine Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie dann ihren AutoFill-Handle nach unten, um diese Formel auf andere Zellen anzuwenden.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Dann können Sie sehen, dass alle Texte nach dem Wort „Excel“ in jeder Zelle extrahiert wurden, wie im Screenshot unten gezeigt.

Hinweise:

2.7.3 Text vor oder nach einem bestimmten Wort mit einem erstaunlichen Tool extrahieren

Wenn Sie das Gefühl haben, dass die Verwendung von Formeln viel Unannehmlichkeiten verursachen könnte, empfehlen wir Ihnen hier die Funktion "Text extrahieren" von "Kutools für Excel". Diese Funktion hilft dabei, die Extraktionsaufgabe in Excel mit nur wenigen Klicks zu automatisieren.

1. Klicken Sie auf „Kutools“ > „Text“ > „Text extrahieren“, um diese Funktion zu aktivieren.

2. Im Dialogfeld „Text extrahieren“ führen Sie die folgenden Einstellungen durch.

2.1) Stellen Sie sicher, dass Sie sich im Tab „Nach Position extrahieren“ befinden;

2.2) Klicken Sie im Feld "Bereich" auf die Schaltfläche, um den Zellbereich auszuwählen, aus dem Sie Texte extrahieren möchten;

2.3) Im Abschnitt "Optionen":

Um alle Texte vor einem Wort zu extrahieren, wählen Sie das Optionsfeld "vor dem Text" und geben dann das Wort in das Textfeld ein;

Um alle Texte nach einem Wort zu extrahieren, wählen Sie die Option "Nach dem Text" und geben dann das Wort in das Textfeld ein.

2.4) Klicken Sie auf die Schaltfläche „OK“. Siehe Screenshot:

Hinweis: Wenn Sie dynamische Ergebnisse erstellen möchten, aktivieren Sie das Kontrollkästchen „Als Formel einfügen“. Dann werden die Ergebnisse automatisch aktualisiert, wenn sich die Daten im Bereich ändern.

3. Anschließend erscheint ein Dialogfeld „Text extrahieren“. Sie müssen eine Zelle auswählen, um das Ergebnis auszugeben, und dann auf die Schaltfläche „OK“ klicken.

Dann werden die Texte vor oder nach einem bestimmten Wort in jeder Zelle des ausgewählten Bereichs sofort extrahiert.

Hinweis: Diese Funktion unterscheidet zwischen Groß- und Kleinschreibung.

Klicken Sie hier, um mehr über diese Funktion zu erfahren.

3. Extrahieren zwischen Zeichen/Wörtern

Wenn Sie Text zwischen bestimmten Zeichen oder Wörtern extrahieren möchten, probieren Sie die folgenden Methoden aus.

3.1 Text zwischen zwei Zeichen extrahieren

Um Text zwischen zwei Zeichen zu extrahieren, die gleich oder unterschiedlich sein können, bietet dieser Abschnitt mehrere Methoden, und Sie können diejenige auswählen, die Ihren Anforderungen am besten entspricht.

 3.1.1 Text zwischen zwei gleichen Zeichen mit Formel extrahieren

Wie im folgenden Screenshot gezeigt, gibt es eine Liste von Textzeichenfolgen in Spalte B, und Sie möchten den Zahlenanteil zwischen den Zeichen „/“ aus jeder Zelle im Bereich extrahieren. Die folgende Formel kann Ihnen dabei helfen.

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein, und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie dann den AutoAusfüllGriff nach unten, um Ergebnisse für andere Zellen in der Liste zu erhalten.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Dann wird der Text zwischen zwei gleichen Zeichen „/“ aus jeder Zelle im Bereich extrahiert. Siehe Screenshot:

Hinweis:

 3.1.2 Text zwischen zwei verschiedenen Zeichen mit Formel extrahieren

Nachdem Sie gelernt haben, wie man Text zwischen zwei gleichen Zeichen innerhalb einer Zelle extrahiert, zeigen wir Ihnen hier eine Formel, um Text zwischen zwei unterschiedlichen Zeichen zu extrahieren. Wie im Screenshot unten dargestellt, können Sie die E-Mail-Adresse zwischen den Zeichen „<“ und „>“ aus jeder Zelle in Spalte B wie folgt extrahieren.

Allgemeine Formel

=TEIL(LINKS(Zelle;FINDEN("end_char";Zelle)-1);FINDEN("start_char";Zelle)+1;LÄNGE(Zelle))

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann deren AutoFill-Handle nach unten, um diese Formel auf andere Zellen anzuwenden.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Wie Sie sehen können, wird nur der Text zwischen den angegebenen Zeichen extrahiert, wie im obigen Screenshot dargestellt.

3.1.3 Text zwischen zwei Zeichen mit einem erstaunlichen Tool extrahieren

Hier empfehlen wir Ihnen die Funktion „Den Text zwischen angegebenen Texten extrahieren“ von „Kutools für Excel“, um Ihnen dabei zu helfen, ganz einfach Text zwischen zwei gleichen oder unterschiedlichen Zeichen innerhalb einer Zelle in Excel zu extrahieren.

1. Wählen Sie eine leere Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf „Kutools“ > „Formelhelfer“ > „Formelhelfer“.

2. Führen Sie im Dialogfeld "Formelhelfer" die folgenden Einstellungen aus.

3. Anschließend wird nur der Text zwischen „<“ und „>“ in der Zelle B5 extrahiert. Gleichzeitig wurde eine Formel erstellt. Sie können diese Ergebniszelle auswählen und dann ihren AutoFill-Handle nach unten ziehen, um Texte aus anderen Zellen in der gleichen Liste zu extrahieren.

Klicken Sie hier, um mehr über diese Funktion zu erfahren.

3.1.4 Text zwischen zwei Zeichen (einschließlich der Zeichen) nach Regel extrahieren

Wenn Sie die beiden Zeichen nach dem Extrahieren beibehalten möchten, versuchen Sie, eine Regel in der Funktion Text extrahieren von Kutools für Excel anzuwenden.

1. Klicken Sie auf „Kutools“ > „Text“ > „Text extrahieren“.

2. Nehmen Sie im Dialogfeld „Text extrahieren“ die folgenden Einstellungen vor.

2.1) Klicken Sie auf den Tab „Nach Regel extrahieren“;

2.2) Klicken Sie im Abschnitt „Bereich“ auf die Schaltfläche, um den Zellbereich auszuwählen, aus dem Sie den Text zwischen Zeichen extrahieren möchten;

2.3) Geben Sie im Feld "Text" "<*>" ein.

Tipp: „<“ und „>“ sind die Zeichen, zwischen denen Sie den Text extrahieren möchten. * ist ein Platzhalter, der eine beliebige Anzahl von Zeichen darstellt. Sie können die Bedingung entsprechend Ihren Anforderungen eingeben.

2.4) Klicken Sie auf die Schaltfläche „Hinzufügen“, um die Bedingung zur Liste „Regelbeschreibung“ hinzuzufügen.

2.5) Klicken Sie auf die Schaltfläche „OK“. Siehe Screenshot:

3. Ein weiteres Dialogfeld „Text extrahieren“ wird geöffnet. Bitte wählen Sie eine Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf die Schaltfläche „OK“.

Dann wird der Text zwischen den angegebenen Zeichen (einschließlich der Zeichen) aus jeder Zelle im ausgewählten Bereich in großen Mengen extrahiert.

3.2 Text zwischen zwei Wörtern extrahieren

Neben dem Extrahieren von Text zwischen zwei Zeichen müssen Sie möglicherweise auch Text zwischen zwei Wörtern extrahieren. Zum Beispiel können Sie alle Textstrings zwischen den beiden Wörtern „KTE“ und „feature“ aus jeder Zelle in Spalte B extrahieren, wie im Screenshot unten dargestellt. Sie können eines der folgenden Verfahren ausprobieren, um dies zu erreichen.

3.2.1 Text zwischen zwei Wörtern mit Formel extrahieren

Sie können eine Formel verwenden, die auf der MID-Funktion und der SEARCH-Funktion basiert, um alle Textzeichenfolgen zwischen zwei Wörtern innerhalb einer Zelle zu extrahieren.

Allgemeine Formel

=TEIL(Zelle;SUCHEN("start_word";Zelle)+3;SUCHEN("end_word";Zelle)-SUCHEN("start_word";Zelle)-4)

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann deren AutoAusfüllkästchen nach unten, um diese Formel auf andere Zellen anzuwenden.

=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)

Hinweis: In der Formel steht die Zahl 3 für die Zeichenlänge des Wortes "KTE"; die Zahl 4 steht für die Zeichenlänge des Wortes "KTE" plus 1.

Sie können sehen, dass alle Textzeichenfolgen zwischen den zwei angegebenen Wörtern aus jeder Zelle in Spalte B extrahiert werden.

3.2.2 Text zwischen zwei Wörtern mit einem erstaunlichen Tool extrahieren

Für viele Excel-Benutzer können Formeln schwer zu merken und zu handhaben sein. Hier können Sie mit der Funktion "Den Text zwischen angegebenen Texten extrahieren" von "Kutools für Excel" ganz einfach Text zwischen zwei Wörtern mit nur wenigen Klicks extrahieren.

1. Wählen Sie eine Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf „Kutools“ > „Formelhelfer“ > „Formelhelfer“.

2. Im Dialogfeld "Formelhelfer" müssen Sie wie folgt konfigurieren:

3. Anschließend werden alle Textzeichenfolgen zwischen den beiden Wörtern „KTE“ und „feature“ innerhalb der Zelle B5 extrahiert. Gleichzeitig wurde eine Formel erstellt, Sie können diese Ergebniszelle auswählen und dann ihren AutoFill-Handle nach unten ziehen, um Texte aus anderen Zellen in derselben Liste zu extrahieren.

Zahlen nach Position extrahieren

Für eine Liste alphanumerischer Zeichenfolgen kann es drei Fälle geben:

1. Die Zahl befindet sich am Anfang des Textes;
2. Die Zahl befindet sich am Ende des Textes;
3. Die Zahl kann sich an beliebiger Stelle im Text befinden.

In diesem Abschnitt stellen wir verschiedene Methoden vor, die verwendet werden können, um die Zahlen in jedem der oben genannten Fälle zu extrahieren.

1 Zahl von links in einer Zeichenfolge extrahieren

Dieser Teil stellt eine Formel vor, die Ihnen hilft, nur die Zahlen zu extrahieren, die in einer Zelle vor dem Text erscheinen.

Allgemeine Formel

=LINKS(Zelle; VERGLEICH(FALSCH; ISTZAHL(TEIL(Zelle; ZEILE(INDIREKT("1:"&LÄNGE(Zelle)+1)); 1) *1); 0) -1)

Argumente

Hinweis:

Wählen Sie eine leere Zelle aus, geben Sie die folgende Formel ein und drücken Sie "Strg" + "Umschalt" + "Eingabe" oder die "Eingabetaste", um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoAusfüllgriff nach unten, um die Zahlen anderer Zellen zu erhalten.

=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)

Hinweise:

2 Zahlen von rechts in einer Zeichenfolge extrahieren

Wie im folgenden Screenshot gezeigt, um nur die Zahlen zu extrahieren, die in einer Zelle nach Text erscheinen, probieren Sie die folgende Formel aus.

Allgemeine Formel

=RECHTS(Zelle; LÄNGE(Zelle) - MAX(WENN(ISTZAHL(TEIL(Zelle; ZEILE(INDIREKT("1:"&LÄNGE(Zelle))); 1)*1)=FALSCH; ZEILE(INDIREKT("1:"&LÄNGE(Zelle))); 0)))

Argumente

Hinweis:

Wählen Sie eine leere Zelle aus, geben Sie die folgende Formel ein und drücken Sie "Strg" + "Umschalt" + "Eingabe" oder die "Eingabetaste", um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoAusfüllgriff nach unten, um die Zahlen anderer Zellen zu erhalten.

=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))

Hinweise:

3. Extrahieren Sie alle Zahlen aus jeder Position in einer Textzeichenfolge

Die oben genannten Methoden helfen dabei, Zahlen nur vom linken oder rechten Ende einer Textzeichenfolge zu extrahieren. Wenn Sie alle Zahlen aus einer Textzeichenfolge unabhängig von ihrer Position extrahieren möchten, bieten wir Ihnen hier drei Methoden, um dies zu erreichen.

3.1 Extrahieren Sie alle Zahlen aus einer Zeichenfolge an beliebiger Stelle mit einer Formel

Sie können die folgende Formel anwenden, um alle Zahlen aus einer Textzeichenfolge in Excel zu extrahieren.

1. Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um alle Zahlen aus Zelle B5 zu erhalten.

=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)

2. Wählen Sie die Ergebniszelle aus und ziehen Sie dann deren AutoFill-Handle nach unten, um alle Zahlen der anderen Zellen zu erhalten.

3.2 Extrahieren Sie alle Zahlen aus einer Zeichenfolge an beliebiger Stelle mit VBA

Die obige Formel ist für viele Excel-Benutzer zu lang und kompliziert. Tatsächlich können Sie ein VBA-Skript ausführen, um die Aufgabe in Excel zu automatisieren. Sie können wie folgt vorgehen.

1. Drücken Sie die Tasten „Alt“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Im geöffneten Fenster „Microsoft Visual Basic for Applications“ auf „Einfügen“ > „Modul“ klicken. Dann das folgende VBA in das Modul-Codefenster kopieren.

VBA-Code: Alle Zahlen aus einer Textzeichenfolge extrahieren

Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. Drücken Sie die Taste „F5“, um den Code auszuführen. Wählen Sie im sich öffnenden Dialogfeld „KutoolsforExcel“ den Zellbereich aus, aus dem Sie alle Zahlen aus jeder Zelle extrahieren möchten, und klicken Sie dann auf die Schaltfläche „OK“.

4. Anschließend erscheint ein weiteres Dialogfeld „KutoolsforExcel“. Wählen Sie in diesem Dialogfeld eine Zielzelle aus und klicken Sie auf „OK“.

Dann werden alle Zahlen in Massen aus jeder Zelle im ausgewählten Bereich extrahiert.

4. Zahlen nach bestimmtem Text extrahieren

Wie im Screenshot unten gezeigt, können Sie mit zwei Methoden beliebige Zahlen nach dem spezifischen Text „No.“ extrahieren. Dieser Abschnitt hilft Ihnen dabei, dies zu erledigen.

4.1 Zahlen nach einem bestimmten Text mit Formel extrahieren

Sie können die folgende Formel anwenden, um Zahlen nach einem bestimmten Text in einer Zelle in Excel zu extrahieren.

Allgemeine Formel:

=SUCHEN(10^6,1*TEIL(Zelle;MIN(FINDEN({0,1,2,3,4,5,6,7,8,9};Zelle&"0123456789";FINDEN("Text"," "&Zelle&" ")));{2,3,4,5,6}))

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die folgende Formel oder geben Sie sie ein und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren Ausfüllkästchen nach unten, um diese Formel auf andere Zellen anzuwenden.

=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))

Hinweise:

4.2 Zahlen nach einem bestimmten Text mit benutzerdefinierter Funktion extrahieren

Die folgende benutzerdefinierte Funktion kann ebenfalls helfen, Zahlen nach einem bestimmten Text in einer Zelle zu extrahieren. Bitte gehen Sie wie folgt vor.

1. Drücken Sie die Tasten „Alt“ + „F11“, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie im Fenster „Microsoft Visual Basic for Applications“ auf „Einfügen“ > „Modul“ und kopieren Sie den folgenden VBA-Code in das Modulcode-Fenster.

VBA-Code: Zahlen nach einem bestimmten Text in einer Zelle extrahieren

Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
    xValue = Rng.Text
    xStart = InStr(1, xValue, Char, vbTextCompare)
    If IsEmpty(xStart) Then
            GetNumberAfterTheChar = ""
            Exit Function
    End If
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xStart = xStart - 1 + Len(Char)
    If xStart < 1 Then
        GetNumberAfterTheChar = ""
        Exit Function
    End If
    xValue = Mid(xValue, xStart + 1)
    xRntString = ""
    For xI = 1 To Len(xValue)
        xC = Mid(xValue, xI, 1)
        Select Case Asc(xC)
        Case 48 To 57
            xRntString = xRntString & xC
       Case Else
            Exit For
        End Select
    Next
   GetNumberAfterTheChar = xRntString
End Function

3. Drücken Sie die Tasten „Alt“ + „Q“, um das Fenster „Microsoft Visual Basic for Applications“ zu schließen.

4. Wählen Sie eine Zelle aus, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoAusfüllgriff nach unten, um diese Formel auf andere Zellen anzuwenden.

=GetNumberAfterTheChar(B5,"No. ")

Hinweise:

Verwandte Artikel: