Zeilenumbruch in Spalten umwandeln

Zeilenumbrüche in einer Zelle in mehrere Spalten aufteilen

In dieser Datei sind in Spalte A verschiedene Zellen, wo eine mehrzeilige Darstellung dadurch erreicht wurde, dass eine manuelle Zeilenschaltung (mittels AltReturn) erzwungen wurde. Das Ziel ist nun, jede dieser sichtbaren Zeilen einer Zelle in die entsprechende Anzahl von Spalten der gleichen Zeile aufzuteilen.

Mit Bordmitteln

Der auf den ersten Blick einfachste Weg ist bereits im Standard-Excel integriert. Das Stichwort heißt: Text in Spalten. Wechseln Sie zum Menü Daten; dort erkennen sie in der Gruppe Datentools den Menüpunkt Text in Spalten. Bevor sie jedoch auf dieses Symbol klicken, werden Sie die Spalte A entweder komplett oder nur den Datenbereich markieren. Anschließend ein Klick auf das Symbol und es zeigt sich das Dialogfenster des Textkonvertierung-Assistenten mit dem ersten von drei Schritten:

Der Text-In-Spalten Assistent, erstes Fenster

Der Text-In-Spalten Assistent, erstes Fenster

Zugegeben, in dieser Vorschau ist nicht erkennbar, ob in der einzelnen Zelle nun Zeilenschaltungen enthalten sind oder nicht. Hier ist es nur wichtig dass sie wissen, es sind gewollte Zeilenumbrüche in den Zellen enthalten. 

Darum ist die Vorauswahl Getrennt die richtige. Ein Klick auf die Schaltfläche Weiter > bringt sie zum nächsten Fenster, wo sich die Daten schon etwas anders darstellen:

Der zweite Schritt erscheint zu Beginn irritierend

Der zweite Schritt erscheint zu Beginn irritierend

Es ist nur jeweils die erste sichtbare Zeile jeder einzelnen Zelle in der Vorschau dargestellt. Die danach folgenden „Zeilen“ sind offensichtlich „unterschlagen“. Klicken Sie nun in der Gruppe Trennzeichen auf die Auswahl Andere: und geben Sie in dem Textfenster daneben folgende Ziffern von der Zehnertastatur bei gedrückter Taste Alt nacheinander so ein: 010. (Bei älteren Notebooks ohne Zehnertastatur müssen Sie die Taste Fn bemühen.) In dem kleinen Textfenster werden sie nur erkennen, dass der Cursor kein Strich mehr ist sondern ein blinkender Punkt. In der Datenvorschau allerdings sehen sie sofort, dass diese Aktion die richtige war; die Zeilen der einzelnen Zelle sind in Spalten aufgeteilt. Auf zum nächsten Schritt mit Weiter >.

Im letzten Schritt des Assistenten werden sie zu Beginn überprüfen, ob alle Spalten auch das richtige Format haben und ob diese importiert werden sollen oder auch nicht. Das stellt sich nun so dar:

Der dritte und (vorläufig) letzte Schritt im Dialog

Der dritte und (vorläufig) letzte Schritt im Dialog

 Etwas anderes ist hier aber wichtig: Wenn Sie jetzt auf Fertig stellen klicken, käme ein Dialog der sie darauf hinweist, dass es in SpalteB schon Daten gibt und Sie werden gefragt, ob sie diese ersetzen wollen. Im Normalfall wird Ihnen daran nicht gelegen sein, darum ein Klick auf Abbrechen. Danach hat sich erst einmal gar nichts getan. Rufen Sie den Vorgang noch einmal auf und sie werden erkennen, dass Excel ihre bisherigen Einstellungen übernommen hat. Im dritten Schritt geben Sie bei Zielbereich: beispielsweise die Zelle D2 als Ziel an. Dadurch haben sie die Daten von der Tabelle durch eine Leerspalte getrennt.

Prinzipiell sieht das jetzt schon sehr gut aus. Folgende Schritte sind durchzuführen:

  • Kopieren Sie die Überschrift aus A1 nach D1,
  • verschieben sie die Daten der Spalte B in der die letzte Spalte mit den Daten (hier: Spalte I)
  • und löschen Sie die Spalten A:C. Das war’s, die Aufgabe ist damit in plain Excel erledigt.

▲ nach oben …

Mit VBA / Makros

wenn dieser Vorgang öfter einmal durchgeführt werden soll, dann bietet sich die Verwendung von VBA (Makros) an. Mit einer kleinen Routine können Sie das recht einfach erledigen. Der folgende Code schreibt die transportierten Zellen in ein Array und fügt dieses an der ursprünglichen Position der auszuwertenden Daten ein. In jedem Fall werden so viele Spalten eingefügt, wie die höchste Anzahl von „Zeilen“ in den einzelnen Zellen sind. Selbst wenn rechts noch genügend Spalten frei wären, werden eventuelle belegte Spalten um dieser Anzahl nach rechts verschoben. Hier nun der Code:

Option Explicit
Option Base 1

Sub SplitLF2Columns()
   Dim lRow As Long, fRow As Long, bUeb As Boolean, cUeb As String
   Dim Ze As Long, Sp As Long, SpArr As Long
   Dim aData1, aData2, aC, AnzLF As Long
   
   bUeb = True 'Es gibt eine Überschrift
   fRow = 2 'erste Datenzeile
   Sp = 1   'auszuwertende Spalte
   If bUeb Then cUeb = Cells(fRow - 1, Sp)
   lRow = Cells(Rows.Count, Sp).End(xlUp).Row 'letzte Datenzeile
   aData1 = Range(Cells(fRow, Sp), Cells(lRow, Sp))
   For Ze = 1 To UBound(aData1)
      AnzLF = WorksheetFunction.Max(AnzLF, UBound(Split(aData1(Ze, 1), Chr(10))))
   Next Ze
   ReDim aData2(UBound(aData1), AnzLF + 1)
   For Ze = 1 To UBound(aData1)
      aC = Split(aData1(Ze, 1), Chr(10))
      For SpArr = 0 To UBound(aC)
         aData2(Ze, SpArr + 1) = aC(SpArr)
      Next SpArr
   Next Ze
   'Platz schaffen, falls erforderlich
   
   Columns(Sp).Delete
   Range(ColN2C(Sp) & ":" & ColN2C(Sp + AnzLF)).EntireColumn.Insert
   Cells(fRow, Sp).Resize(UBound(aData1), AnzLF + 1) = aData2
   If bUeb Then Cells(fRow - 1, Sp) = cUeb
End Sub

Function ColN2C(iNumCol As Long) As String
   Dim cSp
   cSp = Split(Columns(iNumCol).Address(0, 0), ":")
   ColN2C = cSp(0)
End Function

▲ nach oben …

Zukunftsweisende Alternative: Power Query

Die aus meiner Sicht einfachste und am ehesten zukunftsweisende Lösung ist der Einsatz von Power Query. Voraussetzung ist natürlich, dass sie eine Excel-Version ab 2010, besser 2013 oder sogar >=2016 haben. Vorher steht Ihnen das Power Query bzw. Abrufen und transformieren nicht zur Verfügung.

Etwas Vorarbeit ist für diese Aufgabe, Lösung mit PQ aber angesagt. Für die spätere Auswertung in Power Query ist es hilfreich, wenn sie wissen, um wie viel Spalten es sich künftig handelt, wenn die Zeilen gesplittet und transponiert worden sind. Tragen Sie dazu in eine beliebige Zelle diese Formel ein:
=MAX(LÄNGE(A:A) - LÄNGE(WECHSELN(A:A; ZEICHEN(10);))+1)

Schließen Sie die Eingabe nicht wie sonst mit mit einem einfachen Return sondern per StrgShiftReturn, da es sich um eine sogenannte Matrix Formel handelt. Hinweis: Sie werden erkennen, dass Sie diese Formel auf die Spalte A bezieht; sollte eine andere Spalte diese Zeilenumbrüche enthalten, müssen sie an zwei Stellen in der Formel den Bereich anpassen. – In der Beispiel-Datei erhalten Sie 5 als Ergebnis. Merken Sie sich bitte diese Zahl, sie werden sie gleich brauchen.

Klicken Sie nun irgendwo in den Datenbereich und wählen Sie in Excel 2010/13 den Menüpunkt Power Query, in Excel 2016 den Menüpunkt Daten. Dann ein Klick auf Aus Tabelle (Von Tabelle) und Excel fragt Sie, ob der Bereich korrekt ist und ob die Tabelle Überschriften hat. Anhand der „Ameisenkolonne“ erkennen Sie den Bereich und ja, die Tabelle hat Überschriften. Nach einem OK öffnet sich der Query Editor. Hier ist der schon die Spalte mit der Überschrift Zeilenumbruch markiert; sollte eine andere Spalte diese Zeilenumbrüche enthalten, werden Sie diese statt der ersten markieren. Übrigens: Durch diese Aktion werden die Ursprungsdaten automatisch in eine Intelligente Tabelle umgewandelt.

Im Menü Start Gruppe Transformieren klicken Sie auf das Symbol Spalte teilen. Im Dropdown-Menü wählen Sie dann den oberen Punkt Nach Trennzeichen:

Dieser Menüpunkt!

Dieser Menüpunkt!

Es tut sich nun dieses Dialogfenster auf:

Per Power Query eine Zelle in Spalten teilen

Per Power Query eine Zelle in Spalten teilen

Folgende Schritte stehen nun an:

  • Als erstes werden sie bei Trennzeichen statt der vorgegebenen Möglichkeit Komma die Auswahl –Benutzerdefiniert– treffen.
  • Dass Bei jedem Vorkommen des Trennzeichens geteilt werden soll, ist richtig so.
  • Ein Klick auf Erweiterte Optionen fragt sie nach der Spaltenzahl für Teilung. Hier ist eine 2 vorgegeben; die ersetzen sie durch die (eben gemerkte) Zahl 5.
  • Markieren Sie nun das Kästchen Mithilfe von Sonderzeichen teilen, öffnen Sie das DropDown Sonderzeichen einfügen und wählen Sie dort Zeilenvorschub aus. In das Textfeld unterhalb –Benutzerdefiniert– wird nun der symbolische Code für die Zeilenschaltung eingefügt.
  • Schließen Sie dieses Fenster durch Klicken auf OK.

Sie sehen, dass die Spalte Normaler Text automatisch um die erforderliche Anzahl der Spalten nach rechts verschoben worden ist. Die Überschriften für den Zeilenumbruch wurden durch numerische Werte ergänzt, damit jede Überschrift einmalig ist. Die Aufgabe ist damit gelöst.

Wenn Sie -aus welchen Gründen auch immer- im Tabellenblatt mit den Ursprungsdaten keine Hilfszelle zur Berechnung der künftigen Spalten anlegen können, dann bleibt in nur der Weg übrig, die maximale Anzahl von Zeilenumbrüchen je Zelle in der Spalte zu schätzen und noch einen Sicherheit-Puffer dazu zu geben. Diese Zahlen geben Sie dann in dem Dialogfenster ein. Anschließend sollten mehrere leere Spalten in der Abfrage zu finden sein. Diese können Sie dann von Hand löschen.

Hinweis: (13.09.2017)
Power Query wird ständig (im Hintergrund) aktualisiert. So konnte ich heute feststellen, dass Power Query offesichtlich die Zahl der zu trennenden Elemente (bei Zeilenschaltung als Trenner) alleine korrekt erkennt. Wieder ein sehr positiver Schritt nach vorne! Als Beispiel zum üben habe ich Ihnen diese Mappe (allerdings ohne Ergebnis) zur Verfügung gestellt.

▲ nach oben …

Auch wenn Ihnen Power Query jetzt erst einmal etwas fremd vorkommt, es lohnt sich. Fügen Sie doch in den Quelldaten einfach einmal eine oder zwei weitere Zeilen bzw. Zellen am Ende der Liste ein. Ob die nun mit oder ohne Zeilenumbruch sind, spielt nicht die große Rolle. Wechseln Sie wieder zum Tabellenblatt mit dem Ergebnis der Abfrage, Menü Abfrage und in der Gruppe Laden ein Klick auf Aktualisieren. Umgehend werden hier alle Daten aktualisiert, mit einem einzigen Mausklick.

Wenn Sie jetzt noch etwas Lust zum Üben haben, dann empfehle ich Ihnen, diese Datei herunterzuladen und den gleichen Vorgang damit durchzuführen. Das Prinzip ist gleich, die Daten sind etwas anders. - Was hier für Sie vielleicht noch interessant sein könnte, sind die Formeln im Bereich B2:B12 und B13 sowie C2:C12. Spalte2 und Spalte3 sind im Grunde genommen nur Hilfsspalten, wo mit jeweils einer anderen Formel (in Spalte2) oder der gleichen in Spalte3 die maximale Anzahl der künftigen zu nutzenden Spalten berechnet worden ist. Hier ist ihrem Forscherdrang freie Hand gegeben, die interne Excel-Hilfe ist dazu recht gut.

Anmerkung: Eigentlich reicht in Spalte C eine einzige Formel, beispielsweise in C2. Aber da die Formeln nach der automatischen Umwandlung in eine Intelligente Tabelle eingetragen worden sind, hat Excel sie komplett nach unten ausgefüllt. Und B13 ist auch durch eine Funktionalität der Liste berechnet worden.

▲ nach oben …

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