Wie kann man Daten anhand eines Trennzeichens aufteilen und in Excel in einer Spalte anordnen?
Beim Arbeiten mit Excel enthalten Zellen häufig mehrere Werte, die durch Kommas, Semikolons oder andere Trennzeichen voneinander getrennt sind. Wenn Sie einen Bereich solcher Zellen anhand eines bestimmten Trennzeichens aufteilen und alle resultierenden Werte in einer einzigen Spalte zusammenführen möchten – wie im folgenden Screenshot veranschaulicht –, stehen Ihnen effiziente Methoden zur Verfügung, um Ihre Daten optimal zu organisieren und zu analysieren.
Daten aufteilen nach Trennzeichen aufteilen und in eine Spalte umwandeln
Daten aufteilen nach Trennzeichen aufteilen und in eine Spalte umwandeln mithilfe einer Formel (Excel 365)
In Excel 365 können wir diese Umwandlung dynamisch mit modernen Funktionen wie TEXTAUFTEILEN und TEXTVERKETTEN realisieren.
Klicken Sie auf die Zelle, in der das Ergebnis erscheinen soll, geben Sie die folgende Formel ein und drücken Sie die Eingabetaste. Die Zellen mit den kommagetrennten Textzeichenfolgen werden sofort in eine Liste umgewandelt. Siehe Screenshot:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A2:A5), ", "))
- TEXTVERKETTEN(","; WAHR; A2:A5): Verbindet alle Werte in A2:A5 zu einer einzigen Textzeichenfolge, getrennt durch Kommas.
- TEXTAUFTEILEN(...; ","): Trennt den verketteten Text an jeder Stelle, an der ein Komma steht.
- MTRANS(...): Wandelt die resultierende Zeile mit Werten in eine vertikale Spalte um.
Daten aufteilen nach Trennzeichen aufteilen und in eine Spalte umwandeln mit Kutools für Excel
Für Anwender, die mit älteren Excel-Versionen (2016/2019 usw.) arbeiten und die TEXTAUFTEILEN-Funktion nicht nutzen können, bietet das Kutools für Excel-Feature In mehrere Zeilen aufteilen eine ideale Lösung. Mit dieser Funktion teilen Sie Zellinhalte im Handumdrehen anhand eines bestimmten Trennzeichens in eine einzelne Spalte auf – und sparen so wertvolle Zeit und Aufwand.
Nach der Installation von Kutools für Excelführen Sie bitte die folgenden Schritte aus:
- Klicken Sie auf Kutools>Vereinigen/Aufteilen>In mehrere Zeilen aufteilen, siehe Screenshot:
- Geben Sie im Dialogfeld In mehrere Zeilen aufteilendie folgenden Aktionen an:
- Wählen Sie die Zellen aus, die Sie aufteilen möchten;
- Wählen Sie im Abschnitt Trennzeichen die Option Sonstige und geben Sie Ihr Trennzeichen ein (z. B. ein Komma und ein Leerzeichen), um festzulegen, wie der Zellinhalt aufgeteilt werden soll.
- Klicken Sie abschließend auf die Schaltfläche OK.
Ergebnis: Die ausgewählten Zellen wurden mithilfe der angegebenen Trennzeichen in eine Spalte aufgeteilt.
Daten aufteilen nach Trennzeichen aufteilen und in eine Spalte umwandeln mit VBA-Code
Im Folgenden zeigen wir Ihnen Schritt für Schritt, wie Sie mithilfe von VBA Zellinhalte anhand eines Trennzeichens aufteilen und die Ergebnisse in einer Spalte anordnen.
1. Drücken Sie die Tasten Alt + F11, um das Fenster Microsoft Visual Basic für Applikationen anzuzeigen.
2. Klicken Sie auf Einfügen > Modul und fügen Sie den folgenden Code in das neue Modul-Fenster ein.
VBA: Aufteilen und Transponieren
Sub Vertical()
'Updateby Extendoffice
Dim i As Long, strTxt As String
Dim startP As Range
Dim xRg As Range, yRg As Range
On Error Resume Next
Set xRg = Application.InputBox _
(Prompt:="Range Selection...", _
Title:="Kutools For Excel", Type:=8)
i = 1
Application.ScreenUpdating = False
For Each yRg In xRg
If i = 1 Then
strTxt = yRg.Text
i = 2
Else
strTxt = strTxt & "," & yRg.Text
End If
Next
Application.ScreenUpdating = True
Set startP = Application.InputBox _
(Prompt:="paste range...", _
Title:="Kutools For Excel", Type:=8)
ary = Split(strTxt, ",")
i = 1
Application.ScreenUpdating = False
For Each a In ary
startP(i, 1).Value = a
i = i + 1
Next a
Application.ScreenUpdating = True
End Sub3. Drücken Sie die Taste F5, um den Code auszuführen. Es erscheint ein Dialogfeld, in dem Sie einen aufzuteilenden Bereich auswählen und transponieren können.
4. Klicken Sie auf OK. Es erscheint ein weiteres Dialogfeld, in dem Sie einen Speicherort für die Daten auswählen können.
5. Klicken Sie auf OK, und Ihre Auswahl wird durch Kommas getrennt und transponiert.
🔚 Fazit
Alle Methoden lösen das Kernproblem der Aufteilung tief gestaffelter Daten effektiv – die optimale Wahl hängt dabei von Ihrem technischen Know-how und Ihren spezifischen Anforderungen ab.
- Für die meisten Excel-365-Nutzer bietet der Formelansatz die optimale Balance aus Leistungsfähigkeit und Wartbarkeit.
- Für häufige und komplexe Umwandlungen ist Kutools ideal – selbst für Anwender ohne Programmierkenntnisse.
- Für maßgeschneiderte, automatisierte Lösungen in Unternehmensumgebungen bietet VBA maximale Kontrolle.
Wählen Sie die Methode, die am besten zu Ihren individuellen Anforderungen passt. Wenn Sie noch mehr Excel-Tipps und -Tricks entdecken möchten,bietet unsere Website Tausende von Anleitungen, mit denen Sie Excel meistern.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins