Excel-Tutorial – Extrahieren Sie Text oder Zahlen nach einer bestimmten Position

In vielen Fällen müssen Sie möglicherweise nur den nützlichen Inhalt aus einem Satz oder einer Textzeichenfolge in einer Zelle extrahieren, z. und so weiter. Dieses Tutorial grenzt die Extraktion auf eine bestimmte Position in einer Zelle ein und sammelt verschiedene Methoden, um Text oder Zahlen aus einer Zelle anhand einer bestimmten Position in Excel zu extrahieren.

---

Text nach Position extrahieren

Dieser Abschnitt sammelt allgemeine Positionen, an denen ein Text aus einer Zelle extrahiert werden kann, und bietet entsprechende Methoden, um diese Schritt für Schritt zu handhaben. Sie können nach weiteren Details suchen.

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 Sie die ersten oder letzten N Zeichen mit Formeln

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

Extrahieren Sie die ersten N Zeichen aus einer Textzeichenfolge

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

Generische Formel

=LEFT(text_string,[num_chars])

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 Formel unten oder geben Sie sie ein und drücken Sie die Enter Taste, um das erste Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um die Formel auf andere Zellen anzuwenden.

=LEFT(B5,2)

Jetzt wurden die ersten 2 Zeichen in jeder Zelle des Bereichs B5:B10 extrahiert.

Extrahieren Sie die letzten N Zeichen aus einer Textzeichenfolge

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

Generische Formel

=RIGHT(text_string,[num_chars])

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie dann diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um andere Ergebnisse zu erhalten.

=RIGHT(B5,2)

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

Obwohl die obigen Formeln einfach sind, müssen Sie zum Extrahieren der ersten oder letzten n Zeichen aus einer langen Liste von Textzeichenfolgen immer noch das AutoFill-Handle von oben nach unten ziehen, was möglicherweise etwas zeitaufwändig ist. Hier empfiehlt Kutools for Excel Text extrahieren Dienstprogramm zum Extrahieren der ersten oder letzten N Zeichen aus einer Liste von Textzeichenfolgen in großen Mengen.

1. Wählen Sie die Textstringliste aus, aus der Sie Text extrahieren möchten, und klicken Sie auf Kutoolen > Text > Text extrahieren.

2. Beim Auftauchen Text extrahieren Dialogfeld müssen Sie wie folgt konfigurieren.

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

3. Beim nächsten Auftauchen Text extrahieren Wählen Sie im Dialogfeld eine Zelle aus, um die extrahierten Zeichen auszugeben, und klicken Sie dann auf OK.

Dann werden die angegebenen ersten oder letzten N Zeichen in großen Mengen aus ausgewählten Zellen 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 Ihre Anforderungen erfüllen.

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

Wie im Screenshot unten gezeigt, können Sie eine der folgenden 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

Das Anwenden einer Formel basierend auf den Funktionen LINKS und SUCHEN kann helfen, Text vor dem ersten Trennzeichen aus einer Zelle zu extrahieren. Sie können die folgenden Schritte ausführen, um dies zu erledigen.

Generische Formel

=LEFT(text_string,FIND("delimiter",text_string,1)-1)

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die unten stehende Formel oder geben Sie sie ein und drücken Sie die Enter Taste, um das erste Ergebnis zu erhalten. Wählen Sie die erste Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle 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 beim Extrahieren von Text nach dem ersten Trennzeichen aus einer Zelle in Excel.

Generische Formel

=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))

Argumente

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

2.1.3 Extrahieren Sie Text vor oder nach dem ersten Trennzeichen mit einem erstaunlichen Werkzeug

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

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

2. In dem Text extrahieren Dialogfeld 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. Dann noch eine Text extrahieren Dialogfeld erscheint, wählen Sie eine Zelle aus, um die Ergebnisse auszugeben, und klicken Sie auf OK.

Dann werden Texte vor oder nach dem ersten Trennzeichen auf einmal aus ausgewählten Zellen extrahiert.

Um mehr über diese Funktion zu erfahren, besuchen Sie bitte: Extrahieren Sie schnell bestimmten Text 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, zeigt Ihnen dieser Abschnitt zwei Formeln zum Extrahieren von Text vor oder nach dem letzten Trennzeichen aus einer Zelle. Sie können die folgenden Schritte ausführen, 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 SEARCH, LEN und SUBSTITUTE innerhalb der LEFT-Funktion verwenden.

Generische Formel

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

Argumente

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle 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 Text vor dem letzten Trennzeichen aus einer Zelle extrahiert haben, können Sie die folgende Formel anwenden, um den Text nach dem letzten Trennzeichen nach Bedarf zu extrahieren.

Generische Formel

=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))

Argumente

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um die Texte aus anderen Textzeichenfolgen in derselben Spalte zu extrahieren.

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

2.3 Text nach dem n-ten Zeichen extrahieren

Schauen Sie sich das Beispiel unten 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.

Generische Formel

=MID(text_string,nth_char+1,LEN(text_string))

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die unten stehende Formel oder geben Sie sie ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle nach unten, um andere Ergebnisse zu erhalten.

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

2.4 Extrahiere das n-te Wort aus einer Textzeichenfolge

Angenommen, Sie haben eine Liste mit 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 erledigen.

2.4.1 Extrahiere das n-te Wort mit Formel

Sie können die Funktionen TRIM, MID, SUBSTITUTE, REPT und LEN kombinieren, um das n-te Wort aus einer Textzeichenfolge in einer Zelle zu extrahieren.

Generische Formel

=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((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 unten stehende Formel oder geben Sie sie ein und drücken Sie die Enter Taste, um das erste Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie ihren AutoFill-Handle 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 wie folgt direkt in die Formel eingeben.

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

2.4.2 Extrahieren Sie das n-te Wort mit der benutzerdefinierten Funktion

Abgesehen von der obigen 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 Taste Andere + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. In dem Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > Modul, und kopieren Sie dann die VBA unten in das Codefenster.

VBA-Code: Extrahiere das n-te Wort aus einer Textzeichenfolge in einer Zelle

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 Taste Andere + Q Tasten zum Schließen der Microsoft Visual Basic für Applikationen Fenster.

4. Gehen Sie zurück zu dem 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 unten stehende Formel oder geben Sie sie ein und drücken Sie die Enter Taste, um das n-te Wort zu erhalten.

=FindWord(B5,D5)

Or

=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 Werkzeug

Wenn Sie eine Formel oder eine oben bereitgestellte benutzerdefinierte Funktion nicht manuell anwenden möchten, empfiehlt sich hier Kutools für Excel Extrahieren Sie das n-te Wort in der Zelle Dienstprogramm. Mit dieser Funktion können Sie das n-te Wort ganz einfach mit wenigen Klicks aus einer Textzeichenfolge in einer Zelle extrahieren.

1. Wählen Sie eine Zelle aus, um das Ergebnis zu platzieren, und klicken Sie auf Kutoolen > Formel-Helfer > Text > Extrahieren das n-te Wort in der Zelle. Siehe Screenshot:

2. In dem Formeln Helfer Dialogfeld müssen Sie wie folgt konfigurieren.

3. Dann wird das n-te (zweite) Wort aus der Textzeichenfolge in der 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 gezeigt. Um den Text vor oder nach dem zweiten Vorkommen eines Leerzeichens zu extrahieren, bietet dieser Abschnitt zwei Formeln, die Ihnen dabei helfen.

2.5.1 Text vor dem n-ten Vorkommen eines Trennzeichens extrahieren

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

Generische Formel

=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)

Argumente

Wählen Sie eine Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie sie AutoFill-Handle nach unten, um andere 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 Vorkommen eines Leerzeichens. Sie können sie nach Ihren Bedürfnissen ä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 Funktion RECHTS mit den Funktionen SUBSTITUTE, LEN und FIND anwenden.

Generische Formel

=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)

Argumente

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

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie sie AutoFill Handle nach unten, um andere 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 Zeilenumbruch extrahieren

Angenommen, Sie haben eine Bestellliste in Spalte B und möchten nur den Datumsteil und den Produktnr.-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 Datumsteil vor dem ersten Zeilenumbruch innerhalb der Zelle. In diesem Abschnitt wird eine LEFT-Funktion zusammen mit der SEARCH-Funktion demonstriert, die Ihnen hilft, den Text vor dem ersten Zeilenumbruch innerhalb einer Zelle zu extrahieren.

Generische Formel

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

Argumente

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

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

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

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 Sie Text vor dem ersten Zeilenumbruch innerhalb einer Zelle extrahieren. In diesem Teil erfahren Sie, wie Sie Text nach dem letzten Zeilenumbruch in einer Zelle mit einer anderen Formel extrahieren.

Generische Formel

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

Argumente

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

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

Dann die Produkt-Nr. Ein Teil jeder Zelle in der Liste wird extrahiert, wie im obigen Screenshot gezeigt.

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 Teilen 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? In diesem Abschnitt werden drei Methoden vorgestellt, die Ihnen bei dieser Aufgabe helfen.

2.7.1 Text vor einem bestimmten Wort mit Formel extrahieren

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

Generische Formel

=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)

Argumente

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

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

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

Anmerkungen:

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.

Generische Formel

=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))

Argumente

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie 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 alle Texte sehen, nachdem das Wort „Excel“ in jeder Zelle extrahiert wurde, wie im Screenshot unten gezeigt.

Anmerkungen:

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

Wenn Sie der Meinung sind, dass die Verwendung der Formel viele Unannehmlichkeiten verursachen kann, empfehlen wir Ihnen hier dringend die Text extrahieren Nutzen von Kutools for Excel. Diese Funktion hilft, die Extraktionsaufgabe in Excel mit nur wenigen Klicks zu automatisieren.

1 Klicken Kutoolen > Text > Text extrahieren , um diese Funktion zu aktivieren.

2. In dem Text extrahieren Dialogfeld, nehmen Sie die folgenden Einstellungen vor.

Anmerkungen: Wenn Sie dynamische Ergebnisse erstellen möchten, aktivieren Sie die Als Formel einfügen Kasten. Dann werden die Ergebnisse automatisch aktualisiert, wenn sich die Daten im Bereich ändern.

3. Dann ein Text extrahieren Dialogfeld erscheint, müssen Sie eine Zelle auswählen, um das Ergebnis auszugeben, und dann auf klicken OK .

Dann werden Texte vor oder nach einem bestimmten Wort innerhalb jeder Zelle im ausgewählten Bereich sofort extrahiert.

Hinweis: Bei dieser Funktion muss die Groß-/Kleinschreibung beachtet werden.

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

---

3. Auszug zwischen Zeichen/Wörtern

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

3.1 Text zwischen zwei Zeichen extrahieren

Um Text zwischen zwei Zeichen zu extrahieren, die dieselben oder unterschiedliche Zeichen sein können. In diesem Abschnitt finden Sie mehrere Methoden, von denen Sie je nach Bedarf eine davon auswählen können.

3.1.1 Text zwischen zwei gleichen Zeichen mit Formel extrahieren

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

Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie die Ergebniszelle aus und ziehen Sie sie dann AutoFill Handle nach unten, um Ergebnisse anderer 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 wir gelernt haben, wie Sie Text zwischen zwei gleichen Zeichen innerhalb einer Zelle extrahieren, zeigen wir hier eine Formel zum Extrahieren von Text zwischen zwei verschiedenen Zeichen. Wie im Screenshot unten gezeigt, können Sie wie folgt vorgehen, um nur die E-Mail-Adresse zwischen „<“ und „>“ aus jeder Zelle in Spalte B zu extrahieren.

Generische Formel

=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))

Argumente

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

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

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

3.1.3 Extrahieren Sie Text zwischen zwei Zeichen mit einem erstaunlichen Werkzeug

Hier sehr zu empfehlen Extrahieren Sie Zeichenfolgen zwischen dem angegebenen Text Merkmal von Kutools for Excel um Ihnen zu helfen, Text zwischen zwei gleichen oder unterschiedlichen Zeichen innerhalb einer Zelle in Excel einfach zu extrahieren.

1. Wählen Sie eine leere Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf Kutoolen > Formel-Helfer > Formel-Helfer.

2. In dem Formeln Helfer Dialogfeld, nehmen Sie die folgenden Einstellungen vor.

3. Dann wird nur der Text zwischen „<“ und „>“ innerhalb der Zelle B5 extrahiert. Inzwischen 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.

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 for Excel anzuwenden.

1 Klicken Kutoolen > Text > Text extrahieren.

2. In dem Text extrahieren Dialogfeld, nehmen Sie die folgenden Einstellungen vor.

3. Ein anderer Text extrahieren Dialogfeld erscheint, wählen Sie bitte eine Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf OK .

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

3.2 Text zwischen zwei Wörtern extrahieren

Zusätzlich zum Extrahieren von Text zwischen zwei Zeichen müssen Sie möglicherweise auch Text zwischen zwei Wörtern extrahieren. Extrahieren Sie beispielsweise alle Textzeichenfolgen zwischen den beiden Wörtern „KTE“ und „Feature“ aus jeder Zelle in Spalte B, wie im Screenshot unten gezeigt. Sie können eine der folgenden Methoden ausprobieren, um dies zu erledigen.

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.

Generische Formel

=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)

Argumente

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

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

Note: 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 angegebenen zwei Wörtern aus jeder Zelle in Spalte B extrahiert werden.

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

Für viele Excel-Benutzer sind Formeln möglicherweise schwer zu merken und zu handhaben. Hier mit dem Extrahieren Sie Zeichenfolgen zwischen dem angegebenen Text Merkmal von Kutools for Excel, können Sie mit nur wenigen Klicks Text zwischen zwei Wörtern extrahieren.

1. Wählen Sie eine Zelle aus, um das Ergebnis auszugeben, und klicken Sie dann auf Kutoolen > Formel-Helfer > Formel-Helfer.

2. In dem Formel-Helfer Dialogfeld müssen Sie wie folgt konfigurieren.

3. Dann werden alle Textstrings zwischen den beiden Wörtern „KTE“ und „feature“ innerhalb der Zelle B5 extrahiert. Inzwischen 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

Bei einer Liste alphanumerischer Zeichenfolgen kann es drei Fälle geben:

1. Die Nummer steht am Anfang des Textes;
2. Die Nummer steht am Ende des Textes;
3. Die Nummer kann überall im Text stehen.

In diesem Abschnitt stellen wir verschiedene Methoden vor, mit denen die oben genannten Zahlen in jedem Fall extrahiert werden können.

1 Extrahieren Sie eine Zahl von der linken Seite einer Zeichenfolge

In diesem Teil wird eine Formel eingeführt, mit der Sie nur Zahlen extrahieren können, die vor dem Text in einer Zelle stehen.

Generische Formel

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

Argumente

Hinweis:

Wähle eine leere Zelle aus, gib die Formel unten ein und drücke Ctrl + Shift + Enter or Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoFill-Handle nach unten, um die Nummern anderer Zellen zu erhalten.

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

Anmerkungen:

---

2 Extrahieren Sie die Zahl rechts von einer Zeichenfolge

Wie im Screenshot unten gezeigt, versuchen Sie die folgende Formel, um nur die Zahlen zu extrahieren, die nach dem Text in einer Zelle erscheinen.

Generische Formel

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

Argumente

Hinweis:

Wähle eine leere Zelle aus, gib die Formel unten ein und drücke Ctrl + Shift + Enter or Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann ihren AutoFill-Handle nach unten, um die Nummern 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)))

Anmerkungen:

---

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

Die oben genannten Methoden helfen, Zahlen nur aus der linken oder rechten Seite einer Textzeichenfolge zu extrahieren. Wenn Sie alle Zahlen von einer beliebigen Stelle in einer Textzeichenfolge extrahieren möchten, stellen wir Ihnen hier drei Methoden zur Verfügung, um dies zu erledigen.

3.1 Extrahieren Sie alle Zahlen von überall in einer Zeichenfolge mit Formel

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

1. Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Enter Taste, um alle Zahlen aus der 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 ihren AutoFill-Handle nach unten, um alle Nummern anderer Zellen zu erhalten.

3.2 Extrahieren Sie alle Zahlen von überall in einem String mit VBA

Die obige Formel ist für viele Excel-Benutzer zu lang und zu 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 Taste Andere + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. In der Öffnung Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > Modul. Kopieren Sie dann die untenstehende VBA in das Modulcodefenster.

VBA-Code: Extrahiere alle Zahlen von überall in einer Textzeichenfolge

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 Schlüssel zum Ausführen des Codes. In der Eröffnung KutoolsforExcel Wählen Sie im Dialogfeld den Zellbereich aus, in dem Sie alle Zahlen aus jeder Zelle extrahieren möchten, und klicken Sie dann auf das OK .

4. Dann noch eine KutoolsforExcel Dialogfeld erscheint. Wählen Sie in diesem Dialogfeld eine Zielzelle aus und klicken Sie auf OK.

Dann werden alle Zahlen aus jeder Zelle im ausgewählten Bereich in großen Mengen extrahiert.

---

4. Extrahieren Sie Zahlen nach einem bestimmten Text

Wie im Screenshot unten gezeigt, bietet dieser Abschnitt zwei Methoden zum Extrahieren von Zahlen nach dem spezifischen Text „Nr.“

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.

Generische Formel:

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

Argumente

Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Enter Schlüssel, um das Ergebnis zu erhalten. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann den AutoFill-Handle 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}))

Anmerkungen:

4.2 Extrahieren von Zahlen nach einem bestimmten Text mit benutzerdefinierter Funktion

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

1. Drücken Sie die Taste Andere + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. In dem Microsoft Visual Basic für Applikationen Klicken Sie im Fenster Insert > Modul, und kopieren Sie dann den VBA-Code unten in das Modulcodefenster.

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

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 Taste Andere + Q Tasten zum Schließen der Microsoft Visual Basic für Applikationen Fenster.

4. Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Enter Schlüssel. Wählen Sie diese Ergebniszelle aus und ziehen Sie dann den AutoFill-Handle nach unten, um diese Formel auf andere Zellen anzuwenden.

=GetNumberAfterTheChar(B5,"No. ")

Anmerkungen:

---

In Verbindung stehende Artikel: