Excel: So teilen Sie durch Trennzeichen getrennten Text in Zeilen auf
Beim Arbeiten mit Daten in Excel stoßen Anwender häufig auf Situationen, in denen eine einzelne Zelle mehrere Informationen enthält, die durch Trennzeichen wie Kommas, Semikolons oder Leerzeichen voneinander getrennt sind – beispielsweise eine Liste mit Tags, Namen oder Artikelnummern, die alle in einer Zelle zusammengefasst sind. Um aussagekräftige Analysen durchzuführen, Daten präzise zu filtern oder sie optimal für die spätere Verwendung aufzubereiten, müssen diese durch Trennzeichen getrennten Werte in einzelne Zeilen aufgeteilt werden. Das gelungene Aufteilen der Werte in Zeilen vereinfacht die anschließende Datenverarbeitung, -visualisierung und -berichterstattung erheblich und steigert deren Effektivität spürbar. Excel bietet hierfür verschiedene Lösungen, die unterschiedliche Anwendungsszenarien, Excel-Versionen und Benutzerkenntnisse berücksichtigen. Im Folgenden erhalten Sie eine umfassende Anleitung mit mehreren praxiserprobten Ansätzen – von Formeln über integrierte Werkzeuge bis hin zu Add-Ins –, um durch Trennzeichen getrennten Text in Excel effizient in einzelne Zeilen zu zerlegen.
Durch Trennzeichen getrennten Text mithilfe von VBA in Zeilen aufteilen
Durch Trennzeichen getrennten Text in Zeilen aufteilen mit Kutools für Excel
Durch Trennzeichen getrennten Text in Zeilen aufteilen mit POWER QUERY
Wenn Sie häufig durch Trennzeichen getrennten Text in Zeilen aufteilen müssen und mit Makros vertraut sind, übernimmt VBA diese Aufgabe automatisch für Sie. Dieser Ansatz funktioniert in allen Excel-Versionen und eignet sich hervorragend zur Automatisierung wiederkehrender Aufteilungsaufgaben. Beachten Sie jedoch: Das Ausführen eines VBA-Makros verändert Ihre Originaldaten. Erstellen Sie daher unbedingt vorab eine Sicherungskopie – besonders bei Arbeitsmappen mit wichtigen oder unwiederbringlichen Informationen.
1. Drücken Sie „Alt + F11“, um das Fenster des Microsoft Visual Basic for Applications-Editors zu öffnen.
2. Klicken Sie im VBA-Editor auf „Einfügen“ im Menü und wählen Sie „Modul“, um ein neues leeres Modul zu erstellen.
3. Kopieren Sie den folgenden Code, und fügen Sie ihn in das leere Modul ein:
VBA: Durch Trennzeichen getrennten Text in Zeilen aufteilen
Public Sub SplitTextInCellsToRows()
'UpdatebyExtendoffice20220622
Dim xSRg, xIptRg, xCrRg, xRg As Range
Dim xSplitChar As String
Dim xArr As Variant
Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
Dim xWSh As Worksheet
Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
If xSRg Is Nothing Then Exit Sub
xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
If xSplitChar = "" Then Exit Sub
Application.ScreenUpdating = False
xRow = xSRg.Row
xColumn = xSRg.Column
Set xWSh = xSRg.Worksheet
For xFNum = xSRg.Rows.Count To 1 Step -1
Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
Debug.Print xRg.Address
xArr = Split(xRg, xSplitChar)
For xFFNum = LBound(xArr) To UBound(xArr)
xRg.EntireRow.Copy
xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xFFNum)
Next
xRg.EntireRow.Delete
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub4. Drücken Sie F5 oder klicken Sie auf „Ausführen“, um den Code zu starten. Wählen Sie bei der Aufforderung den Zellbereich (ohne Überschriften) aus, der den durch Trennzeichen getrennten Text enthält, den Sie in einzelne Zeilen aufteilen möchten, und bestätigen Sie mit OK.
5. Geben Sie im nächsten Dialogfeld das Trennzeichen ein, nach dem die Daten aufgeteilt werden sollen – beispielsweise ein Komma (,), ein Semikolon oder ein Leerzeichen. Wenn Ihre Daten kommagetrennt sind, geben Sie einfach ein Komma (,) ein und klicken Sie anschließend auf OK.
Jeder durch Trennzeichen getrennte Wert wird nun in eine eigene Zeile eingefügt, und alle zugehörigen Spalten werden entsprechend dupliziert – so bleiben Ihre Datensätze stets vollständig.
Hinweis: Die VBA-Methode verändert Ihren ursprünglichen Datensatz. Sichern Sie daher Ihre Arbeitsmappe oder erstellen Sie eine Sicherungskopie, bevor Sie das Makro ausführen. Achten Sie zudem bei größeren Datensätzen oder komplexen Arbeitsmappen darauf, dass Makros aktiviert sind und Sie über die erforderlichen Berechtigungen zum Ausführen von VBA-Code verfügen.
Für Excel-Nutzer, die eine grafische Oberfläche bevorzugen und das Schreiben oder Ausführen von Code vermeiden möchten, bietet Kutools für Excel ein äußerst effizientes Werkzeug, um durch Trennzeichen getrennten Text in Zeilen aufzuteilen. Dieses Add-In ist ideal für alle, die eine komfortable Lösung für kleine wie große Datenmengen suchen – ganz ohne Formeln oder Skripte. Kutools unterstützt verschiedene Trennzeichen wie Kommas, Semikolons und Leerzeichen und meistert zahlreiche Anwendungsszenarien mühelos. Besonders vorteilhaft ist es, wenn Sie eine schnelle und fehlerfreie Umwandlung benötigen, bei der alle zugehörigen Spalten konsistent übernommen werden.
Führen Sie nach der Installation von Kutools für Excel die folgenden Schritte aus:
1. Klicken Sie auf die Registerkarte „Kutools“, navigieren Sie zu „Vereinigen/Aufteilen“ und wählen Sie im Dropdown-Menü „In mehrere Zeilen aufteilen“. Orientieren Sie sich am folgenden Screenshot:
2. Führen Sie im Dialogfeld „In mehrere Zeilen aufteilen“ die folgenden Schritte aus, um den Vorgang zu konfigurieren:
- Wählen Sie die Spalte aus, die Sie im Textfeld „Bereich (einzige Spalte)“ aufteilen möchten.
- Wählen Sie das Trennzeichen aus, mit dem Ihre Daten getrennt sind. Für benutzerdefinierte Trennzeichen – wie beispielsweise „, “ (Komma und Leerzeichen) – wählen Sie die Option „Andere“ und geben Sie Ihr eigenes Trennzeichen ein.
- Nachdem Sie Ihre Einstellungen überprüft haben, klicken Sie auf die Schaltfläche „OK“.
Der ausgewählte, durch Trennzeichen getrennte Text wird sofort anhand Ihres festgelegten Trennzeichens in mehrere Zeilen aufgeteilt – alle anderen Spalten bleiben erhalten und werden bei Bedarf wiederholt.
Bei der Verwendung von Kutools empfiehlt es sich – besonders bei größeren Tabellen –, zunächst Ihre Einstellungen zu überprüfen, bevor Sie bestätigen. Zudem bietet Kutools praktische Optionen zur Verarbeitung leerer Zellen, benutzerdefinierte Trennzeichen sowie weitere erweiterte Aufteilungsfunktionen für spezialisierte Anwendungsfälle. Wenn Sie häufig durch Trennzeichen getrennte Daten aufteilen, spart Ihnen diese Methode erheblich Zeit und Aufwand.
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
Power Query ist ein leistungsstarkes Datenumwandlungstool, das in Excel 2016 und höher enthalten ist (und als kostenloses Add-In für Excel 2013 verfügbar ist). Es eignet sich besonders gut für komplexe oder umfangreiche Datenumstrukturierungen und ermöglicht das Aufteilen von durch Trennzeichen getrenntem Text in Zeilen unter gleichzeitiger Sicherstellung von Nachvollziehbarkeit und Reproduzierbarkeit durch Abfrageschritte. Power Query ist die bevorzugte Methode, wenn Sie importierte Daten häufig neu strukturieren, mehrere Umwandlungsschritte kombinieren oder Daten aufteilen automatisch aktualisieren möchten, sobald sich die Quelle ändert. Gehen Sie vorsichtig vor, wenn Sie mit Tabellen arbeiten, die mit externen Quellbereich verknüpft sind, und beachten Sie, dass das Ergebnis nach dem Laden in einem neuen Arbeitsblatt erscheint.
1. Wählen Sie Ihren ursprünglichen Datenbereich aus und klicken Sie auf „Daten“ > „Aus Tabelle/Bereich“.
2. Aktivieren Sie im Dialogfeld „Tabelle erstellen“ gegebenenfalls das Kontrollkästchen „Meine Tabelle hat Überschriften“ und bestätigen Sie mit OK.
Das Power Query-Editor-Fenster öffnet sich nun und zeigt Ihre Daten übersichtlich im Tabellenformat an.
3. Wählen Sie die Spalte mit dem durch Trennzeichen getrennten Text aus, wechseln Sie zur Registerkarte „Transformieren“ und klicken Sie auf „Spalte teilen“ > „Nach Trennzeichen“.
Alternativ klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift, um über das Kontextmenü direkt auf dieselben „Spalte teilen“-Optionen zuzugreifen.
4. Wählen Sie im Dialogfeld „Spalte nach Trennzeichen teilen“ Ihr gewünschtes Trennzeichen (z. B. Komma oder Leerzeichen) aus der Dropdown-Liste „Trennzeichen auswählen oder eingeben“. Stellen Sie sicher, dass die Option „Jedes Vorkommen des Trennzeichens“ aktiviert ist. Erweitern Sie unter „Erweiterte Optionen“ den Bereich und wählen Sie „Zeilen“. Klicken Sie anschließend auf „OK“.
Nach der Anwendung der Aufteilung wird jeder durch das gewählte Trennzeichen getrennte Wert in eine neue Zeile übertragen.
5. Klicken Sie nach Abschluss Ihrer Anpassungen auf „Schließen und laden“ und bestätigen Sie im Popup-Dialogfeld mit „Behalten“.
Ein neues Arbeitsblatt, das Ihre neuen Daten aufteilt, wird erstellt.
Mit Power Query behalten Sie eine vollständige Aufzeichnung Ihrer Umwandlungsschritte – so aktualisieren Sie Ihre aufgeteilten Daten mühelos, sobald sich die Quelle ändert, und automatisieren komplexe Wiederholungen. Als Best Practice sollten Sie stets sicherstellen, dass Trennzeichen und Spaltenauswahl korrekt sind, bevor Sie das Laden abschließen. Sollten Fehler oder falsche Zeilenanzahlen auftreten, überprüfen Sie jeden Umwandlungsschritt im Bereich „Angewendete Schritte“, um die Probleme gezielt zu beheben.
Durch Trennzeichen getrennten Text mithilfe einer Excel-Formel (TEXTSPLIT, FILTER, SEQUENZ) in Zeilen aufteilen
Ab Excel 365 und Excel 2021 stehen Benutzern dynamische Arrayformeln wie TEXTSPLIT, FILTER und SEQUENZ zur Verfügung – mit ihnen lässt sich durch Trennzeichen getrennter Text ganz ohne Code oder Add-Ins automatisch in mehrere Zeilen aufteilen. Diese native, formelbasierte Lösung eignet sich ideal für alle, die eine stets aktuelle Darstellung wünschen: Sobald sich die Quelldaten ändern, aktualisiert sich das Ergebnis automatisch. Besonders praktisch ist dieser Ansatz für Live-Dashboards, Berichts-Arbeitsmappen oder Situationen, in denen Automatisierung und Transparenz der manuellen Verarbeitung vorgezogen werden. Beachten Sie jedoch: Diese Funktionen sind ausschließlich in Microsoft 365 sowie Excel 2021 oder höher verfügbar.
Anwendungsszenario: Sie haben eine Liste, bei der jede Zelle mehrere Werte enthält, die durch ein bestimmtes Trennzeichen wie Kommas oder Semikolons getrennt sind – und möchten diese in eine fortlaufende Liste mit jeweils einem Wert pro Zeile umwandeln.
Vorteile: Nativer Support, sofortige Aktualisierung – keine Makros oder Add-Ins erforderlich. Die Ergebnisse passen sich automatisch an und erweitern sich dynamisch, um variable Anzahlen aufgeteilter Elemente aufzunehmen.
Einschränkungen: Diese Formeln wiederholen standardmäßig keine anderen Spalten. Möglicherweise sind zusätzliche Schritte erforderlich, um verwandte Daten zu verknüpfen – insbesondere bei mehrspaltigen Datensätzen.
1. Angenommen, Ihre durch Trennzeichen getrennten Daten beginnen in Zelle A2 (z. B. „Apple,Banana,Cherry“). Geben Sie in einem neuen Arbeitsblatt oder einer leeren Spalte diese Formel in B2 ein, um alle Werte in separate Zeilen aufzuteilen:
=TRANSPOSE(TEXTSPLIT(A2, ","))Nachdem Sie die Formel eingegeben haben, drücken Sie die Eingabetaste. Die Zelle gibt das Ergebnis vertikal aus und listet jede Frucht in einer eigenen Zeile auf. Sie können diese Formel bei Bedarf durch Ziehen oder Kopieren auf weitere Zeilen übertragen. Beachten Sie, dass Sie bei der TEXTSPLIT-Funktion das Trennzeichen angeben können – passen Sie daher "," entsprechend dem Symbol in Ihren Daten an.
2. Wenn Sie einen Zellbereich (z. B. A2:A4) mit durch Trennzeichen getrennten Werten aufteilen und alle Ergebnisse in einer einzigen Spalte auflisten möchten, verwenden Sie folgende Arrayformel in einer anderen Spalte (z. B. ab Zelle B2):
=LET(flat, TEXTSPLIT(TEXTJOIN(",",TRUE,A2:A4), ","), TRANSPOSE(flat))Diese Formel kombiniert alle Werte aus A2:A4, teilt sie am Trennzeichen auf und listet sie anschließend in separaten Zeilen auf. Drücken Sie die Eingabetaste – schon erscheinen alle Werte als vertikaler Ausgabebereich. Falls Ihre Daten ein anderes Trennzeichen verwenden, ersetzen Sie "," entsprechend.
Für Benutzer ohne TEXTSPLIT können Sie eine Kombination aus Formeln mit FILTER, INDEX und SEQUENCE verwenden – der Vorgang ist jedoch komplexer und weniger dynamisch.
Beim Anwenden dieser Formeln resultieren Fehler üblicherweise aus nicht übereinstimmenden Trennzeichen, zusätzlichen Leerzeichen oder nicht standardmäßiger Zellenformatierung. Überprüfen Sie Ihr Trennzeichen sorgfältig und stellen Sie sicher, dass in Ihren Daten keine überflüssigen Leerzeichen vorhanden sind. Seien Sie beim Anpassen an mehrere Zeilen oder Spalten vorsichtig beim Kopieren und Verweisen, um das Überschreiben vorhandener Daten zu vermeiden.
Hinweis: Wenn im Ausgabebereich ein #SPILL!-Fehler angezeigt wird, stellen Sie sicher, dass direkt unterhalb der Formel keine blockierenden Inhalte (z. B. andere ausgefüllte Zellen) vorhanden sind, und passen Sie gegebenenfalls die Bezüge an. Wir empfehlen, Ihre Arbeit zu speichern oder bei umfangreichem Einsatz dynamischer Formeln mit neuen Datensätzen auf einer separaten Kopie zu arbeiten.
Diese formelbasierten Lösungen bieten eine effiziente und einfach aktualisierbare Möglichkeit, Text in Zeilen aufzuteilen, und sind damit ideal für moderne Excel-Umgebungen, die Automatisierung und Flexibilität priorisieren.
Wie wechselt man die Zeilenfarbe gruppenbasiert in Excel?
In Excel ist es für die meisten von uns einfach, jede zweite Zeile einzufärben. Aber haben Sie schon einmal versucht, die Zeilen abwechselnd einzufärben, sobald sich der Wert in einer Spalte ändert – beispielsweise in Spalte A, wie im folgenden Screenshot gezeigt? In diesem Artikel zeige ich Ihnen, wie Sie die Zeilenfarbe in Excel gruppenbasiert und abwechselnd gestalten können.
Wie reduziert man die Excel-Dateigröße?
Wenn eine Excel-Datei zu groß ist, kann das Öffnen oder Speichern mehrere Minuten dauern. In diesem Tutorial zeigen wir Ihnen, wie Sie die Dateigröße effektiv verringern – indem Sie ungenutzte Inhalte und überflüssige Formatierungen entfernen.
Excel: So erstellen oder fügen Sie ein Lesezeichen ein
Haben Sie sich schon einmal gewünscht, ein Lesezeichen zu erstellen oder einzufügen, um bei umfangreichen Daten in einem Arbeitsblatt oder einer Arbeitsmappe blitzschnell zum gewünschten Bereich zu springen?
Wie färbt man ungerade oder gerade (abwechselnde) Zeilen/Spalten in Excel?
Beim Gestalten eines Arbeitsblatts färben viele Benutzer:innen ungerade oder gerade (abwechselnde) Zeilen oder Spalten ein, um das Arbeitsblatt übersichtlicher zu gestalten. In diesem Artikel stellen wir zwei Methoden vor, mit denen Sie ungerade oder gerade Zeilen/Spalten in Excel einfärben können.
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