Nur zahlen aus text (string) extrahieren
In Excel können Sie Formeln verwenden, um die Zahl aus einem Wert zu extrahieren, indem Sie Text und Zahlen kombinieren. Im folgenden Beispiel haben wir eine Formel verwendet, um 1984 aus dem Wert „TPS1984“ zu ermitteln.
Formel zum Extrahieren von Zahlen aus Text
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))In diesem Tutorial werden wir diese Formel im Detail verstehen. Und um es zu verstehen, müssen wir es in mehrere Teile unterteilen:
- Zuerst haben wir die LEN-Funktion, um die Anzahl der Zeichen im Wert zu ermitteln.
- Danach verwendet INDIRECT eine Zellreferenz unter Verwendung der 1 und 7 (vom LEN zurückgegeben).
- Dann verwendet die ROW-Funktion INDIRECT und gibt ein Array von Zahlen zurück, die mit 1 beginnen und mit 7 enden.
- Jetzt verwendet MID dieses Array und gibt ein neues mit den Werten der Zelle zurück, auf die Sie verwiesen haben.
- Als nächstes haben wir eine einfache Multiplikationsformel, um das Array mit 1 zu multiplizieren. Mit dieser einfachen Multiplikation erhalten Sie ein neues Array, in dem Sie #WERT haben! Der Fehler für Textwerte und Zahlen bleibt bestehen.
- Von hier aus konvertiert die IFERROR-Funktion die Fehlerwerte in leere Werte.
- Und am Ende kombiniert TEXTJOIN die Werte, und das Ergebnis enthält nur Zahlen.
Die obige Formel funktioniert nur für Excel 2019 und höher. Dies liegt daran, dass wir TEXTJOIN verwenden, das in früheren Versionen nicht verfügbar war.
Wenn Sie einen Wert für einen Teil der Formel wünschen, wählen Sie diesen Teil aus und drücken Sie die Tastenkombination F9.
TEXTJOIN ist eine flüchtige Funktion, die ihren Wert ändert, wenn Sie eine Zelle im Arbeitsblatt aktualisieren. Dadurch kann Ihre Arbeitsmappe etwas langsam werden.
Was aber, wenn ich eine frühere Version verwende (2007, 2010, 2013, 2016)?
Wenn Sie frühere Versionen verwenden, müssen Sie eine andere Formel verwenden. Eine Formel wie diese:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"") =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)Die beiden obigen Formeln funktionieren gut mit verschiedenen Arten von Werten. Ich habe es folgendermaßen getestet.
In Zelle A1 oben haben wir einen komplexen Wert bestehend aus Text, Zahlen und Symbolen und im Ergebnis haben wir nur Zahlen.