Excel Formeln – Zahl trennen (irgendwo)

Sie haben in Excel eine Zelle, in der sowohl Text als auch Zahlen vorkommen und Sie möchten nur die Zahlen auslesen? Dafür benötigen Sie wieder eine Formel. Welche das ist, erfahren Sie hier.

Man kennt es mit Sicherheit. Man hat in Excel eine Zelle, in der sowohl Zahlen als auch Text vorkommen, man möchte aber nur die Zahlen geliefert bekommen. So etwas selbst zu programmieren ist viel zu aufwändig. Zum Glück haben wir ja mindestens eine Formel dafür.

Normal:

In Zelle A1 oder irgendeine andere freie schreiben Sie den String bestehend aus Zahlen und Buchstaben völlig durcheinander. In eine andere Zelle, wie zum Beispiel B2 kommt nur die Formel.

=SUMME((TEIL(0&A1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&A1;ZEILE($1:$256);1)*1);ZEILE($1:$256);1);ZEILE($1:$256));1)*1)*10^(ZEILE($1:$256)-1))

Diese Formel ist eine Matrixformel. Wenn Sie versuchen sie mit einem einfachen Druck auf der Enter Taste auszuführen, werden Sie merken, dass Sie damit wenig Erfolg haben werden. Entweder liefert die Formel einen Fehler oder ein falsches Ergebnis. Um das zu umgehen klicken Sie oben in die Leiste, in der die Formel bearbeitet wird. Anschließend drücken Sie STRG+SHIFT+Enter, wodurch die Formel als Matrixformel bestätigt wird. Die Zeile oben wird sich dann in folgendes verändern:

{=SUMME((TEIL(0&A1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&A1;ZEILE($1:$256);1)*1);ZEILE($1:$256);1);ZEILE($1:$256));1)*1)*10^(ZEILE($1:$256)-1))}

Ausgegeben wird hier nun die Ziffern, die in der Zelle A1 stehen. Bei mir stand dort „1ab23c4def5“, deshalb sehe ich in B2 nun „12345“.

(Zur Darstellung in Originalgröße Abbildungen anklicken)

Ohne führende Null:

Was aber passiert nun, wenn man als erste Ziffer eine Null angibt?
Das können Sie selbst bestimmen. Gehen Sie mit ihrer Maus doch einfach ins Feld A1 und bearbeiten dort den String. Schreiben Sie ganz an den Anfang einfach eine „0“. Haben Sie die Formel in B1 nicht verändert, so erscheint dort keine führende „0“. Die Ausgabe sieht in meinem Beispiel also unverändert so aus: „12345“.

Mit führender Null:

Möchten Sie aber, dass eine führende Null erscheint, so müssen sie natürlich die Formel etwas verändern. Löschen Sie dazu das ganze Feld B2. Anschließend geben Sie die neue Formel

=TEXT(SUMME((TEIL(0&A1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&A1;ZEILE($1:$256);1)*1);ZEILE($1:$256);1);ZEILE($1:$256));1)*1)*10^(ZEILE($1:$256)-1));WIEDERHOLEN(0;SUMME(ISTZAHL(TEIL(A1;ZEILE($1:$256);1)*1)*1)))

ein. Da auch diese Formel wieder eine Matrixformel ist, muss sie wieder mit STRG+SHIFT+Enter bestätigt werden. Verändert sieht sie dann so aus:

{=TEXT(SUMME((TEIL(0&A1;KGRÖSSTE(WENN(ISTZAHL(TEIL(0&A1;ZEILE($1:$256);1)*1);ZEILE($1:$256);1);ZEILE($1:$256));1)*1)*10^(ZEILE($1:$256)-1));WIEDERHOLEN(0;SUMME(ISTZAHL(TEIL(A1;ZEILE($1:$256);1)*1)*1)))}

 
Jetzt wissen Sie, wie man aus einem String auch ganz ungeordnete Zahlen auslesen kann. Das Problem mit der führenden Null können Sie auch bewältigen.