Generische Formel
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Zusammenfassung
Um eine Zahl von einem Einheitswert zu trennen, können Sie eine Formel verwenden, die auf mehreren Funktionen basiert: MAX, ISNUMBER, VALUE und MID. Im gezeigten Beispiel lautet die Formel in C5:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Hinweis: Dies ist eine experimentelle Formel, die eine fest codierte Array-Konstante verwendet, die hier als Referenz und Kommentar angegeben ist. Nur gelegentlich getestet, seien Sie also vorsichtig, wenn Sie es verwenden oder anpassen.
Erläuterung
Manchmal stoßen Sie auf Daten, die Einheiten direkt mit Zahlen mischen (z. B. 8 km, 12 V, 7,5 Stunden). Leider behandelt Excel die Zahlen in diesem Format als Text, und Sie können mit solchen Werten keine mathematischen Operationen ausführen.
Um eine Zahl von einem Einheitswert zu trennen, müssen Sie die Position der letzten Zahl bestimmen. Wenn Sie dieser Position 1 hinzufügen, haben Sie den Anfang des Einheitentextes. Diese Formel verwendet dieses Konzept, um herauszufinden, wo die Maßeinheit beginnt.
Im gezeigten Beispiel lautet die Formel in C5:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Diese Formel verwendet die MID-Funktion, um die ersten 9 Werte in B5 zeichenweise zu extrahieren. Das Ergebnis ist ein Array wie folgt:
("8","0","v","","","","","","")
Wir verwenden dann die VALUE-Funktion, um Zahlen im Textformat in tatsächliche Zahlen umzuwandeln. Das Ergebnis ist:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Wir führen dieses Array über ISNUMBER aus, um Folgendes zu erhalten:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Dann multiplizieren Sie dieses Mal ein anderes Array mit 9 Zahlen, um Folgendes zu erhalten:
(1,2,0,0,0,0,0,0,0)
Dann verwenden wir MAX, um den größten Wert zu erhalten, der die Position der "letzten Zahl" ist.
Schließlich addieren wir 1 zur Position, um die Position "Einheitsstart" zu erhalten.
Schließlich verwenden wir diese Position mit den Standardfunktionen LINKS und RECHTS, um die Zahlen von den Einheiten zu trennen:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Beachten Sie, dass die fest codierte Zahlenarray-Konstante der Einfachheit halber ein Hack ist und nur Rohwerte mit einer Länge von bis zu 9 Zeichen verarbeitet.
