Generische Formel
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Zusammenfassung
Um Text und Zahlen zu trennen, können Sie eine Formel verwenden, die auf der Funktion FIND, der Funktion MIN und der Funktion LEN mit der Funktion LEFT oder RIGHT basiert, je nachdem, ob Sie den Text oder die Zahl extrahieren möchten. Im gezeigten Beispiel lautet die Formel in C5:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
was 7 zurückgibt, die Position der Nummer 3 in der Zeichenfolge "apples30".
Erläuterung
Überblick
Die Formel sieht komplex aus, aber die Mechanik ist in der Tat recht einfach.
Wie bei den meisten Formeln, die Text teilen oder extrahieren, besteht der Schlüssel darin, die Position des gesuchten Objekts zu ermitteln. Sobald Sie die Position haben, können Sie andere Funktionen verwenden, um das zu extrahieren, was Sie benötigen.
In diesem Fall gehen wir davon aus, dass Zahlen und Text kombiniert werden und die Zahl nach dem Text erscheint. Aus dem Originaltext, der in einer Zelle angezeigt wird, möchten Sie den Text und die Zahlen wie folgt in separate Zellen aufteilen:
| Original | Text | Nummer |
| Äpfel30 | Äpfel | 30 |
| Pfirsiche24 | Pfirsiche | 24 |
| Orangen12 | Orangen | 12 |
| Pfirsiche0 | Pfirsiche | 0 |
Wie oben erwähnt, besteht der Schlüssel in diesem Fall darin, die Startposition der Zahl zu ermitteln, was Sie mit einer Formel wie der folgenden tun können:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Wenn Sie die Position gefunden haben, verwenden Sie Folgendes, um nur den Text zu extrahieren:
=LEFT(A1,position-1)
Und um nur die Zahl zu extrahieren, verwenden Sie:
=RIGHT(A1,LEN(A1)-position+1)
In der ersten Formel oben verwenden wir die FIND-Funktion, um die Startposition der Zahl zu lokalisieren. Für den find_text verwenden wir die Array-Konstante (0,1,2,3,4,5,6,7,8,9). Dadurch führt die FIND-Funktion eine separate Suche für jeden Wert in der Array-Konstante durch. Da die Array-Konstante 10 Zahlen enthält, ist das Ergebnis ein Array mit 10 Werten. Wenn der Originaltext beispielsweise "apples30" lautet, lautet das resultierende Array:
(8,10,11,7,13,14,15,16,17,18)
Jede Zahl in diesem Array repräsentiert die Position eines Elements in der Array-Konstante im Originaltext.
Als nächstes gibt die MIN-Funktion den kleinsten Wert in der Liste zurück, der der Position in der ersten Zahl entspricht, die im Originaltext erscheint. Im Wesentlichen erhält die FIND-Funktion alle Zahlenpositionen und MIN gibt uns die erste Zahlenposition: Beachten Sie, dass 7 der kleinste Wert im Array ist, der der Position der Zahl 3 im Originaltext entspricht.
Möglicherweise wundern Sie sich über die ungerade Konstruktion für inside_text in der Suchfunktion :
B5&"0123456789"
Dieser Teil der Formel verkettet jede mögliche Zahl 0-9 mit dem Originaltext in B5. Leider gibt FIND keine Null zurück, wenn ein Wert nicht gefunden wird. Dies ist nur eine clevere Methode, um Fehler zu vermeiden, die auftreten können, wenn eine Zahl nicht gefunden wird.
Da wir in diesem Beispiel davon ausgehen, dass die Nummer im Originaltext immer an zweiter Stelle steht, funktioniert dies gut, da MIN nur die Rückgabe des kleinsten oder ersten Vorkommens einer Nummer erzwingt. Solange eine Zahl im Originaltext erscheint, wird diese Position zurückgegeben.
Wenn der Originaltext keine Zahlen enthält, wird eine "falsche" Position zurückgegeben, die der Länge des Originaltextes + 1 entspricht. Bei dieser falschen Position gibt die obige LINKE Formel weiterhin den Text zurück und die RECHTE Formel gibt eine leere Zeichenfolge ("") zurück.
