Heute möchte ich dir einige Möglichkeiten Präsentieren, wie du eine Zelle auf das vorhanden sein einer Zahl überprüfen und wie du anschließend diese Zahl auslesen kannst.

Bei der Aufbereitung des Artikels, bin ich auf einige interessante Ansätze gestoßen. Mir war vorher gar nicht klar, wie viele Möglichkeiten es gibt, das zeigt wieder das enorme Potential von Excel. Sei also gespannt, was ich dir alles zeige.

Wenn du vielleicht vor einem ähnlichen Problem stehst, musst du zuerst zwei Dinge unterscheiden.

Willst du nur überprüfen, ob eine Zelle eine Zahl enthält oder möchtest du zusätzlich diese Zahl extrahieren?

In diesem Artikel zeige ich dir, wie du aus einer Zelle eine Zahl auslesen kannst aber auch, wie du lediglich überprüfst, ob eine Zelle eine Zahl enthält. Dazu verwende ich einerseits die in Excel implementierten Lösungen aber auch Funktionen bis hin zu VBA Programmen

Wie immer kannst du dir hier die Übungsdatei zum Artikel gratis herunterladen:

Übungsdatei Zahl auslesen

Los geht’s.

So überprüfst du, ob eine Zelle nur Zahlen enthält

Als erstes möchte ich dir zeigen, wie du überprüfst, ob eine Zelle nur Zahlen enthält. Das ist noch die einfachste Variante. Später kümmern wir uns dann darum, wie du eine Zahl auslesen kannst.

Hier nun die 6 Möglichkeiten, die ich gefunden habe, um zu überprüfen, ob in einer Zelle nur Zahlen vorhanden sind.

Mit der Funktion „Inhalte anzeigen“

Diese Funktion kennst du bereits, wenn du hin und wieder Artikel von mir liest. Zuletzt habe ich sie dir in diesem Artikel vorgestellt: 42 Tipps für 2020 zum effizienten Arbeiten in Excel

Rufe das Dialogfeld „Inhalte anzeigen“ auf (Taste F5–>Inhalte…). Wähle nun die Option „Konstanten“ und deaktiviere die Checkbox bei „Text“, „Wahrheitswerte“ und „Fehler“. Wenn du noch mit „Ok“ bestätigst, werden alle Zellen deren Inhalt nur Zahlen sind markiert.

Das ist die schnellste und einfachste Möglichkeit, wie du Zellen welche nur Zahlen enthalten feststellen kannst.

Nun kommen wir zu den etwas aufwändigeren Funktionen.

Mit der Funktion ISTZAHL

Die Funktion ISTZAHL stellt, wie der Name sagt fest, ob die Eingabe oder ein beliebiger Bezug eine Zahl ist.

Das Ergebnis ist WAHR oder FALSCH.

Mit der Funktion ISTTEXT

Diese Funktion prüft, ob es sich bei der Eingabe oder dem Bezug um einen Text handelt. Das kannst du indirekt dazu verwenden, um festzustellen, ob es sich bei der Zelle, um eine mit Zahlen enthält.

Denn wenn kein Text vorhanden ist, handelt es sich in den meisten Fällen um eine Zahl.

ACHTUNG: Die Zelle ist auch kein Text, wenn sie einen Logischen Wert also WAHR oder FALSCH oder Fehlerwerte enthält.

Mit der Funktion TYP

Die Funktion TYP gibt als Ergebnis den Datentyp der jeweiligen Zelle wieder.

Excel unterscheidet dabei 5 Typen.

Wenn du als Ergebnis also eine 1 erhältst, weißt du, dass es sich um eine Zahl handelt.

Indem du die Zelle durch sich selbst dividierst

Eine andere Möglichkeit wie du überprüfen kannst ob eine Zelle eine Zahl enthält, ist die Zelle durch sich selbst zu dividieren. Ist das Ergebnis eine 1, weißt du, die Zelle ist eine Zahl.

ACHTUNG: auch hier musst du wieder die logischen Werte ausschließen. Denn auch wenn du den Wert WAHR durch sich selbst dividierst, erhältst du als Ergebnis eine 1.

Daher musst du die Formel etwas ergänzen, um so das richtige Ergebnis zu erhalten.

Mit der Funktion ZELLE

Als letzte Möglichkeit möchte ich dir die Funktion ZELLE vorstellen.

Mit dieser Funktion kannst du Informationen zu einer bestimmten Zelle ausgeben.

Beispielsweise kannst du so die Adresse oder Breite einer Zelle auslesen oder eben über die Infotypen „Format“ und „Typ“ den Inhalt der Zelle bestimmen.

ACHTUNG: Auch hier musst du aufpassen, da das Format oder die Type nicht immer nur den Zellinhalt, sondern auch dessen Format betrachten oder logische Werte als Zahl behandeln.

In folgendem Bild siehst du alle gezeigten Möglichkeiten und deren Ergebnisse bei unterschiedlichen Zellinhalten.

Viele der hier gezeigten Möglichkeiten entdecken leider nicht alle Variationen korrekt.

Die zuverlässigsten sind die Funktion ISTZAHL und die Funktion TYP daher rate ich dir, dich auf diese zu beschränken.

So überprüfst du ob ein Text eine Zahl enthält

Zellen die nur Zahlen enthalten kannst du nun identifizieren. Wie gehen wir jedoch mit Zellen um, in denen Zahlen und Text kombiniert sind?

Das ist schon etwas schwieriger, eine einzelne Funktion oder ein Befehl ist dafür nicht vorhanden.

Daher nutzen wir den Funktionsumfang von Excel und erstellen unsere eigene Formel dafür.

Ich möchte dir vier unterschiedliche Formeln vorstellen, mit denen du den Zellinhalt auf eine Zahl überprüfen kannst. Ich bin mir sicher es gibt noch andere Formeln aber auf diese vier bin ich bei meinen Nachforschungen gestoßen.

Zelleninhalt auf Zahl überprüfen Variante 1

=WENN(MIN(LÄNGE(WECHSELN(B9;ZEILE($A$1:$A$10)-1;““)))=LÄNGE(B9);“keine Zahl„;“Zahl vorhanden„)

Der Kern der Formel ist die Funktion WECHSELN. Diese ersetzt in einem Text vordefinierte Zeichen durch neue Zeichen.

In unserem Fall werden über die Funktion ZEILE, Zahlen von 1 bis 10 erstellt, die in der Funktion WECHSELN durch keine Zeichen ersetzt werden.

=WECHSELN(B9;ZEILE($A$1:$A$10)-1;““)

Löst du die Formel von innen nach außen auf, erhältst du folgendes Ergebnis.

=WECHSELN(B9;{1;2;3;4;5;6;7;8;9;10}-1;““)

Das –1 in der Formel sorgt dafür, dass alle Zahlen von 0 bis 9 verwendet werden.

=WECHSELN(„4 Katzen;{0;1;2;3;4;5;6;7;8;9};““)

Im Anschluss werden nun alle Zahlen von 0 bis 9 nacheinander durch keine Werte („“) ersetzt.

Das bedeute es überprüft den Text 4 Katzen ob eine 0 vorhanden ist und ersetzt diese durch nichts, anschließend wird der Text auf 1 überprüft und so weiter.

Dadurch erhältst du folgende Matrix.

={„4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“ Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„}

Wie du siehst ist der 5 Wert in unserer Matrix ohne Zahl. Weil der 5 Wert aus der vorigen Matrix die Zahl 4 war und diese durch nichts ersetzt wurde.

Als nächster Schritt wird nun die Länge der Werte in der Matrix ermittelt. Wodurch du folgendes Ergebnis erhältst.

=WENN(MIN(LÄNGE({„4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“ Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„;“4 Katzen„}))=LÄNGE(B9);“keine Zahl„;“Zahl vorhanden„)

=WENN(MIN(LÄNGE({„8„;“8„;“8„;“8„;“7„;“8„;“8„;“8„;“8„;“8„}))=LÄNGE(B9);“keine Zahl„;“Zahl vorhanden„)

Nun wird der Minimale Wert aus dieser Matrix, was der Länge des Textes ohne Zahl entspricht, mit der Länge unseres Ausgangswertes verglichen.

Dazu kurz zur Erklärung.

Wenn eine Zelle keine Zahl enthält, wird auch keine ersetzt und die Länge bleibt gleich. Wenn die Zelle eine Zahl enthält, wird diese durch „keinen Wert“ ersetzt und somit die Länge um mindestens ein Zeichen kürzer, und genau so machen wir die Zellen ausfindig, die Zahlen enthalten.

=WENN(7=LÄNGE(„4 Katzen„);“keine Zahl„;“Zahl vorhanden„)

=WENN(7=8;“keine Zahl„;“Zahl vorhanden„)

Die Prüfung ist FALSCH und wir können mit der WENN Funktion den Wert „Zahl vorhanden“ zurückgeben.

Nimm dir die Zeit und gehe die Formel Schritt für Schritt durch, ich denke dann kommst du sehr schnell hinter den genauen Ablauf und kannst die einzelnen Schritte nachvollziehen. Nutze auch die Taste F9 für die Auflösung der Formel.

Wie du die Taste F9 einsetzen kannst und weitere hilfreiche Tipps zur Arbeit mit Formeln findest du in diesem Artikel: 42 Tipps für 2020 zum effizienten Arbeiten in Excel

Und in diesem Artikel findest du hilfreiche Tipps zur Formelanalyse im Allgemeinen: So gelingt dir jede Formel Analyse

Zelleninhalt auf Zahl überprüfen Variante 2

=WENN(SUMMENPRODUKT(ISTZAHL(TEIL(B13;ZEILE($A$1:$A$20);1)*1)*1);“Zahl„;“keine Zahl„)

Also beginnen wir wieder im Kern der Formel, der TEIL Funktion.

Diese extrahiert einen Teil eines Textes, dabei kannst du mit den Argumenten bestimmen, aus welchem Text du Zeichen extrahieren möchtest, welches das erste Zeichen ist und die Anzahl der Zeichen, die du extrahieren willst.

Der Teil ZEILE erzeugt eine Matrix von 1 bis 20. Es wird also erst das erste Zeichen extrahiert, dann das zweite, dann das dritte und so weiter. So wird eine Matrix aus allen einzelnen Zeichen des jeweiligen Textes erzeugt.

Diese Matrix wird anschließend mit 1 multipliziert, so werden die Zahlen wieder in Zahlen umgewandelt und alle Buchstaben und Sonderzeichen entfernt. Da die Formel TEIL als Ergebnis immer einen Text liefert.

=TEIL(B13;ZEILE($A$1:$A$20);1)

=TEIL(„ABC123„;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1;““)

={„A„;“B„;“C„;“1„;“2„;“3„;““;““;““;““;““;““;““;““;““;““;““;““;““;““}*1

Der nächste Schritt ist, dass die Formel mit der Funktion ISTZAHL, wie wir weiter oben gelernt haben, die Matrix auf Zahlen überprüft. Und erzeugt eine neue Matrix bestehend aus nur WAHR und FALSCH. Jetzt fehlt nur noch eine Funktion, die mit Matrizen umgehen kann und das ist die Funktion SUMMENPRODUKT .

Abschließend wird mit der Funktion SUMMENPRODUKT und der letzten Multiplikation mit 1 die Summe aller zahlen gebildet.

Wie die Formel SUMMENPRODUKT funktioniert und wie du diese einsetzt, habe ich dir in diesem Artikel gezeigt:

An alle Wissbegierigen: Die Excel Funktion SUMMENPRODUKT einfach erklärt

Zelleninhalt auf Zahl überprüfen Variante 3

=WENN(SUMMENPRODUKT(ISTZAHL(FINDEN(ZEILE($A$1:$A$10)-1;B13))*1);“Zahl„;“keine Zahl„)

Die letzte Variante ist sehr ident der zweiten. Hier verwenden wir die Funktion FINDEN, um nach einzelnen Zahlen in unserem Text zu suchen und erzeugen daraus wieder eine Matrix.

Als kleine Aufgabe, ja so was gibt’s hier auch😊, kannst du ja versuchen, hinter die weiteren Rechenschritte der Formel zu kommen. Ich bin mir sicher, das ist für dich nun ein Kinderspiel.

Zelleninhalt auf Zahl überprüfen Variante 4

Und wenn wir schon dabei sind, habe ich noch eine vierte Variante, die du ganz allein lösen darfst, ich bin mir sicher, du kommst dahinter, wie sie funktioniert.

Einziges Manko bei dieser Variante ist, dass die Formel den Wert 0 nicht als Zahl erkennt.

Solltest du Hilfe beim Verständnis der Formel brauchen, schreib mir doch eine Nachricht und ich helfe dir. Du kannst mich hier erreichen

Bevor wir nun zum nächsten Absatz kommen und dazu, wie du einen Text auf bestimmte Zahlen prüfen kannst, hier noch eine Zusammenfassung.

In folgendem Bild wieder alle Varianten und die Ergebnisse für verschiedene Zellinhalte.

Zusätzlich kannst du dir die Übungsdatei wie immer hier herunterladen: Übungsdatei Zahl auslesen

So kannst du feststellen, ob eine Zelle bestimmte Zeichen enthält

Willst du die Werte in einer Zelle auf bestimmte Zeichen überprüfen, so ist das ganz einfach.

Die Funktion SUCHEN ermöglicht es dir, eine bestimmte Zeichenfolge innerhalb einer anderen zu finden.

Alle zuvor gezeigten Möglichkeiten kannst du selbstverständlich auch in der bedingten Formatierung verwenden, um Zellen mit Zahlen zu kennzeichnen.

Wenn du mehr zur bedingten Formatierung erfahren möchtest, ist der Artikel interessant für dich: Wie du mit der bedingten Formatierung wirklich Zeit sparen kannst

So kannst du aus einer Zelle eine Zahl auslesen

Wie du eine Zelle auf das vorhanden sein einer Zahl hin überprüfen kannst, haben wir denke ich ausführlich behandelt.

Als nächstes möchten wir diese Zahlen nun aus der Zelle extrahieren und in eine neue Zelle überführen, also die Zahl auslesen. Die meisten der hierzu verwendeten Funktionen kennst du bereits.

Das sieht vielleicht im ersten Moment etwas schwierig aus, ist es aber nicht, wie du gleich sehen wirst.

Wenn wir eine Zahl auslesen, möchte ich gerne drei Varianten unterscheiden.

Erstens eine zusammenhängende Zahl auslesen, dann einzelne Zahlen auslesen und zusammenhängen, und dann Zahlen auslesen und in unterschiedliche Zellen aufteilen.

So kannst du eine zusammenhängende Zahl auslesen

Ich habe dir sieben verschiedene Varianten aufgelistet, mit denen du Zahlen aus einer Zelle auslesen kannst.

Dabei solltest du beachten, dass alle Ihre Stärke und Schwächen haben.

Zahl auslesen Variante 1, 2 und 3

=VERWEIS(9^9;1*TEIL(B12&“#„;SPALTE(12:12);6))

=VERWEIS(9^9;1*TEIL(B12&“#„;SPALTE(12:12);1))

=VERWEIS(9^9;1*TEIL(B12&“#„;SPALTE(12:12);(ANZAHL(TEIL(B6;ZEILE($A$1:$A$10)-1;1)*1))))

Die ersten drei Formeln können eine Zahl auslesen, die mit der Anzahl der definierten Stellen übereinstimmt, aber auch nur jene Zahlen, die zusammenhängend sind, sollten zwei oder mehr Zahlen also durch einen Buchstaben oder ein anders Zeichen getrennt sein, liest die Formel nur eine Zahl aus und nicht alle.

Ich habe dir auch eine Variante erstellt, die die Anzahl der Stellen automatisch bestimmt, diese Variante ist jedoch auch fehlerhaft bei mehreren nicht zusammenhängenden Zahlen.

Alle drei Varianten suchen immer die größte Zahl aus der Zelle, wenn mehrere vorhanden sind.

Zahl auslesen Variante 4 und 5

Die nächsten beiden Varianten lesen mit Textfunktionen eine Zahl aus, die ganz links oder ganz rechts in der Zelle steht.

=WERT(LINKS(B12;SUMME(–ISTZAHL(–LINKS(B12;SPALTE(1:1))))))

=WERT(RECHTS(B12;SUMME(–ISTZAHL(–RECHTS(B12;SPALTE(1:1))))))

Der Nachteil hierbei ist auch, dass nur zusammenhängende Zahlen ausgelesen werden.

Zahl auslesen Variante 6

Die vorherigen Varianten geben immer die größte Zahl wieder.

Daher möchte ich dir noch eine Möglichkeit zeigen, wie du die kleinste Zahl aus einer Zelle extrahieren kannst.

=INDEX(1*TEIL(WECHSELN(B12;“ „;“#„)&“#„;SPALTE(12:12);1);;VERGLEICH(1;1*TEIL(WECHSELN(B12;“ „;“#„)&“#„;SPALTE(12:12);1);-1))

Leider habe ich bei der Variante in Zeile 12 nicht herausgefunden, wieso die Formel nicht funktioniert.

Zahl auslesen Variante 7

Die letzte Variante liest alle Zeichen von der ersten Zahl bis zur letzten aus. Also auch nur zu verwenden, wenn die Zahlen zusammenhängen.

=TEIL(LINKS(B5;MAX(ISTZAHL(TEIL(B5;SPALTE(1:1);1)*1)*SPALTE(1:1)));VERGLEICH(1;ISTZAHL(TEIL(B5&0;SPALTE(1:1);1)*1)*1;0);LÄNGE(B5))

Ich habe hier bewusst die Erklärungen der einzelnen Formel weggelassen, da ich überzeugt davon bin, dass du am meisten lernst, wenn du die Formeln selbst analysierst.

Wie du dabei am besten vorgehst und worauf du achten musst, habe ich dir in diesem Artikel beschrieben: So analysierst du Formeln schnell und einfach

Wenn du es möchtest, reiche ich die Erklärung der Funktion natürlich gerne nach. Schreib mir dazu einfach kurz eine Nachricht. Du kannst mich hier erreichen.

So kannst du alle Zahlen auslesen und verkettet ausgeben

Jetzt habe ich dir viele Wege gezeigt, wie es nicht geht eine getrennte Zahl auszulesen.

Daher zeige ich dir jetzt wie es geht.

Ich habe dir wieder mehrere Varianten aufbereitet wie du einzelne Zahlen aus einer Zelle extrahieren kannst.

Dier ersten beiden Möglichkeiten sind mittels eigens erstellter Funktion in VBA.

Ich weiß, dass ist nicht für jeden etwas, aber du brauchst den Code nur in deine Arbeitsmappe einfügen und dann kannst du die Funktionen nutzen.

Wie das auch ohne Programmier-Kenntnisse geht, habe ich dir hier gezeigt: VBA und MAKROS nutzen auch ohne Programmierkenntnisse

Diese beiden Codes habe nicht ich entwickelt, diese stammen aus diesem und diesem Artikel.

Die nächste Möglichkeit stammt allerdings wieder von mir und kommt ganz ohne VBA aus.

Leider ist die Funktion TEXTKETTE erst ab Excel 2019 verfügbar.

=TEXTKETTE(WENNFEHLER(TEIL(B12;ZEILE($A$1:$A$20);1)*ISTZAHL(TEIL(B12;ZEILE($A$1:$A$20);1)*1);““))

Diese Formel liest mit der Funktion TEIL alle Zahlen aus einer Zelle aus und erstellt daraus eine Matrix. Diese Matrix wird dann mittels TEXTKETTE zu einem Text verknüpft.

Die Funktion Zeile bestimmt dabei welche Stelle des Textes extrahiert wird. Hast du also einen Text der 100 Zeichen lang ist, musst du auch die Zeile bis 100 erweitern.

=TEXTKETTE(WENNFEHLER(TEIL(B12;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20};1)*ISTZAHL(TEIL(B12;{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20};1)*1);““))

Danach wird jedes Zeichen aus dem Text einzeln ausgelesen.

=TEXTKETTE(WENNFEHLER({„A„;“$„;“B„;“2„;“$„;“5„;“R„;“4„;““;““;““;““;““;““;““;““;““;““;““;““}*ISTZAHL({„A„;“$„;“B„;“2„;“$„;“5„;“R„;“4„;““;““;““;““;““;““;““;““;““;““;““;““}*1);““))

Nun wird die Matrix mit der Multiplikation mit *1 in lauter Zahlen umgewandelt und mit der Funktion ISTZAHL jene Werte der Matrix, die eine Zahl sind, ermittelt.

=TEXTKETTE(WENNFEHLER({„A„;“$„;“B„;“2„;“$„;“5„;“R„;“4„;““;““;““;““;““;““;““;““;““;““;““;““}*{„FALSCH„;“FALSCH„;“FALSCH„;“WAHR„;“FALSCH„;“WAHR„;“FALSCH„;“WAHR„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„;“FALSCH„};““))

Im nächsten Schritt werden die beiden übrigen Matrizen miteinander multipliziert und so (durch die Matrix mit WAHR und FALSCH) nur die Zahlen in der übrigen Matrix behalten. Mit der Funktion WENNFEHLER werden zusätzlich alle Fehlerwerte entfernt und so entsteht eine Matrix nur aus Zahlen.

=TEXTKETTE({„“;““;““;“2„;““;“5„;““;“4„;““;““;““;““;““;““;““;““;““;““;““;““})

Und das Ergebnis der Verkettung ist folgendes.

=254

Wie du siehst, ist nicht immer ein Makro erforderlich oder ein VBA Code, um zum Ergebnis zu kommen, oft reicht ein wenig Einfallsreichtum und Durchhaltevermögen völlig aus und du findest einen Weg.

Den VBA Code findest du in der Übungsdatei, welche du dir hier herunterladen kannst:

Übungsdatei Zahl auslesen

So kannst du Zahlen auslesen und aufteilen

Abschließend habe ich noch zwei Formeln für dich.

Diese kannst du nutzen, damit du einzelne Zahlen aus einer Zelle auslesen kannst und jede Zahl wird in eine separate Zelle extrahiert.

Die erste Formel untersucht die Zelle nach der Zahl, die mit der Funktion ZEILE definiert ist.

=SUCHEN(ZEILE(B5)-5;$B$5;1)

Mit der zweiten Formel kannst du dann die Zahl auslesen. Mit der Unterstützung der ersten Formel, weiß die zweite auch von welcher Position des Textes sie die Zahl auslesen soll.

=TEIL($B$5;D5;1)

Der Nachteil ist allerdings, dass es nur bei Unikaten funktioniert. Das bedeutet, dass du beispielsweise die Zahl 5 nur einmal auslesen kannst.

Außerdem brauchst du einen größeren Bereich indem du die Formeln einfügen kannst.

Fazit

Du siehst es gibt eine ganze Menge an Möglichkeiten, wie du aus einer Zelle eine Zahl auslesen und Zellen auf Zahlen überprüfen kannst.

Mit den in diesem Artikel angeführten Formeln und Möglichkeiten solltest du deine Probleme ohne weiteres lösen können.

Da jede Formel ihre ganz speziellen Einsatzgebiete hat, solltest du genau darüber nachdenken welche du für welchen Fall einsetzt.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Vergiss nicht, solltest du spezifische Probleme mit Excel haben, schreib mir eine Nachricht und wir lösen sie gemeinsam. Hier kannst du mich kontaktieren.