Excel

Text und Zahlen aufteilen

Split Text Numbers

Excel-Formel: Text und Zahlen aufteilenAllgemeine Formel |_+_| Zusammenfassung

Um Text und Zahlen zu trennen, können Sie eine Formel verwenden, die auf dem FINDEN-Funktion , das MIN-Funktion , und der LEN-Funktion mit dem LINKS oder RECHTS Funktion, 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},A1&'0123456789'))

was 7 zurückgibt, die Position der Zahl 3 in der Zeichenfolge 'apples30'.

Erläuterung

Überblick

Die Formel sieht komplex aus, aber die Mechanik ist eigentlich ganz einfach.





Wie bei den meisten Formeln, die Text teilen oder extrahieren, besteht der Schlüssel darin, die Position von der Sache, die Sie suchen. 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 Anfangsposition der Zahl zu finden, was Sie mit einer Formel wie dieser tun können:

So ändern Sie die Werte der horizontalen Achse in Excel Mac
 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},B5&'0123456789'))

Sobald Sie die Position haben, um nur den Text zu extrahieren, verwenden Sie:

 
= MIN ( FIND ({0,1,2,3,4,5,6,7,8,9},A1&'0123456789'))

Und um nur die Zahl zu extrahieren, verwenden Sie:

 
= LEFT (A1,position-1)

In der ersten Formel oben verwenden wir die FIND-Funktion, um die Startposition der Zahl zu finden. Für find_text verwenden wir die Array-Konstante {0,1,2,3,4,5,6,7,8,9}, dies bewirkt, dass die FIND-Funktion eine separate Suche nach jedem Wert in der Array-Konstante durchführt. Da die Array-Konstante 10 Zahlen enthält, ist das Ergebnis ein Array mit 10 Werten. Wenn der Originaltext beispielsweise 'apples30' ist, lautet das resultierende Array:

 
= RIGHT (A1, LEN (A1)-position+1)

Jede Zahl in diesem Array repräsentiert die Position eines Elements in der Arraykonstante innerhalb des Originaltexts.

Als nächstes gibt die MIN-Funktion den kleinsten Wert in der Liste zurück, der der Position in der entspricht erste Zahl das steht im Originaltext. Im Wesentlichen erhält die Funktion FIND 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.

Sie fragen sich vielleicht über die seltsame Konstruktion für innerhalb_text in der Suchfunktion:

wie man Datum und Uhrzeit in Excel hinzufügt
 
{8,10,11,7,13,14,15,16,17,18}

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 also 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 Zahl immer erscheint Sekunde im Originaltext funktioniert es gut, weil MIN nur die kleinsten erzwingt, oder Erste Vorkommen einer zurückzugebenden Zahl. Solange eine Zahl tut im Originaltext erscheinen, wird diese Position zurückgegeben.

Enthält der Originaltext keine Zahlen, wird eine 'falsche' Position gleich der Länge des Originaltextes + 1 zurückgegeben. Bei dieser falschen Position gibt die obige LINKE Formel immer noch den Text zurück und die RECHTE Formel gibt ein zurück leerer String ('').

Autor Dave Bruns


^