Zeilenumbrüche in Spalten trennen

Mehrzeilge Zelle(n) in mehrere nebeneinander stehende Zellen aufteilen

Immer wieder tauchen beispielsweise in Foren Fragen auf, wie mehrzeilige Zellen in mehrere Zellen aufgeteilt werden können. Dabei sind nicht Zellen gemeint, die wegen der Formatierung mehrzeilig sind, sondern nur jene, wo durch mindestens eine Zeilenschaltung (Alt+Eingabe) mehrere Zeilen in dieser einen Zelle, beispielsweise A1 sind. Eine kleine entsprechende kleine Musterdatei können (und sollten) Sie hier herunterladen. Es wird vermutlich bei Ihnen ein Hinweis erscheinen, dass die Datei wegen enthaltener Makros nicht sicher ist. Wie Sie damit umgehen, überlasse ich Ihnen. Es ist ausschließlich der weiter unten gelistete Code enthalten. Auf Wunsch senden wir Ihnen aber auch gerne ein File ohne enthaltene Makros zu.

Die Daten sind als Intelligente Tabelle/Liste eingerichtet bzw. formatiert. Das hat einige Vorteile. Insbesondere den, dass Formeln automatisch in der gesamten Spalte ergänzt werden. Außerdem gibt es die Möglichkeit, eine Zusammenfassung einzufügen und dort aggregierte Werte anzeigen zu lassen. Weiter unten dazu mehr … Die Überschrift der Liste ist nicht gerade eine gedankliche Explosion, aber da sie durch Excel selbst eingefügt worden ist, reicht das vollkommen aus.

Die Lösungsvorschläge führen alle zum Ziel. Welcher für Sie der beste, der optimalste ist hängt von Ihren Fähigkeiten in Sachen „Dazulernen“ ab, von Ihrem Geschmack, Ihrer Excel-Version, …

▲ nach oben …

Ohne Hilfsspalte (1)

Solange sich die auszuwertenden Daten nur in der letzten Spalte befinden und diese dann entsprechend aufgeteilt werden sollen, bietet Excel eine Methode an, die einfach aber prägnant ist:

  • Markieren Sie den Bereich der Daten oder (notfalls) die ganze Spalte. Bei einer Intelligenten Tabelle (wie hier) ist es am einfachsten, eine Zelle zu markieren und dann StrgA.
  • Menü Daten, Gruppe Datentools, Text in Spalten
  • Datentyp: Getrennt, Weiter >
  • Trennzeichen: Andere markieren
  • In dem Textfeld daneben geben Sie folgendes ein:
    • Alt (gedrückt halten) 0 1 0
    • Dann erst Alt loslassen
  • Auch wenn das Zeichen in dem Textfeld nicht direkt oder schlecht sichtbar ist, im unteren Fensterbereich sollte die Splittung sichtbar sein:

Der zweite Schritt beim teilen der Daten

Der zweite Schritt beim teilen der Daten

  • Das reicht schon, darum auf Fertig Stellen klicken.

Das Ziel ist erreicht. Aber wie gesagt, es funktioniert nur so einfach, wenn in der rechten Nachbarspalte keine Daten stehen. Falls doch, wäre ein denkbarer Umweg, dass Sie nach dem zweiten Schritt auf Weiter > klicken und im dritten Schritt ändern Sie den vorgegebenen Zielbereich $A$2 beispielsweise auf D2:

Hier bitte das neue Zeil eingeben

Hier bitte das neue Zeil eingeben

Wie schon erwähnt: Wichtig ist nur, dass in den Folgespalten keine Daten stehen. Erkennt Excel, dass dort irgendwo doch Daten oder Formeln enthalten sind, wird eine entsprechende Warnmeldung mit der Möglichkeit des Abbruchs ausgegeben.

▲ nach oben …

Mit Hilfsspalte

Vielleicht gelingt es Ihnen -aus welchen Gründen auch immer- nicht, mit der Zehnertastatur zu hantieren. Das ist bei älteren Notebooks, Netbooks, etc. mitunter etwas problematisch. Dann ist eine Hilfsspalte gewiss ein einfacher Weg. Tragen Sie in B2 folgende Formel ein:
=WECHSELN(A2; ZEICHEN(10); "|")
Jede Zeilenschaltung ZEICHEN(10) wird durch das Pipe-Symbol (|) ersetzt. Und jetzt ist es recht einfach, die Zellen über Text in Spalten zu splitten. Das Pipe-Symbol können Sie ja auf jeder Tastatur per AltGr< eingeben.

▲ nach oben …

Ohne Hilfsspalte (2)

Grundsätzliche Anmerkung: Sie müssen Excel (Windows) ab Version 2010 einsetzen und sich eventuell bei Microsoft ein Add-In herunterladen: Power Query. Mehr dazu können Sie hier im Blog nachlesen.

Ausgangspunkt ist wiederum die (unbearbeitete) Tabelle, welche Sie oben schon kennen gelernt haben. Hier noch einmal der Link, damit Sie „sauber“ starten können. 😎 Je nach Excel-Version ist es nun leicht unterschiedlich, wie Sie die ersten Schritte vollziehen. Die Daten der Tabelle müssen in den Abfrage-Editor geladen werden …

▲ nach oben …

Excel-Versionen 2010/2013

Die aktive Zelle ist auf jeden Fall eine der Zellen, die ausgewertet werden sollen. Also in der Intelligenten Tabelle.

  • Menü Power Query, Gruppe Excel-Daten und hier der einzige Punkt: Von Tabelle.
  • Bestätigen Sie im Dialogfenster den vorgegebenen Bereich oder korrigieren Sie ihn und achten Sie darauf, dass das Häkchen bei Meine Tabelle hat Überschriften gesetzt ist.

Hinweis: Es ist durchaus möglich (sogar wahrscheinlich), dass keine Nachfrage kommt und der als Tabelle formatierte Bereich direkt und ohne Nachfrage übernommen wird.

▲ nach oben …

Excel-Version 2016

Die aktive Zelle ist auf jeden Fall eine der Zellen, die ausgewertet werden sollen. Also in der Intelligenten Tabelle.

  • Menü Daten, Gruppe Abrufen und transformieren
  • Menüpunkt Aus Tabelle

… und automatisch wird die aktuelle Tabelle in den Editor geladen.

▲ nach oben …

Alle Versionen (mit Power Query)

Die Daten sind nun in den Abfrage-Editor geladen. In einem neuen Fenster stellt sich das so dar:

Der Abfrage-Editor direkt nach dem laden

Der Abfrage-Editor direkt nach dem laden

Auch hier gibt es ein Menü, ein Menüband (mit Symbolen/Ribbons) und Gruppen. Ausgewählt ist das Menü Start, dabei bleibt es auch. Und Sie erkennen vielleicht, dass die erste (und einzige) Spalte markiert ist. Dennoch ist ein Klick in die Überschrift hilfreich, um in jedem Fall die ganze Spalte auszuwählen. Das erkennen Sie dann auch daran, dass die Überschrift dann ein dunkleres Grün annimmt.

In der Gruppe Transformieren klicken Sie auf das Symbol Spalte teilen. Das entspricht in etwa der Funktionalität Text in Spalten aus dem normalen Excel. Im DropDown gibt es nur zwei Auswahlmöglichkeiten, Sie wählen natürlich die obere:

Auswahl: Nach Trennzeichen

Auswahl: Nach Trennzeichen

Die direkte Eingabe des Trennzeichens über die Zehnertastatur (das kennen Sie ja schon) geht/funktioniert hier nicht. Ich musste da auch einige Zeit experimentieren, um das festzustellen. Dennoch werden Sie nach dem Klick auf Nach Trennzeichen solch ein Fenster sehen:

Ausgangslage des Trennen-Dialoga

Ausgangslage des Trennen-Dialoga

In der DropDown-Auswahl, wo als aktueller Wert Komma angezeigt wird, wählen Sie den letzten angezeigten Wert, –Benutzerdefiniert–. Direkt darunter erscheint nun ein neues Textfeld, wo das Trennzeichen eingegeben werden soll. Das lassen Sie bitte leer. Bei der Auswahl Teilen ist auch Bei jedem Vorkommen des Trennzeichens genau richtig.

Ein Klick auf Erweiterte Optionen öffnet eine neue Auswahlmöglichkeit:

Die erweiterten Möglichkeiten

Die erweiterten Möglichkeiten

Und hier heißt es erst einmal STOP! PQ (Power Query) möchte die Spaltenzahl für die Teilung wissen. Hier werden zwar 4 vorgeschlagen (oft auch nur 2), seinen Sie aber gewiss, das stimmt für diese Daten nicht. Irgendwie müssen Sie also nun noch herausbekommen, wie die höchste Anzahl an Zeilenschaltungen in einer der Zellen. Klar, Sie könnten es sich einfach machen und „frech“ 99 dort hinein schreiben. So viele Trennzeichen sind es garantiert nicht. Aber dann hätten Sie im Anschluss die Arbeit, die überflüssigen Spalten zu löschen. Und wenn das mehrere hundert oder sogar hunderttausend Zeilen sind, dann ist das ein so gut wie unmögliches Unterfangen.

Darum brechen Sie hier erst einmal ab. Ich klicke dazu in der Regel einfach auf die Schaltfläche Abbrechen in dem inneren Fenster und dann auf den Menüpunkt Datei | Verwerfen und schließen. Zugegeben, alles was Sie bis jetzt im Editor gemacht haben ist verloren, aber durchaus rasch wieder hergestellt. Hier habe ich Sie erst einmal in die Irre geführt, damit Sie das Ganze etwas üben können.  😉 

▲ nach oben …

Nun doch mit Hilfsspalte  😉 

Jetzt sind Sie wieder in der Tabelle und können erkunden, wie viele Zeilenschaltungen denn maximal in der Spalte A in den einzelnen Zellen enthalten sind. Denn daraus ergibt sich ja, wie viele Spalten bei der Auswertung durch PQ eingerichtet werden sollen. Bei diesen wenigen Zeilen könnten Sie ja rasch mal zählen, oder? Kennen Sie das Sprichwort: „Dicht daneben ist auch vorbei!“? Sie würden glatt daneben liegen, denn die Zeile 11 besteht aus genau 1 Zeile. (Es hat mich richtig viel Mühe gekostet, so viele Worte mir halbwegs Sinn da hinein zu bekommen.) Übrigens: Das war eben auch schon im Abfrage-Editor ganz gut zu sehen 🙂 . Das Format der Zelle sorgt nur dafür, dass mehrere Zeilen sichtbar sind. Dort ist also kein einziger Zeilenumbruch im Sinne des ZEICHEN(10) drin.

Also muss ein anderer Weg beschritten werden. Das geht idealerweise über zwei Hilfsspalten oder zumindest 1 Hilfsspalte und eine Ergebnis-Zelle. Tragen Sie neben der Liste in B2 diese Formel ein:
=LÄNGE(A2)-LÄNGE(WECHSELN(A2; ZEICHEN(10);))+1
Damit berechnen Sie die Zahl der enthaltenen Zeilenschaltungen (entspricht ja ZEICHEN(10) und addieren 1. Warum 1 mehr? Nun ja, A1 enthält ja keine Zeilenschaltung, das Ergebnis ist also null. Da ich aber die Zahl der benötigten Spalten für den Eintrag brauche, muss natürlich noch 1 addiert werden. Dank der Listen-Funktionalität wird die Formel automatisch bis zur letzten Zeile herunter kopiert.

Selbstredend könnte ich jetzt den höchsten Wert durch Augenschein heraussuchen, aber das ist wiederum bei großen Tabellen einfach kontraproduktiv. Darum beispielsweise in eine zweite Hilfsspalte, also in C2 diese Formel: =MAX(B:B); diese Formel könnten Sie auch in eine gesonderte Zelle schreiben. Das Ergebnis sind hier 5 Spalten, die benötigt werden. Und den Wert merken Sie sich.

Aber Sie haben doch eine Intelligente Tabelle! Nutzen Sie die Möglichkeiten, die sich Ihnen da bieten. Die erste Hilfsspalte enthält ja die berechnete Anzahl der benötigten Spalten. Eine zweite Hilfsspalte oder extra Ergebnis-Zelle ist überflüssig, kann also gelöscht werden. Rechtsklick in Spalte B, Tabelle | Ergebniszeile und automatisch wird am Ende eine neue Zeile eingefügt, wo in Spalte B die Summe berechnet wird. Ein Klick auf den DropDown-Pfeil DropDownPfeil rechts der Zelle und Sie wählen Maximum aus. Sie ahnen es, das Ergebnis ist 5. Damit diese Zeile nicht in den Editor übernommen wird, erst natürlich das Ergebnis merken und dann noch einmal Tabelle | Ergebniszeile um diese Zeile wieder zu löschen.

Jetzt gehen Sie genau so vor, wie zuvor beschrieben. Erstellen Sie aus der Tabelle eine Abfrage im Abfrage-Editor. Die sieht nun wegen der ein bzw. zwei Spalten mehr etwas anders aus, aber das ist gar nicht einmal so schlecht. Denn dadurch ist da der Umstand gegeben, dass direkt neben der zu teilenden Spalte noch weitere Werte stehen. Sie erinnern sich, dass so etwas in plain Excel nicht so ideal war.

Wiederum die markierte Spalte Teilen, und zwar nach Trennzeichen. Vorsichtshalber die Überschrift der ersten Spalte anklicken. Auch –Benutzerdefiniert– ist korrekt und Sie aktivieren Erweiterten Optionen. Als Spaltenzahl für die Teilung tragen Sie nun 5 ein, was Sie ja eben berechnet hatten und was übrigens auch im Editor zu sehen ist, falls Sie die Max-Berechnung innerhalb der zweiten Hilfsspalte gemacht haben.

Setzen Sie nun das Häkchen ganz unten im inneren Fenster bei Mithilfe von Sonderzeichen teilen. Nun können Sie im darunterliegenden DropDown den Zeilenvorschub per Mausklick auswählen. Aber bitte, ein Mausklick reicht. Auch wenn es nicht sofort auffällt, in der bislang leeren Zeile unterhalb –Benutzerdefiniert– ist ein Code eingetragen worden, der stellvertretend für den Zeilenumbruch steht. Später können Sie diese Zeichenfolge auch direkt ohne Umwege in das Feld eingeben.

Jetzt OK und es werden automatisch an der korrekten Position vier weitere Spalten eingefügt und mit den Werten der einzelnen „logischen“ Zeilen gefüllt. Dort, wo weniger Zeilenschaltungen waren, steht der Wert null im Feld der entsprechenden Spalte.

Bleibt eigentlich nur noch, die letzte oder die letzten beiden Hilfsspalten zu markieren über das Symbol oder Rechtsklick und dann im Kontextmenü Spalten entfernen zu löschen. – Das allererste Symbol oben im Bildbereich anklicken und der Editor wird geschlossen. In einem neuen Arbeitsblatt wird eine Tabelle erstellt, die genau den Forderungen entspricht.

Zugegeben, anfangs etwas Aufwand. Vor allen Dingen, wenn Sie noch nie Kontakt zu Power Query hatten. Aber der riesige Vorteil: Wenn sich die Basisdaten ändern, dann genügt ein Klick auf Abfrage | Aktualisieren und dann ist die Ergebnis-Tabelle auf dem neuesten Stand. Ach ja, und nicht eine Zeile VBA-Code (den ich übrigens auch liebe).

▲ nach oben …

Lösung der VBA / Makro

Option Explicit
Option Base 1

Sub ZeilenschaltungenZuSpalten()
'Bewusst ohne Listen-Funktionalität
'Zu Vergleichzwecken erfolgt die Ausgabe in Spalte_C:??
   Dim fRow As Long, lRow As Long, c As Range, z As Range
   Dim aZelle
   Dim Ze As Long, Sp As Long, SpDst As Long
   Dim rngDst As Range
   
   Application.ScreenUpdating = False  'Flimmern verhindern + Speed
   fRow = 2 'first Row
   lRow = Cells(Rows.Count, 1).End(xlUp).Row
   SpDst = 3   'Ausgabe ab Spalte 3
   For Each c In Range(Cells(fRow, 1), Cells(lRow, 1))
      Ze = c.Row
      aZelle = Split(c, Chr(10))
      Sp = UBound(aZelle)
      Set rngDst = Range(Cells(Ze, SpDst), Cells(Ze, SpDst + Sp))
      rngDst = aZelle
      'Zahlen als Zahl
      For Each z In rngDst
         If IsNumeric(z) Then z = z * 1
      Next z
   Next c
   Application.ScreenUpdating = True
End Sub

Sie haben mehrere Wege kennen gelernt, wie Sie zum Ziel kommen. Darauf aufbauend werden Sie gewiss auch Ihr persönliches Problem lösen können.

▲ nach oben …

Dieser Beitrag wurde unter Formatierung, Mit VBA/Makro, Ohne Makro/VBA, Power Query, Tabelle und Zelle, Wege nach Rom abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.