Laden Sie die Beispielarbeitsmappe herunter
Dieses Tutorial wird demonstrieren Sie, wie Sie eine Zahl in einer Textzeichenfolge in Excel und Google Sheets finden und extrahieren.
Zahlen aus String suchen und extrahieren
Manchmal können Ihre Daten Zahlen und Text enthalten 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 aufzuteilen . Wenn sich die Zahlen jedoch innerhalb der Zeichenfolge befinden, d. h. zwischen zwei Textzeichenfolgen, müssen Sie eine viel kompliziertere Formel verwenden (siehe unten).
TEXTJOIN - Zahlen in Excel 2016+ extrahieren
In Excel 2016 wurde die Funktion TEXTJOIN eingeführt, die die Zahlen von überall aus der Textzeichenfolge extrahieren kann. Hier ist die Formel:
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)*1),"")) |
Sehen wir uns an, wie diese Formel funktioniert.
Die Funktionen ROW, INDIRECT und LEN geben ein Array von Zahlen zurück, die jedem Zeichen in Ihrer alphanumerischen Zeichenfolge entsprechen. In unserem Fall hat „Monday01Day“ 11 Zeichen, sodass die ROW-Funktion dann das Array {1;2;3;4;5;6;7;8;9;10;11} zurückgibt.
| 1 | =TEXTJOIN("",TRUE,IFERROR((MID(B3, {1;2;3;4;5;6;7;8;9;10;11},1)*1),"")) |
Als nächstes extrahiert die MID-Funktion jedes Zeichen aus der Textzeichenfolge und erstellt ein Array, das Ihre ursprüngliche Zeichenfolge enthält:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({"M";"o";"n";"d";"a";"y";"0";"1";"D";"a ";"y"}*1),"")) |
Das Multiplizieren jedes Wertes im Array mit 1 erzeugt ein Array mit Fehlern Wenn das Array-Zeichen Text war:
| 1 | =TEXTJOIN("",TRUE,IFERROR(({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0;1;#VALUE!;#VALUE!;#VALUE !}),"")) |
Als nächstes entfernt die IFERROR-Funktion die Fehlerwerte:
| 1 | =TEXTJOIN("",TRUE,{"";"";"";"";"";"";0;1;"";"";""}) |
Es bleibt nur die TEXTJOIN-Funktion übrig, die die restlichen Zahlen zusammenfügt.
Beachten Sie, dass die Formel Ihnen alle numerischen Zeichen zusammen aus Ihrer Zeichenfolge liefert. Wenn Ihre alphanumerische Zeichenfolge beispielsweise Monday01Day01 ist, erhalten Sie als Ergebnis 0101.
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 Funktion FIND zusammen mit den Funktionen IFERROR und MIN verwenden, um sowohl die erste Stelle des Zahlenteils als auch die erste Stelle des Textteils nach der Zahl zu bestimmen. Dann extrahieren wir einfach den Zahlenteil mit der MID-Funktion.
| 1 | =MID(B3,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999)),MIN(IFERROR(SEARCH({"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(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B3),999999999))),999999999))-MIN(IFERROR(FIND( {0,1,2,3,4,5,6,7,8,9},B3),999999999))) |
Hinweis: Dies ist eine Array-Formel, Sie müssen die Formel eingeben, indem Sie STRG + UMSCHALT + EINGABETASTE drücken, anstatt nur EINGABETASTE.
Sehen wir uns Schritt für Schritt an, wie diese Formel funktioniert.
Faustzahl finden
Wir können die FIND-Funktion verwenden, um die Anfangsposition der Zahl zu finden.
| 1 | = MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B3),999999999)) |
Für das find_text-Argument der FIND-Funktion verwenden wir die Array-Konstante {0,1,2,3,4,5,6,7,8,9}, wodurch die FIND-Funktion eine separate Suche nach jedem Wert in . durchführt die Array-Konstante.
Das Argument innerhalb_text der Funktion FIND ist in unserem Fall Monday01Day, wobei 1 an Position 8 und 0 an Position 7 zu finden ist. Unser Ergebnisarray ist also: {8,#VALUE,#VALUE,#VALUE, #WERT,#WERT,#WERT, #WERT, #WERT,7}.
Mit der IFERROR-Funktion ersetzen wir die #VALUE-Fehler durch 999999999. Dann suchen wir einfach nach dem Minimum innerhalb dieses Arrays und erhalten somit die Stelle der ersten Zahl (7).
Bitte beachten Sie, dass die obige Formel eine Array-Formel ist, Sie müssen Strg + Umschalt + Eingabe drücken, um sie auszulösen.
Erstes Textzeichen nach Zahl finden
Ähnlich wie beim Auffinden der ersten Zahl innerhalb des Strings verwenden wir die FIND-Funktion, um zu bestimmen, wo der Text nach der Zahl wieder beginnt, wobei wir auch das start_num-Argument der Funktion sinnvoll nutzen.
| 1 | =MIN(IFERROR(FIND({"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 zum Auffinden der ersten Zahl verwendet wurde, nur verwenden wir Buchstaben in unserer Array-Konstante und daher verursachen Zahlen #VALUE-Fehler. Bitte beachten Sie, dass wir die Suche erst nach der für die erste Zahl ermittelten Position beginnen (dies ist das Argument start_num) und nicht am Anfang des Strings.
Vergessen Sie nicht, Strg + Umschalt + Eingabetaste zu drücken, um die obige Formel zu verwenden.
Es bleibt nichts übrig, als das alles zusammenzufügen.
Zahlen aus zwei Texten extrahieren
Sobald wir die Startposition des Zahlenteils und danach den Anfang des Textteils haben, verwenden wir einfach die MID-Funktion, um den gewünschten Zahlenteil zu extrahieren.
| 1 | =MITTEL(B3,C3,D3-C3) |
Andere Methode
Ohne es näher zu erklären, können Sie auch die folgende komplexe Formel verwenden, um die Zahl aus einem String zu erhalten.
| 1 | =SUMMENPRODUKT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))*ROW(INDIRECT("1:"&LEN(B3) )),0),ROW(INDIRECT("1:"&LEN(B3))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B3)))/10) |
Bitte beachten Sie, dass die obige Formel 0 ergibt, wenn keine Zahl in der Zeichenfolge gefunden wird und führende Nullen weggelassen werden.
Suchen und Extrahieren von Zahlen aus String in Text in Google Sheets
Die oben gezeigten Beispiele funktionieren in Google Tabellen genauso wie in Excel.
