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.

Dieses Tutorial behandelt hauptsächlich: (Klicken Sie auf eine Überschrift im Inhaltsverzeichnis unten oder rechts, um zum entsprechenden Inhalt zu navigieren.)

Text nach Position extrahieren

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

1.1 Extrahieren Sie die ersten oder letzten N Zeichen mit Formeln

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

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

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

2.1.1 Text vor dem ersten Trennzeichen mit Formeln extrahieren

2.1.2 Text nach dem ersten Trennzeichen mit Formel extrahieren

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

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

2.2.1 Text vor dem letzten Trennzeichen mit Formel extrahieren

2.2.2 Text nach dem letzten Trennzeichen mit Formel extrahieren

2.3 Text nach dem n-ten Zeichen extrahieren

2.4 Extrahiere das n-te Wort aus einer Textzeichenfolge

2.4.1 Extrahiere das n-te Wort mit Formel

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

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

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

2.5.1 Text vor dem n-ten Vorkommen eines Trennzeichens extrahieren

2.5.2 Text nach dem n-ten Vorkommen eines Trennzeichens extrahieren

2.6 Text vor oder nach Zeilenumbruch extrahieren

2.6.1 Text vor dem ersten Zeilenumbruch mit Formel extrahieren

2.6.2 Text nach dem letzten Zeilenumbruch mit Formel extrahieren

2.7 Text vor oder nach einem Wort extrahieren

2.7.1 Text vor einem bestimmten Wort mit Formel extrahieren

2.7.2 Text nach einem bestimmten Wort mit Formel extrahieren

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

3. Auszug zwischen Zeichen/Wörtern

3.1 Text zwischen zwei Zeichen extrahieren

3.1.1 Text zwischen zwei gleichen Zeichen mit Formel extrahieren

3.1.2 Text zwischen zwei verschiedenen Zeichen mit Formel extrahieren

3.1.3 Extrahieren Sie Text zwischen zwei Zeichen mit einem erstaunlichen Werkzeug

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

3.2 Text zwischen zwei Wörtern extrahieren

3.2.1 Text zwischen zwei Wörtern mit Formel extrahieren

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

Zahlen nach Position extrahieren

1. Extrahieren Sie eine Zahl von der linken Seite einer Zeichenfolge

2. Extrahieren Sie die Zahl rechts von einer Zeichenfolge

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

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

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

3.3 Extrahieren Sie alle Zahlen von überall in einer Zeichenfolge mit einem erstaunlichen Werkzeug

4. Nummer nach spezifischem Text extrahieren

4.1 Zahl nach einem bestimmten Text mit Formel extrahieren

4.2 Nummer nach einem bestimmten Text mit benutzerdefinierter Funktion 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

Textzeichenfolge: Die Textzeichenfolge, die die zu extrahierenden Zeichen enthält. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

Anzahl_chars: Gibt die Anzahl der zu extrahierenden Zeichen an.

Die Num_chars müssen größer oder gleich null sein;

Wenn die angegebene Num_chars größer als die Länge des Textstrings ist, wird der gesamte Text zurückgegeben;

Wenn The Num_chars weggelassen wird, wird 1 angenommen.

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 Weiter 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

Anzahl_chars: Gibt die Anzahl der zu extrahierenden Zeichen an.

Die Anzahl_chars muss größer oder gleich Null sein;

Wenn das angegebene Anzahl_chars größer als die Länge der Textzeichenfolge ist, gibt es den gesamten Text zurück;

Wenn die Anzahl_chars weggelassen wird, wird angenommen, dass es 1 ist.

Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Weiter 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.

2.1) Stellen Sie sicher, dass Sie in der bleiben Nach Ort extrahieren Tab;

2.2) In der Abdeckung im Feld wird der ausgewählte Bereich angezeigt, Sie können je nach Bedarf zu einem anderen Bereich wechseln;

2.3) In der Optionen Abschnitt:

Wenn Sie die ersten N Zeichen extrahieren möchten, wählen Sie die Das erste N-Zeichen Optionsfeld, und geben Sie dann die Anzahl der zu extrahierenden Zeichen in das Textfeld ein. In diesem Fall gebe ich die Zahl 2 ein;

Wenn Sie die letzten N Zeichen extrahieren möchten, wählen Sie die Das letzte N Zeichen Optionsfeld, und geben Sie dann die Anzahl der zu extrahierenden Zeichen in das Textfeld ein. In diesem Fall möchte ich die letzten 2 Zeichen aus der Textzeichenfolge extrahieren, also gebe ich die Zahl 2 in das Textfeld ein.

2.4) Klicken Sie auf OK. Siehe Screenshot:

Hinweis: 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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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

Textzeichenfolge: Die Textzeichenfolge, aus der Sie die Teilzeichenfolge extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

Trennzeichen: Das erste Trennzeichen, das definiert, welcher Text aus einer Zelle extrahiert wird.

Wählen Sie eine leere Zelle aus, kopieren Sie die unten stehende Formel oder geben Sie sie ein und drücken Sie die Weiter 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

Textzeichenfolge: Die Textzeichenfolge, aus der Sie eine Teilzeichenfolge extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

Trennzeichen: Das erste Trennzeichen, das definiert, welcher Text aus einer Zelle extrahiert wird.

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

2.1) Bleiben Sie im Nach Ort extrahieren Tab;

2.2) In der Abdeckung Feld wird der ausgewählte Bereich darin angezeigt, Sie können ihn nach Bedarf ändern;

2.3) In der Optionen Abschnitt:

Um Text vor dem ersten Trennzeichen zu extrahieren, wählen Sie die vor dem Text Optionsfeld, und geben Sie dann das erste Trennzeichen in das Textfeld ein;

Um Text nach dem ersten Trennzeichen zu extrahieren, wählen Sie die nach dem text Optionsfeld, und geben Sie dann das erste Trennzeichen in das Textfeld ein.

2.4) Klicken Sie auf OK.

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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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

Trennzeichen: Das letzte Trennzeichen, das definiert, welcher Text aus einer Zelle extrahiert wird.

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Weiter 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

Trennzeichen: Das letzte Trennzeichen, das definiert, welcher Text aus einer Zelle extrahiert wird.

=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

n-tes_Zeichen: Eine Zahl stellt das n-te Zeichen dar, und Sie werden den Text danach extrahieren.

Wählen Sie eine leere Zelle aus, kopieren Sie die unten stehende Formel oder geben Sie sie ein und drücken Sie die Weiter 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

Textzeichenfolge: Die Textzeichenfolge, aus der Sie das n-te Wort extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

N: Eine Zahl stellt das n-te Wort dar, das Sie aus der Textzeichenfolge extrahieren.

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 Weiter 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

Funktion ExtractTheNthWord(Source As String, Position As Integer) 'Aktualisieren um 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) Ende, wenn Ende Funktion

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 Weiter Taste, um das n-te Wort zu erhalten.

=FindWord(B5,D5)

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

2.1) In der Wählen Sie eine Formelliste Box, die Extrahieren Sie das n-te Wort in der Zelle Option ist hervorgehoben;

2.2) In der Zelle Feld, wählen Sie eine Zelle aus, die die Textzeichenfolge enthält, aus der Sie das n-te Wort extrahieren möchten;

2.3) In der Der Nth Wählen Sie im Feld eine Zelle mit der n-ten Zahl aus oder geben Sie eine Zahl nach Ihren Wünschen direkt ein;

2.4) Klicken Sie auf OK.

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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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

Textzeichenfolge: Die Textzeichenfolge, aus der Sie den Text extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

N: Eine Zahl stellt das n-te Vorkommen eines Trennzeichens dar, vor dem Sie Text extrahieren.

Wählen Sie eine Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Weiter 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

N: Eine Zahl stellt das n-te Vorkommen eines Trennzeichens dar, nach dem Sie Text extrahieren.

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 Weiter 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

Zelle: Die Zelle, aus der Sie den Text vor dem ersten Zeilenumbruch 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 Weiter 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

Zelle: Die Zelle, aus der Sie den Text vor dem ersten Zeilenumbruch extrahieren möchten.

Wählen Sie eine leere Zelle aus, geben Sie die Formel unten ein und drücken Sie die Weiter 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

Zelle: Die Zelle, aus der Sie den Text vor einem bestimmten Wort extrahieren möchten.

Wort: Das Wort, aus dem Sie den gesamten Text davor extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

Wählen Sie eine leere Zelle aus, geben Sie die Formel unten ein und drücken Sie die Weiter 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)

Hinweise:

1) Bei dieser Formel muss die Groß-/Kleinschreibung beachtet werden.

2) Wenn das von Ihnen eingegebene Wort Duplikate in derselben Zelle enthält, extrahiert die Formel den Text nur nach dem ersten Vorkommen.

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

Zelle: Die Zelle, aus der Sie den Text nach einem bestimmten Wort extrahieren möchten.

Wort: Das Wort, aus dem Sie den gesamten Text danach extrahieren möchten. Dies kann ein Zellbezug oder eine tatsächliche Textzeichenfolge sein, die in doppelte Anführungszeichen eingeschlossen ist;

Wählen Sie eine Zelle aus, geben Sie die Formel unten ein und drücken Sie die Weiter 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.

Hinweise:

1) Bei dieser Formel wird die Groß-/Kleinschreibung nicht beachtet.

2) Wenn das von Ihnen eingegebene Wort Duplikate in derselben Zelle enthält, extrahiert die Formel den Text nur nach dem ersten Vorkommen.

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.

2.1) Stellen Sie sicher, dass Sie in der Nach Ort extrahieren Tab;

2.2) In der Abdeckung Klicken Sie auf die Schaltfläche

Schaltfläche, um den Zellbereich auszuwählen, aus dem Sie Texte extrahieren möchten;

2.3) In der Optionen Abschnitt:

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

Um alle Texte nach einem Wort zu extrahieren, wählen Sie die nach dem text Optionsfeld und geben Sie dann das Wort in das Textfeld ein.

2.4) Klicken Sie auf OK Taste. Siehe Screenshot:

Hinweise: 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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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 Weiter 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:

1) B5 ist die Zelle, aus der Sie Text zwischen zwei gleichen Zeichen extrahieren möchten;

2) "/“ sind zwei gleiche Zeichen, zwischen denen Sie Text extrahieren möchten.

Sie müssen diese Variablen basierend auf Ihren eigenen Daten ändern.

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

Zelle: Die Zelle, aus der Sie den Text zwischen zwei verschiedenen Zeichen extrahieren möchten;

Ende_Zeichen: Der Endcharakter der beiden unterschiedlichen Charaktere;

Startzeichen: Das Startzeichen der beiden unterschiedlichen Zeichen.

Wählen Sie eine leere Zelle aus, kopieren Sie die Formel unten oder geben Sie sie ein und drücken Sie die Weiter 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.

2.1) Überprüfen Sie die Filter Kästchen und dann das Wort eingeben "Extrakt" in das Textfeld;

2.2) In der Wählen Sie eine Formelliste Klicken Sie auf die Schaltfläche Extrahieren Sie Zeichenfolgen zwischen dem angegebenen Text Option;

2.3) In der Eingabe von Argumenten Abschnitt:

In den Zelle Wählen Sie im Feld eine Zelle aus, in der Sie den Text extrahieren möchten (hier wähle ich Zelle B5);

In den Starten Sie char (s) Feld, geben Sie das Startzeichen der beiden verschiedenen Zeichen ein;

In den Ende Geben Sie im Feld char(s) das Endzeichen der beiden unterschiedlichen Zeichen ein.

2.4) Klicken Sie auf Ok. Screenshot:

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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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.

2.1) Klicken Sie auf Nach Regel extrahieren Tab;

2.2) In der Abdeckung Abschnitt, klicken Sie auf

Schaltfläche, um den Zellbereich auszuwählen, in dem Sie den Text zwischen den Zeichen extrahieren möchten;

2.3) In der Text Box, geben Sie ein <*>;

Tipps: "<" und ">” sind die Zeichen, zwischen denen Text extrahiert werden soll, * ist ein Platzhalter, der für eine beliebige Anzahl von Zeichen steht. Sie können die Bedingung nach Ihren Wünschen eingeben.

2.4) Klicken Sie auf Verwenden Sie Schaltfläche zum Hinzufügen der Bedingung zum Regelbeschreibung Listenfeld;

2.5) Klicken Sie auf Ok Taste. Siehe Screenshot:

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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

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

Zelle: die Zelle, aus der Sie alle Textzeichenfolgen zwischen zwei Wörtern extrahieren möchten;

Startwort: das Anfangswort der beiden Wörter, aus denen Sie alle Textzeichenfolgen extrahieren möchten;

Länge1: die Zeichenlänge des Startwortes.

Endwort: das Endwort der beiden Wörter, die Sie alle Textstrings davor extrahieren möchten.

Länge2: die Zeichenlänge des Startwortes plus 1.

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

2.1) Überprüfen Sie die Filter Kästchen und dann das Wort eingeben "Extrakt" in das Textfeld;

2.2) In der Wählen Sie eine Formelliste Klicken Sie auf die Schaltfläche Extrahieren Sie Zeichenfolgen zwischen dem angegebenen Text Option;

2.3) In der Eingabe von Argumenten Abschnitt:

In den Zelle Wählen Sie im Feld eine Zelle aus, in der Sie den Text extrahieren möchten (hier wähle ich Zelle B5);

In den Starten Sie char (s) Geben Sie in das Feld das Anfangswort der beiden Wörter ein, aus denen Sie alle Textzeichenfolgen extrahieren möchten;

In den Endzeichen(e) Geben Sie das Endwort der beiden Wörter ein, aus denen Sie alle Textzeichenfolgen davor extrahieren möchten.

2.4) Klicken Sie auf Ok. Screenshot:

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.

Wenn Sie eine kostenlose Testversion (30 Tage) dieses Dienstprogramms wünschen, Bitte klicken Sie, um es herunterzuladenund wenden Sie dann die Operation gemäß den obigen Schritten an.

Zahlen nach Position extrahieren

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

Die Nummer steht am Anfang des Textes;
Die Nummer steht am Ende des Textes;
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

Zelle: Die Zelle, aus der Sie die Zahl links von der Textzeichenfolge extrahieren möchten.

Hinweis:

1) Wenn Sie Excel 2019 und früher verwenden, müssen Sie die Taste drücken Ctrl + Verschiebung + Weiter Tasten, um diese Array-Formel zu bestätigen.

2) Wenn Sie Excel 365 oder Excel 2021 verwenden, bestätigen Sie diese Formel einfach mit dem Weiter Key.

Wähle eine leere Zelle aus, gib die Formel unten ein und drücke Ctrl + Verschiebung + Weiter or Weiter 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)

Hinweise:

1) Enthält eine Zelle nur Zahlen, wird die ganze Zahl extrahiert.

2) Diese Formel extrahiert nur die Zahlen links von einer Textzeichenfolge. Wenn Zahlen in der Mitte oder am Ende der Textzeichenfolge vorhanden sind, werden diese ignoriert.

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

Zelle: Die Zelle, aus der Sie die Zahl am Anfang der Textzeichenfolge extrahieren möchten.

Hinweis:

1) Wenn Sie Excel 2019 und früher verwenden, müssen Sie die Taste drücken Ctrl + Verschiebung + Weiter Tasten, um diese Array-Formel zu bestätigen.

2) Wenn Sie Excel 365 oder Excel 2021 verwenden, bestätigen Sie diese Formel einfach mit der Eingabetaste.

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

Hinweise:

1) Enthält eine Zelle nur Zahlen, wird die ganze Zahl extrahiert.

2) Diese Formel extrahiert nur die Zahlen rechts von einer Textzeichenfolge. Wenn Zahlen in der Mitte oder am Anfang der Textzeichenfolge vorhanden sind, werden sie ignoriert.

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 Weiter 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() 'Aktualisiert von 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( ."Please select textInputsBox( ."Please select textInputBox) :", 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

Zelle: Die Zelle, aus der Sie Zahlen nach einem bestimmten Text extrahieren möchten;

Text: Der Text, aus dem Sie Zahlen extrahieren möchten.

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

1) Wenn eine Zelle den spezifischen Text nicht enthält, gibt die Formel #N/A zurück.

2) Bei der Formel muss die Groß-/Kleinschreibung beachtet werden.

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

Funktion GetNumberAfterTheChar(Rng As Range, Char As String) 'Aktualisiert von 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 Weiter 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. ")

Hinweise:

1) In dieser Formel ist B5 die Zelle, aus der Sie die Zahl extrahieren möchten, und „Nr. ” ist der spezifische Text, aus dem Sie die Zahl danach extrahieren möchten. Sie können sie nach Bedarf ändern.

2) Wenn zwischen dem spezifischen Text und der Nummer ein Trennzeichen steht, fügen Sie das Trennzeichen bitte am Ende des Textes hinzu. In diesem Fall habe ich nach dem Text „Nr.“ ein Leerzeichen eingefügt, das schließlich als „Nr. “.

3) Bei dieser Methode wird die Groß-/Kleinschreibung nicht beachtet;

4) Wenn eine Zelle den spezifischen Text nicht enthält, gibt die Formel ein leeres Ergebnis zurück.

In Verbindung stehende Artikel:

Excel-Tutorial: Text-, Zahlen- und Datumszellen aufteilen (in mehrere Spalten aufteilen)
Dieses Tutorial ist in drei Teile gegliedert: geteilte Textzellen, geteilte Zahlenzellen und geteilte Datumszellen. Jeder Teil enthält verschiedene Beispiele, die Ihnen helfen, den Aufteilungsauftrag zu handhaben, wenn das gleiche Problem auftritt.
Klicken Sie hier, um mehr zu erfahren ...

Excel Text und Zahl in die angegebene Position der Zelle einfügen
In Excel ist das Hinzufügen von Texten oder Zahlen zu Zellen eine sehr häufige Aufgabe. Zum Beispiel das Hinzufügen von Leerzeichen zwischen Namen, das Hinzufügen von Präfixen oder Suffixen zu Zellen oder das Hinzufügen von Bindestrichen zu sozialen Nummern. Hier in diesem Tutorial werden fast alle Hinzufügensszenarien in Excel aufgelistet und die entsprechenden Methoden für Sie bereitgestellt.
Klicken Sie hier, um mehr zu erfahren ...

Excel Zeichen, Wörter, Zahlen aus Textzeichenfolgen entfernen
Angenommen, Sie haben eine lange Liste von Textzeichenfolgen, die Zeichen, Zahlen oder andere spezifische Symbole enthalten. In bestimmten Fällen müssen Sie möglicherweise einige Zeichen basierend auf der Position entfernen, z. B. von rechts, links oder in der Mitte aus den Textzeichenfolgen, oder einige unerwünschte Zeichen und Zahlen aus der Zeichenfolgenliste löschen. Das Finden der Lösungen nacheinander wird Ihnen Kopfschmerzen bereiten. Dieses Tutorial sammelt alle Arten von Methoden zum Entfernen von Zeichen, Wörtern oder Zahlen in Excel.
Klicken Sie hier, um mehr zu erfahren ...

Die besten Tools für die Office-Produktivität

Kutools for Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Vollständige Funktionen 30 Tage kostenlose Testversion. 60 Tage Geld-zurück-Garantie.

Lesen Sie mehr... Kostenfreier Download... Kauf...

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
Erhöht Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!