In dieser Datei sind in Spalte A ver­schiedene Zellen, wo eine mehrzeilige Darstel­lung dadurch erre­icht wurde, dass eine manuelle Zeilen­schal­tung (mit­tels AltReturn) erzwun­gen wurde. Das Ziel ist nun, jede dieser sicht­baren Zeilen ein­er Zelle in die entsprechende Anzahl von Spal­ten der gle­ichen Zeile aufzuteilen.

Mit Bordmitteln

Der auf den ersten Blick ein­fach­ste Weg ist bere­its im Stan­dard-Excel inte­gri­ert. Das Stich­wort heißt: Text in Spal­ten. Wech­seln Sie zum Menü Dat­en; dort erken­nen sie in der Gruppe Daten­tools den Menüpunkt Text in Spal­ten. Bevor sie jedoch auf dieses Sym­bol Klick­en, wer­den Sie die Spalte A entwed­er kom­plett oder nur den Daten­bere­ich markieren. Anschließend ein Klick auf das Sym­bol und es zeigt sich das Dialogfen­ster des Tex­tkon­vertierung-Assis­ten­ten mit dem ersten von drei Schrit­ten:

Der Text-In-Spal­ten Assis­tent, erstes Fen­ster

Zugegeben, in dieser Vorschau ist nicht erkennbar, ob in der einzel­nen Zelle nun Zeilen­schal­tun­gen enthal­ten sind oder nicht. Hier ist es nur wichtig dass sie wis­sen, es sind gewollte Zeilenum­brüche in den Zellen enthal­ten. 

Darum ist die Vorauswahl Getren­nt die richtige. Ein Klick auf die Schalt­fläche Weit­er > bringt sie zum näch­sten Fen­ster, wo sich die Dat­en schon etwas anders darstellen:

Der zweite Schritt erscheint zu Beginn irri­tierend

Es ist nur jew­eils die erste sicht­bare Zeile jed­er einzel­nen Zelle in der Vorschau dargestellt. Die danach fol­gen­den „Zeilen” sind offen­sichtlich „unter­schla­gen”. Klick­en Sie nun in der Gruppe Trennze­ichen auf die Auswahl Andere: und geben Sie in dem Textfen­ster daneben fol­gende Zif­fern von der Zehn­er­tas­tatur bei gedrück­ter Taste Alt nacheinan­der so ein: 010. (Bei älteren Note­books ohne Zehn­er­tas­tatur müssen Sie die Taste Fn bemühen.) In dem kleinen Textfen­ster wer­den sie nur erken­nen, dass der Cur­sor kein Strich mehr ist son­dern ein blink­ender Punkt. In der Daten­vorschau allerd­ings sehen sie sofort, dass diese Aktion die richtige war; die Zeilen der einzel­nen Zelle sind in Spal­ten aufgeteilt. Auf zum näch­sten Schritt mit Weit­er >.

Im let­zten Schritt des Assis­ten­ten wer­den sie zu Beginn über­prüfen, ob alle Spal­ten auch das richtige For­mat haben und ob diese importiert wer­den sollen oder auch nicht. Das stellt sich nun so dar:

Der dritte und (vor­läu­fig) let­zte Schritt im Dia­log

 Etwas anderes ist hier aber wichtig: Wenn Sie jet­zt auf Fer­tig stellen Klick­en, käme ein Dia­log der sie darauf hin­weist, dass es in Spal­teB schon Dat­en gibt und Sie wer­den gefragt, ob sie diese erset­zen wollen. Im Nor­mal­fall wird Ihnen daran nicht gele­gen sein, darum ein Klick auf Abbrechen. Danach hat sich erst ein­mal gar nichts getan. Rufen Sie den Vor­gang noch ein­mal auf und sie wer­den erken­nen, dass Excel ihre bish­eri­gen Ein­stel­lun­gen über­nom­men hat. Im drit­ten Schritt geben Sie bei Ziel­bere­ich: beispiel­sweise die Zelle D2 als Ziel an. Dadurch haben sie die Dat­en von der Tabelle durch eine Leerspalte getren­nt.

Prinzip­iell sieht das jet­zt schon sehr gut aus. Fol­gende Schritte sind durchzuführen:

• Kopieren Sie die Über­schrift aus A1 nach D1,
• ver­schieben sie die Dat­en der Spalte B in der die let­zte Spalte mit den Dat­en (hier: Spalte I)
• und löschen Sie die Spal­ten A:C. Das war’s, die Auf­gabe ist damit in Plain Excel erledigt.

▲ nach oben …

Mit VBA / Makros

wenn dieser Vor­gang öfter ein­mal durchge­führt wer­den soll, dann bietet sich die Ver­wen­dung von VBA (Makros) an. Mit ein­er kleinen Rou­tine kön­nen Sie das recht ein­fach erledi­gen. Der fol­gende Code schreibt die trans­portierten Zellen in ein Array und fügt dieses an der ursprünglichen Posi­tion der auszuw­er­tenden Dat­en ein. In jedem Fall wer­den so viele Spal­ten einge­fügt, wie die höch­ste Anzahl von „Zeilen” in den einzel­nen Zellen sind. Selb­st wenn rechts noch genü­gend Spal­ten frei wären, wer­den eventuelle belegte Spal­ten um dieser Anzahl nach rechts ver­schoben. 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 mein­er Sicht ein­fach­ste und am ehesten zukun­ftsweisende Lösung ist der Ein­satz von Pow­er Query. Voraus­set­zung ist natür­lich, dass sie eine Excel-Ver­sion ab 2010, bess­er 2013 oder sog­ar >=2016 haben. Vorher ste­ht Ihnen das Pow­er Query bzw. Abrufen und trans­formieren nicht zur Ver­fü­gung.

Etwas Vorar­beit ist für diese Auf­gabe, Lösung mit PQ aber ange­sagt. Für die spätere Auswer­tung in Pow­er Query ist es hil­fre­ich, wenn sie wis­sen, um wie viel Spal­ten es sich kün­ftig han­delt, wenn die Zeilen ges­plit­tet und transponiert wor­den sind. Tra­gen 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 son­st mit mit einem ein­fachen Return son­dern per StrgShiftReturn, da es sich um eine soge­nan­nte Matrix Formel han­delt. Hin­weis: Sie wer­den erken­nen, dass Sie diese Formel auf die Spalte A bezieht; sollte eine andere Spalte diese Zeilenum­brüche enthal­ten, müssen sie an zwei Stellen in der Formel den Bere­ich anpassen. – In der Beispiel-Datei erhal­ten Sie 5 als Ergeb­nis. Merken Sie sich bitte diese Zahl, sie wer­den sie gle­ich brauchen.

Klick­en Sie nun irgend­wo in den Daten­bere­ich und wählen Sie in Excel ²⁰¹⁰⁄₁₃ den Menüpunkt Pow­er Query, in Excel 2016 den Menüpunkt Dat­en. Dann ein Klick auf Aus Tabelle (Von Tabelle) und Excel fragt Sie, ob der Bere­ich kor­rekt ist und ob die Tabelle Über­schriften hat. Anhand der „Ameisenkolonne“ erken­nen Sie den Bere­ich und ja, die Tabelle hat Über­schriften. Nach einem OK öffnet sich der Query Edi­tor. Hier ist der schon die Spalte mit der Über­schrift Zeilenum­bruch markiert; sollte eine andere Spalte diese Zeilenum­brüche enthal­ten, wer­den Sie diese statt der ersten markieren. Übri­gens: Durch diese Aktion wer­den die Ursprungs­dat­en automa­tisch in eine Intel­li­gente Tabelle umge­wan­delt.

Im Menü Start Gruppe Trans­formieren Klick­en Sie auf das Sym­bol Spalte teilen. Im Drop­down-Menü wählen Sie dann den oberen Punkt Nach Trennze­ichen:

Dieser Menüpunkt!

Es tut sich nun dieses Dialogfen­ster auf:

Per Pow­er Query eine Zelle in Spal­ten teilen

Fol­gende Schritte ste­hen nun an:

• Als erstes wer­den sie bei Trennze­ichen statt der vorgegebe­nen Möglichkeit Kom­ma die Auswahl –Benutzerdefiniert– tre­f­fen.
• Dass Bei jedem Vorkom­men des Trennze­ichens geteilt wer­den soll, ist richtig so.
• Ein Klick auf Erweit­erte Optio­nen fragt sie nach der Spal­tenzahl für Teilung. Hier ist eine 2 vorgegeben; die erset­zen sie durch die (eben gemerk­te) Zahl 5.
• Markieren Sie nun das Kästchen Mith­il­fe von Son­derze­ichen teilen, öff­nen Sie das Drop­Down Son­derze­ichen ein­fü­gen und wählen Sie dort Zeilen­vorschub aus. In das Textfeld unter­halb –Benutzerdefiniert– wird nun der sym­bol­is­che Code für die Zeilen­schal­tung einge­fügt.
• Schließen Sie dieses Fen­ster durch Klick­en auf OK.

Sie sehen, dass die Spalte Nor­maler Text automa­tisch um die erforder­liche Anzahl der Spal­ten nach rechts ver­schoben wor­den ist. Die Über­schriften für den Zeilenum­bruch wur­den durch numerische Werte ergänzt, damit jede Über­schrift ein­ma­lig ist. Die Auf­gabe ist damit gelöst.

Wenn Sie ‑aus welchen Grün­den auch immer- im Tabel­len­blatt mit den Ursprungs­dat­en keine Hil­f­szelle zur Berech­nung der kün­fti­gen Spal­ten anle­gen kön­nen, dann bleibt in nur der Weg übrig, die max­i­male Anzahl von Zeilenum­brüchen je Zelle in der Spalte zu schätzen und noch einen Sicher­heit-Puffer dazu zu geben. Diese Zahlen geben Sie dann in dem Dialogfen­ster ein. Anschließend soll­ten mehrere leere Spal­ten in der Abfrage zu find­en sein. Diese kön­nen Sie dann von Hand löschen.

Hin­weis: (13.09.2017)
Pow­er Query wird ständig (im Hin­ter­grund) aktu­al­isiert. So kon­nte ich heute fest­stellen, dass Pow­er Query offesichtlich die Zahl der zu tren­nen­den Ele­mente (bei Zeilen­schal­tung als Tren­ner) alleine kor­rekt erken­nt. Wieder ein sehr pos­i­tiv­er Schritt nach vorne! Als Beispiel zum üben habe ich Ihnen diese Mappe (allerd­ings ohne Ergeb­nis) zur Ver­fü­gung gestellt.

▲ nach oben …

Auch wenn Ihnen Pow­er Query jet­zt erst ein­mal etwas fremd vorkommt, es lohnt sich. Fügen Sie doch in den Quell­dat­en ein­fach ein­mal eine oder zwei weit­ere Zeilen bzw. Zellen am Ende der Liste ein. Ob die nun mit oder ohne Zeilenum­bruch sind, spielt nicht die große Rolle. Wech­seln Sie wieder zum Tabel­len­blatt mit dem Ergeb­nis der Abfrage, Menü Abfrage und in der Gruppe Laden ein Klick auf Aktu­al­isieren. Umge­hend wer­den hier alle Dat­en aktu­al­isiert, mit einem einzi­gen Mausklick.

Wenn Sie jet­zt noch etwas Lust zum Üben haben, dann empfehle ich Ihnen, diese Datei herun­terzu­laden und den gle­ichen Vor­gang damit durchzuführen. Das Prinzip ist gle­ich, die Dat­en sind etwas anders. – Was hier für Sie vielle­icht noch inter­es­sant sein kön­nte, sind die Formeln im Bere­ich B2:B12 und B13 sowie C2:C12. Spalte2 und Spalte3 sind im Grunde genom­men nur Hil­f­ss­pal­ten, wo mit jew­eils ein­er anderen Formel (in Spalte2) oder der gle­ichen in Spalte3 die max­i­male Anzahl der kün­fti­gen zu nutzen­den Spal­ten berech­net wor­den ist. Hier ist ihrem Forscher­drang freie Hand gegeben, die interne Excel-Hil­fe ist dazu recht gut.

Anmerkung: Eigentlich reicht in Spalte C eine einzige Formel, beispiel­sweise in C2. Aber da die Formeln nach der automa­tis­chen Umwand­lung in eine Intel­li­gente Tabelle einge­tra­gen wor­den sind, hat Excel sie kom­plett nach unten aus­ge­füllt. Und B13 ist auch durch eine Funk­tion­al­ität der Liste berech­net wor­den.

▲ nach oben …