Generische Formel
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Zusammenfassung
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($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Hinweis: Verweise auf B5 und C4 sind gemischte Verweise, damit die Formel quer und nach unten kopiert werden kann.
Erläuterung
Der Kern dieser Formel besteht darin, ein bestimmtes Trennzeichen mit SUBSTITUTE und REPT durch eine große Anzahl von Leerzeichen zu ersetzen und dann mit der MID-Funktion Text zu extrahieren, der sich auf das "n-te Vorkommen" bezieht, und mit der TRIM-Funktion, um den zusätzlichen Leerzeichen zu entfernen.
In diesem Snippet wird das Trennzeichen (Delim) durch eine Anzahl von Leerzeichen ersetzt, die der Gesamtlänge der Zeichenfolge entsprechen:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Dann verwendet die Formel die MID-Funktion, um den n-ten Teilstring zu extrahieren. Der Startpunkt wird mit dem folgenden Code berechnet, wobei N "n-te" darstellt:
(N-1)*LEN(A1)+1
Die insgesamt extrahierten Zeichen entsprechen der Länge der Volltextzeichenfolge. Die TRIM-Funktion entfernt dann alle zusätzlichen Leerzeichen und gibt nur die n-te Zeichenfolge zurück.
Extrahieren Sie nur eine Instanz
Obwohl das Beispiel so eingerichtet ist, dass 5 Teilzeichenfolgen aus dem Text in Spalte B extrahiert werden, können Sie problemlos nur eine Instanz extrahieren. Um beispielsweise nur das 4. Element (Stadt) zu extrahieren, können Sie Folgendes verwenden:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Funktion "Text in Spalten"
Für manuelle, einmalige Konvertierungen verfügt Excel über eine integrierte Funktion namens "Text in Spalten", mit der Text in Zellen mit einem Trennzeichen Ihrer Wahl aufgeteilt werden kann. Sie finden diese Funktion auf der Registerkarte Daten des Menübands im Abschnitt Datenwerkzeuge.
