Zahlen aus einer Zeichenkette suchen und extrahieren – Excel & GS
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie in Excel und Google Sheets eine Zahl aus einer Textzeichenfolge suchen und extrahieren können.
Eine Zahl suchen und aus einer Zeichenfolge extrahieren
Manchmal enthalten Ihre Daten Zahlen und Text und Sie möchten die numerischen Daten extrahieren.
Wenn sich der Zahlenteil auf der rechten oder linken Seite der Zeichenfolge befindet, ist es relativ einfach, die Zahl und den Text zu trennen. Befinden sich die Zahlen jedoch innerhalb der Zeichenfolge, d. h. zwischen zwei Textzeichenfolgen, müssen Sie eine viel kompliziertere Formel verwenden (siehe unten).
TEXTVERKETTEN – Zahlen in Excel 2016 extrahieren
In Excel 2016 wurde die Funktion TEXTVERKETTEN eingeführt, mit der die Zahlen an einer beliebigen Stelle der Textzeichenfolge extrahiert werden können. Hier ist die Formel:
=TEXTVERKETTEN("";WAHR;WENNFEHLER((TEIL(B3;ZEILE(INDIREKT("1:"&LÄNGE(B3)));1)*1);"")))
Schauen wir uns an, wie diese Formel funktioniert:
Die Funktionen ZEILE, INDIREKT und LÄNGE geben eine Reihe von Zahlen zurück, die jedem Zeichen in Ihrer alphanumerischen Zeichenkette entsprechen. In unserem Fall hat „Montag01Tag“ 11 Zeichen, so dass die ZEILE-Funktion das Array {1;2;3;4;5;6;7;8;9;10;11} zurückgibt.
=TEXTVERKETTEN("";WAHR;WENNFEHLER((TEIL(B3; {1;2;3;4;5;6;7;8;9;10;11};1)*1);""))Als Nächstes extrahiert die TEIL-Funktion jedes Zeichen aus der Textzeichenfolge und erstellt ein Array, das Ihre ursprüngliche Zeichenfolge enthält.
=TEXTVERKETTEN("";WAHR,WENNFEHLER(({"M";"o";"n";"t";"a";"g";"0";"1";"T";"a";"g"}*1);""))Durch Multiplikation jedes Wertes im Array mit 1 wird ein Array erstellt, das Fehler enthält, wenn das Array-Element ein Text war.
=TEXTJOIN("";WAHR;WENNFEHLER(({#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;0;1;#WERT!;#WERT!;#WERT!});""))Als nächstes werden die Fehlerwerte mit der Funktion WENNFEHLER entfernt.
=TEXTVERKETTEN("";WAHR;{"";"";"";"";"";"";0;1;"";"";""})Es bleibt nur noch die Funktion TEXTVERKETTEN übrig, die die übrigen Zahlen miteinander verbindet.
Beachten Sie, dass die Formel alle numerischen Zeichen aus Ihrer Zeichenkette zusammenfasst. Wenn Ihre alphanumerische Zeichenfolge beispielsweise Montag01Tag01 lautet, erhalten Sie 0101 als Ergebnis.
Zahlen extrahieren – vor Excel 2016
Vor Excel 2016 konnten Sie eine viel kompliziertere Methode verwenden, um Zahlen aus Text zu extrahieren. Sie können die FINDEN-Funktion zusammen mit den Funktionen WENNFEHLER und MIN verwenden, um sowohl die erste Position des Zahlenteils als auch die erste Position des Textteils nach der Zahl zu bestimmen. Dann extrahieren wir einfach den Zahlenteil mit der TEIL-Funktion.
=TEIL(B3;MIN(WENNFEHLER(FINDEN({0;1;2;3;4;5;6;7;8;9};B3);999999999));MIN(WENNFEHLER(SUCHEN({"a";"b";"c";"d";"e";"f";"g";"h";"I";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z"};B3;MIN(WENNFEHLER(FINDEN({0;1;2;3;4;5;6;7;8;9};B3);999999999)));999999999))-MIN(WENNFEHLER(FINDEN({0;1;2;3;4;5;6;7;8;9};B3);999999999)))
Hinweis: Da es sich um eine Array-Formel handelt, müssen Sie diese durch Drücken von STRG+UMSCHATTASTE+EINGABE eingeben, anstatt nur EINGABE.
Sehen wir uns nun Schritt für Schritt an, wie diese Formel funktioniert.
Die erste Zahl finden
Wir können die Funktion FINDEN verwenden, um die Anfangsposition der Zahl zu finden:
=MIN(WENNFEHLER(FINDEN({1;2;3;4;5;6;7;8;9;0};B3);999999999))
Für das Argument Suchtext der Funktion FINDEN wird die Array-Konstante {0;1;2;3;4;5;6;7;8;9} verwendet, so dass die FINDEN-Funktion für jeden Wert in dieser Array-Konstante eine eigene Suche durchführt.
Das Argument Text der FINDEN-Funktion ist in unserem Fall Montag01Tag, in dem 1 an der Position 8 und 0 an der Position 7 gefunden werden kann, so dass unser Ergebnis-Array wie folgt aussehen wird: {7;8;#WERT;#WERT;#WERT;#WERT;#WERT;#WERT;#WERT;#WERT}
Mit der Funktion WENNFEHLER ersetzen wir die #WERT-Fehler durch 999999999. Dann suchen wir einfach nach dem Minimum in diesem Array und erhalten so die Stelle der ersten Zahl (7).
Bitte beachten Sie, dass es sich bei der obigen Formel um eine Array-Formel handelt, die Sie mit STRG-Umschaltaste-Eingabe eingeben müssen.
Das erste Textzeichen nach der Zahl finden
Ähnlich wie bei der Suche nach der ersten Zahl in der Zeichenkette verwenden wir die Funktion FINDEN, um festzustellen, wo der Text nach der Zahl wieder beginnt, wobei wir auch das Argument Erstes_Zeichen der Funktion nutzen:
=MIN(WENNFEHLER(FINDEN({"a";"b";"c";"d";"e";"f";"g";"h";"I";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"v";"w";"x";"y";"z"};B3;C3);999999999))
Diese Formel funktioniert sehr ähnlich wie die vorherige, die für die Suche nach der ersten Zahl verwendet wurde, nur dass wir Buchstaben in unserer Array-Konstante verwenden und daher Zahlen #WERT-Fehler verursachen. Bitte beachten Sie, dass die Suche erst ab der für die erste Zahl festgelegten Position beginnt (dies ist das Argument Erstes_Zeichen) und nicht ab dem Anfang der Zeichenkette.
Vergessen Sie nicht, STRG-Umschaltaste-Eingabe zu drücken, um die obige Formel zu verwenden.
Es bleibt nichts anderes übrig, als dies alles zusammenzufügen.
Zahl aus zwei Texten extrahieren
Sobald wir die Anfangsposition des Zahlenteils und den Anfang des Textteils danach haben, verwenden wir einfach die TEIL-Funktion, um den gewünschten Zahlenteil zu extrahieren:
=TEIL(B3;C3;D3-C3)
Andere Methode
Ohne es näher zu erläutern, können Sie auch die folgende komplexe Formel verwenden, um die Zahl innerhalb einer Zeichenkette zu ermitteln:
=SUMMENPRODUKT(TEIL(0&B3;KGRÖSSTE(INDEX(ISTZAHL(--TEIL(B3;ZEILE(INDIREKT("1:"&LÄNGE(B3)));1))*ZEILE(INDIREKT("1:"&LÄNGE(B3)));0);ZEILE(INDIREKT("1:"&LÄNGE(B3))))+1;1)*10^ZEILE(INDIREKT("1:"&LÄNGE(B3)))/10)
Bitte beachten Sie, dass die obige Formel 0 ergibt, wenn keine Zahl in der Zeichenkette gefunden wurde, und dass führende Nullen weggelassen werden.
Eine Zahl aus einer Zeichenkette suchen und extrahieren – Google Sheets
Die oben gezeigten Beispiele funktionieren in Google Sheets auf die gleiche Weise wie in Excel.
