Text mit Trennzeichen teilen

Um Text an einem beliebigen Trennzeichen (Komma, Leerzeichen, Pipe usw.) zu teilen, können Sie eine Formel verwenden, die auf den Funktionen TRIM, MID, SUBSTITUTE, REPT und LEN basiert. Im gezeigten Beispiel lautet die Formel in C5:

= TRIM ( MID ( SUBSTITUTE (A1,delim, REPT (' ', LEN (A1))),(N-1)* LEN (A1)+1, LEN (A1)))

Hinweis: Verweise auf B5 und C4 sind gemischte Referenzen damit die Formel quer und nach unten kopiert werden kann.

Wann runden Sie eine Zahl auf?

Der Kern dieser Formel besteht darin, ein bestimmtes Trennzeichen mit SUBSTITUTE und REPT durch eine große Anzahl von Leerzeichen zu ersetzen, dann die MID-Funktion zu verwenden, um Text zu extrahieren, der sich auf das 'n-te Vorkommen' bezieht, und die TRIM-Funktion, um das zusätzliche Leerzeichen zu entfernen.

In diesem Snippet wird das Trennzeichen (delim) durch eine Anzahl von Leerzeichen ersetzt, die der Gesamtlänge der Zeichenfolge entspricht:

 
= TRIM ( MID ( SUBSTITUTE ($B5,'|', REPT (' ', LEN ($B5))),(C-1)* LEN ($B5)+1, LEN ($B5)))

Dann verwendet die Formel die MID-Funktion, um die n-te Teilzeichenfolge zu extrahieren. Der Startpunkt wird mit dem folgenden Code berechnet, wobei N für 'nth' steht:

 
 SUBSTITUTE (A1,delim, REPT (' ', LEN (A1)))

Die Gesamtzahl der extrahierten Zeichen entspricht der Länge der Volltextzeichenfolge. Die TRIM-Funktion entfernt dann alle zusätzlichen Leerzeichen und gibt nur die n-te Zeichenfolge zurück.

Nur eine Instanz extrahieren

Obwohl das Beispiel so eingerichtet ist, dass es 5 Teilzeichenfolgen aus dem Text in Spalte B extrahiert, können Sie problemlos nur 1 Instanz extrahieren. Um beispielsweise nur das vierte Element (Stadt) zu extrahieren, können Sie Folgendes verwenden:

 
(N-1)* LEN (A1)+1

Text-in-Spalten-Funktion

Für manuelle, einmalige Konvertierungen verfügt Excel über eine integrierte Funktion namens ' Text in Spalten ', die Text in Zellen mit einem Trennzeichen Ihrer Wahl aufteilen kann. Sie finden diese Funktion auf der Registerkarte Daten des Menübands im Abschnitt Datentools.