Excel-TippDaten mit unterschiedlichen Trennzeichen in Spalten aufteilen

In Power Query kommt es oft vor, dass eine Spalte Werte enthält, die mit unterschiedlichen Trennzeichen voneinander getrennt sind. Ein typisches Beispiel ist eine exportierte CSV-Datei aus einem System, in der Namen, Kategorien oder Artikelnummern mit verschiedenen Trennzeichen gespeichert sind.

Diese uneinheitliche Struktur kann die Weiterverarbeitung in Excel erschweren. Die Herausforderung besteht darin, diese Werte in mehrere Spalten aufzuteilen, auch wenn die Trennzeichen nicht einheitlich sind.

Beispiel: Liste mit mehreren Artikeldaten in einer Zelle – durch unterschiedliche Zeichen getrennt

Im folgenden Beispiel gibt es eine CSV-Datei mit Artikeldaten, in der die einzelnen Attribute mit unterschiedlichen Trennzeichen (Semikolon, Komma, Bindestrich, Doppelpunkt) getrennt sind.

Diese uneinheitliche Struktur erfordert eine clevere Lösung in Power Query, um die Daten so aufzubereiten, dass sie in Excel weiterverarbeitet werden können.

Liste aus Excel in Power Query importieren

Importieren Sie als Erstes die Daten in Power Query, indem Sie eine Zelle in der Liste markieren und dann im Menüband die Befehlsfolge Registerkarte Daten > Befehlsgruppe Daten abrufen und transformieren > Befehl Aus Tabelle/Bereich aktivieren.

Um die Daten in Power Query zu laden, wird zunächst der Bereich in eine intelligente Tabelle umgewandelt. Es öffnet sich das Dialogfeld Tabelle erstellen. Der Bereich wird automatisch erkannt.

Da im Beispiel die erste Zeile eine Beschriftung enthält, muss das Kontrollkästchen Tabelle hat Überschriften aktiviert sein.

Bestätigen Sie die Einstellungen, indem Sie das Dialogfeld durch Klick auf OK schließen.

Daraufhin bekommen Sie im Power-Query-Editor die importierten Daten angezeigt.

Unterschiedliche Trennzeichen suchen und ersetzen

Nun müssen Sie die Trennzeichen identifizieren und die Daten separieren. Im Beispiel erkennen Sie die Trennzeichen Semikolon, Komma, Doppelpunkt und Bindestrich.

Alle unterschiedlichen Trennzeichen müssen durch ein eindeutiges Zeichen ersetzt werden. Dieses Zeichen darf nicht in den bereits vorhandenen Daten vorkommen. Wir wählen daher das Zeichen % als Trennzeichen.

Klicken Sie nun als Nächstes mit der rechten Maustaste auf den Spaltennamen und wählen Sie im Kontextmenü den Eintrag Werte ersetzen aus.

Es öffnet sich das Dialogfeld Werte ersetzen. Als Erstes ersetzen Sie das Semikolon (;) durch ein %-Zeichen. Erfassen Sie daher bei Zu suchender Wert ; und bei Ersetzen durch %.

Bestätigen Sie Ihre Einstellung, indem Sie auf OK klicken.

Daraufhin werden alle Semikolons durch ein % in der Liste ersetzt.

Wiederholen Sie diesen Vorgang jetzt für die noch verbliebenen Trennzeichen Komma, Doppelpunkt und Bindestrich.

Am Ende sind alle unterschiedlichen Trennzeichen durch das %-Zeichen ersetzt.

Spalte nach Trennzeichen in mehrere Spalten aufteilen

Haben Sie alle Trennzeichen durch ein % ersetzt, dann klicken Sie wieder mit der rechten Maustaste auf die Spaltenüberschrift und wählen Sie im Kontextmenü den Eintrag Spalte teilen > Nach Trennzeichen aus.

Es öffnet sich das Dialogfeld Spalte nach Trennzeichen teilen. Wählen Sie hier bei Trennzeichen eingeben oder auswählen den Eintrag Benutzerdefiniert aus und erfassen Sie darunter das Zeichen %.

Bei Aufteilen wählen Sie die Option Bei jedem Vorkommen des Trennzeichens aus und bestätigen Sie Ihre Einstellungen, indem Sie auf OK klicken.

Die Daten werden daraufhin entsprechend auf neue Spalten verteilt.

Sie können die Daten jetzt an Excel zurückgeben, indem Sie im Menüband auf die Befehlsfolge Datei > Schließen & laden klicken.

Hinweis:

Auch in Excel selbst können Zeichen durch ein anderes Zeichen ersetzt und Spalten mit TEXTTEILEN() aufgeteilt werden. Da sich die Ausgangsliste aber mit jedem Import einer neuen CSV-Datei ändern kann, müssten Sie mehrere Funktionen und Abfragen verknüpfen und jedes Mal prüfen.

Mit Power Query genügt ein einfaches Daten aktualisieren nach dem Import.