Immer wieder tauchen beispiel­sweise in Foren Fra­gen auf, wie mehrzeilige Zellen in mehrere Zellen aufgeteilt wer­den kön­nen. Dabei sind nicht Zellen gemeint, die wegen der For­matierung mehrzeilig sind, son­dern nur jene, wo durch min­destens eine Zeilen­schal­tung (Alt+Eingabe) mehrere Zeilen in dieser einen Zelle, beispiel­sweise A1 sind. Eine kleine entsprechende kleine Mus­ter­datei kön­nen (und soll­ten) Sie hier herun­ter­laden. Es wird ver­mut­lich bei Ihnen ein Hin­weis erscheinen, dass die Datei wegen enthal­tener Makros nicht sich­er ist. Wie Sie damit umge­hen, über­lasse ich Ihnen. Es ist auss­chließlich der weit­er unten gelis­tete Code enthal­ten. Auf Wun­sch senden wir Ihnen aber auch gerne ein File ohne enthal­tene Makros zu.

Die Dat­en sind als Intel­li­gente Tabelle/Liste ein­gerichtet bzw. for­matiert. Das hat einige Vorteile. Ins­beson­dere den, dass Formeln automa­tisch in der gesamten Spalte ergänzt wer­den. Außer­dem gibt es die Möglichkeit, eine Zusam­men­fas­sung einzufü­gen und dort aggregierte Werte anzeigen zu lassen. Weit­er unten dazu mehr … Die Über­schrift der Liste ist nicht ger­ade eine gedankliche Explo­sion, aber da sie durch Excel selb­st einge­fügt wor­den ist, reicht das vol­lkom­men aus.

Die Lösungsvorschläge führen alle zum Ziel. Welch­er für Sie der beste, der opti­mal­ste ist hängt von Ihren Fähigkeit­en in Sachen „Dazuler­nen” ab, von Ihrem Geschmack, Ihrer Excel-Ver­sion, …

▲ nach oben …

Ohne Hilfsspalte (1)

Solange sich die auszuw­er­tenden Dat­en nur in der let­zten Spalte befind­en und diese dann entsprechend aufgeteilt wer­den sollen, bietet Excel eine Meth­ode an, die ein­fach aber präg­nant ist:

• Markieren Sie den Bere­ich der Dat­en oder (not­falls) die ganze Spalte. Bei ein­er Intel­li­gen­ten Tabelle (wie hier) ist es am ein­fach­sten, eine Zelle zu markieren und dann StrgA.
• Menü Dat­en, Gruppe Daten­tools, Text in Spal­ten
• Daten­typ: Getren­nt, Weit­er >
• Trennze­ichen: Andere markieren
• In dem Textfeld daneben geben Sie fol­gen­des ein:
  • Alt (gedrückt hal­ten) 0 1 0
  • Dann erst Alt loslassen
• Auch wenn das Zeichen in dem Textfeld nicht direkt oder schlecht sicht­bar ist, im unteren Fen­ster­bere­ich sollte die Split­tung sicht­bar sein:

Der zweite Schritt beim teilen der Dat­en

• Das reicht schon, darum auf Fer­tig Stellen Klick­en.

Das Ziel ist erre­icht. Aber wie gesagt, es funk­tion­iert nur so ein­fach, wenn in der recht­en Nach­barspalte keine Dat­en ste­hen. Falls doch, wäre ein denkbar­er Umweg, dass Sie nach dem zweit­en Schritt auf Weit­er > Klick­en und im drit­ten Schritt ändern Sie den vorgegebe­nen Ziel­bere­ich $A$2 beispiel­sweise auf D2:

Hier bitte das neue Zeil eingeben

Wie schon erwäh­nt: Wichtig ist nur, dass in den Folges­pal­ten keine Dat­en ste­hen. Erken­nt Excel, dass dort irgend­wo doch Dat­en oder Formeln enthal­ten sind, wird eine entsprechende Warn­mel­dung mit der Möglichkeit des Abbruchs aus­gegeben.

▲ nach oben …

Mit Hilfsspalte

Vielle­icht gelingt es Ihnen ‑aus welchen Grün­den auch immer- nicht, mit der Zehn­er­tas­tatur zu hantieren. Das ist bei älteren Note­books, Net­books, etc. mitunter etwas prob­lema­tisch. Dann ist eine Hil­f­ss­palte gewiss ein ein­fach­er Weg. Tra­gen Sie in B2 fol­gende Formel ein:
=WECHSELN(A2; ZEICHEN(10); "|")
Jede Zeilen­schal­tung ZEICHEN(10) wird durch das Pipe-Sym­bol (|) erset­zt. Und jet­zt ist es recht ein­fach, die Zellen über Text in Spal­ten zu split­ten. Das Pipe-Sym­bol kön­nen Sie ja auf jed­er Tas­tatur per Alt­Gr< eingeben.

▲ nach oben …

Ohne Hilfsspalte (2)

Grund­sät­zliche Anmerkung: Sie müssen Excel (Win­dows) ab Ver­sion 2010 ein­set­zen und sich eventuell bei Microsoft ein Add-In herun­ter­laden: Pow­er Query. Mehr dazu kön­nen Sie hier im Blog nach­le­sen.

Aus­gangspunkt ist wiederum die (unbear­beit­ete) Tabelle, welche Sie oben schon ken­nen gel­ernt haben. Hier noch ein­mal der Link, damit Sie „sauber” starten kön­nen. 😎 Je nach Excel-Ver­sion ist es nun leicht unter­schiedlich, wie Sie die ersten Schritte vol­lziehen. Die Dat­en der Tabelle müssen in den Abfrage-Edi­tor geladen wer­den …

▲ nach oben …

Excel-Versionen ²⁰¹⁰⁄₂₀₁₃

Die aktive Zelle ist auf jeden Fall eine der Zellen, die aus­gew­ertet wer­den sollen. Also in der Intel­li­gen­ten Tabelle.

• Menü Pow­er Query, Gruppe Excel-Dat­en und hier der einzige Punkt: Von Tabelle.
• Bestäti­gen Sie im Dialogfen­ster den vorgegebe­nen Bere­ich oder kor­rigieren Sie ihn und acht­en Sie darauf, dass das Häkchen bei Meine Tabelle hat Über­schriften geset­zt ist.

Hin­weis: Es ist dur­chaus möglich (sog­ar wahrschein­lich), dass keine Nach­frage kommt und der als Tabelle for­matierte Bere­ich direkt und ohne Nach­frage über­nom­men wird.

▲ nach oben …

Excel-Version 2016

Die aktive Zelle ist auf jeden Fall eine der Zellen, die aus­gew­ertet wer­den sollen. Also in der Intel­li­gen­ten Tabelle.

• Menü Dat­en, Gruppe Abrufen und trans­formieren
• Menüpunkt Aus Tabelle

… und automa­tisch wird die aktuelle Tabelle in den Edi­tor geladen.

▲ nach oben …

Alle Versionen (mit Power Query)

Die Dat­en sind nun in den Abfrage-Edi­tor geladen. In einem neuen Fen­ster stellt sich das so dar:

Der Abfrage-Edi­tor direkt nach dem laden

Auch hier gibt es ein Menü, ein Menüband (mit Symbolen/Ribbons) und Grup­pen. Aus­gewählt ist das Menü Start, dabei bleibt es auch. Und Sie erken­nen vielle­icht, dass die erste (und einzige) Spalte markiert ist. Den­noch ist ein Klick in die Über­schrift hil­fre­ich, um in jedem Fall die ganze Spalte auszuwählen. Das erken­nen Sie dann auch daran, dass die Über­schrift dann ein dun­kleres Grün annimmt.

In der Gruppe Trans­formieren Klick­en Sie auf das Sym­bol Spalte teilen. Das entspricht in etwa der Funk­tion­al­ität Text in Spal­ten aus dem nor­malen Excel. Im Drop­Down gibt es nur zwei Auswahlmöglichkeit­en, Sie wählen natür­lich die obere:

Auswahl: Nach Trennze­ichen

Die direk­te Eingabe des Trennze­ichens über die Zehn­er­tas­tatur (das ken­nen Sie ja schon) geht/funktioniert hier nicht. Ich musste da auch einige Zeit exper­i­men­tieren, um das festzustellen. Den­noch wer­den Sie nach dem Klick auf Nach Trennze­ichen solch ein Fen­ster sehen:

Aus­gangslage des Tren­nen-Dialo­ga

In der Drop­Down-Auswahl, wo als aktueller Wert Kom­ma angezeigt wird, wählen Sie den let­zten angezeigten Wert, –Benutzerdefiniert–. Direkt darunter erscheint nun ein neues Textfeld, wo das Trennze­ichen eingegeben wer­den soll. Das lassen Sie bitte leer. Bei der Auswahl Teilen ist auch Bei jedem Vorkom­men des Trennze­ichens genau richtig.

Ein Klick auf Erweit­erte Optio­nen öffnet eine neue Auswahlmöglichkeit:

Die erweit­erten Möglichkeit­en

Und hier heißt es erst ein­mal STOP! PQ (Pow­er Query) möchte die Spal­tenzahl für die Teilung wis­sen. Hier wer­den zwar 4 vorgeschla­gen (oft auch nur 2), seinen Sie aber gewiss, das stimmt für diese Dat­en nicht. Irgend­wie müssen Sie also nun noch her­aus­bekom­men, wie die höch­ste Anzahl an Zeilen­schal­tun­gen in ein­er der Zellen. Klar, Sie kön­nten es sich ein­fach machen und „frech” 99 dort hinein schreiben. So viele Trennze­ichen sind es garantiert nicht. Aber dann hät­ten Sie im Anschluss die Arbeit, die über­flüs­si­gen Spal­ten zu löschen. Und wenn das mehrere hun­dert oder sog­ar hun­dert­tausend Zeilen sind, dann ist das ein so gut wie unmöglich­es Unter­fan­gen.

Darum brechen Sie hier erst ein­mal ab. Ich klicke dazu in der Regel ein­fach auf die Schalt­fläche Abbrechen in dem inneren Fen­ster und dann auf den Menüpunkt Datei | Ver­w­er­fen und schließen. Zugegeben, alles was Sie bis jet­zt im Edi­tor gemacht haben ist ver­loren, aber dur­chaus rasch wieder hergestellt. Hier habe ich Sie erst ein­mal in die Irre geführt, damit Sie das Ganze etwas üben kön­nen.  😉 

▲ nach oben …

Nun doch mit Hilfsspalte  😉 

Jet­zt sind Sie wieder in der Tabelle und kön­nen erkun­den, wie viele Zeilen­schal­tun­gen denn max­i­mal in der Spalte A in den einzel­nen Zellen enthal­ten sind. Denn daraus ergibt sich ja, wie viele Spal­ten bei der Auswer­tung durch PQ ein­gerichtet wer­den sollen. Bei diesen weni­gen Zeilen kön­nten Sie ja rasch mal zählen, oder? Ken­nen Sie das Sprich­wort: „Dicht daneben ist auch vor­bei!”? Sie wür­den glatt daneben liegen, denn die Zeile 11 beste­ht aus genau 1 Zeile. (Es hat mich richtig viel Mühe gekostet, so viele Worte mir halb­wegs Sinn da hinein zu bekom­men.) Übri­gens: Das war eben auch schon im Abfrage-Edi­tor ganz gut zu sehen 🙂 . Das For­mat der Zelle sorgt nur dafür, dass mehrere Zeilen sicht­bar sind. Dort ist also kein einziger Zeilenum­bruch im Sinne des ZEICHEN(10) drin.

Also muss ein ander­er Weg beschrit­ten wer­den. Das geht ide­al­er­weise über zwei Hil­f­ss­pal­ten oder zumin­d­est 1 Hil­f­ss­palte und eine Ergeb­nis-Zelle. Tra­gen Sie neben der Liste in B2 diese Formel ein:
=LÄNGE(A2)-LÄNGE(WECHSELN(A2; ZEICHEN(10);))+1
Damit berech­nen Sie die Zahl der enthal­te­nen Zeilen­schal­tun­gen (entspricht ja ZEICHEN(10) und addieren 1. Warum 1 mehr? Nun ja, A1 enthält ja keine Zeilen­schal­tung, das Ergeb­nis ist also null. Da ich aber die Zahl der benötigten Spal­ten für den Ein­trag brauche, muss natür­lich noch 1 addiert wer­den. Dank der Lis­ten-Funk­tion­al­ität wird die Formel automa­tisch bis zur let­zten Zeile herunter kopiert.

Selb­stre­dend kön­nte ich jet­zt den höch­sten Wert durch Augen­schein her­aus­suchen, aber das ist wiederum bei großen Tabellen ein­fach kon­trapro­duk­tiv. Darum beispiel­sweise in eine zweite Hil­f­ss­palte, also in C2 diese Formel: =MAX(B:B); diese Formel kön­nten Sie auch in eine geson­derte Zelle schreiben. Das Ergeb­nis sind hier 5 Spal­ten, die benötigt wer­den. Und den Wert merken Sie sich.

Aber Sie haben doch eine Intel­li­gente Tabelle! Nutzen Sie die Möglichkeit­en, die sich Ihnen da bieten. Die erste Hil­f­ss­palte enthält ja die berech­nete Anzahl der benötigten Spal­ten. Eine zweite Hil­f­ss­palte oder extra Ergeb­nis-Zelle ist über­flüs­sig, kann also gelöscht wer­den. Recht­sklick in Spalte B, Tabelle | Ergeb­niszeile und automa­tisch wird am Ende eine neue Zeile einge­fügt, wo in Spalte B die Summe berech­net wird. Ein Klick auf den Drop­Down-Pfeil  rechts der Zelle und Sie wählen Max­i­mum aus. Sie ahnen es, das Ergeb­nis ist 5. Damit diese Zeile nicht in den Edi­tor über­nom­men wird, erst natür­lich das Ergeb­nis merken und dann noch ein­mal Tabelle | Ergeb­niszeile um diese Zeile wieder zu löschen.

Jet­zt gehen Sie genau so vor, wie zuvor beschrieben. Erstellen Sie aus der Tabelle eine Abfrage im Abfrage-Edi­tor. Die sieht nun wegen der ein bzw. zwei Spal­ten mehr etwas anders aus, aber das ist gar nicht ein­mal so schlecht. Denn dadurch ist da der Umstand gegeben, dass direkt neben der zu teilen­den Spalte noch weit­ere Werte ste­hen. Sie erin­nern sich, dass so etwas in Plain Excel nicht so ide­al war.

Wiederum die markierte Spalte Teilen, und zwar nach Trennze­ichen. Vor­sicht­shal­ber die Über­schrift der ersten Spalte anklick­en. Auch –Benutzerdefiniert– ist kor­rekt und Sie aktivieren Erweit­erten Optio­nen. Als Spal­tenzahl für die Teilung tra­gen Sie nun 5 ein, was Sie ja eben berech­net hat­ten und was übri­gens auch im Edi­tor zu sehen ist, falls Sie die Max-Berech­nung inner­halb der zweit­en Hil­f­ss­palte gemacht haben.

Set­zen Sie nun das Häkchen ganz unten im inneren Fen­ster bei Mith­il­fe von Son­derze­ichen teilen. Nun kön­nen Sie im darun­ter­liegen­den Drop­Down den Zeilen­vorschub per Mausklick auswählen. Aber bitte, ein Mausklick reicht. Auch wenn es nicht sofort auf­fällt, in der bis­lang leeren Zeile unter­halb –Benutzerdefiniert– ist ein Code einge­tra­gen wor­den, der stel­lvertre­tend für den Zeilenum­bruch ste­ht. Später kön­nen Sie diese Zeichen­folge auch direkt ohne Umwege in das Feld eingeben.

Jet­zt OK und es wer­den automa­tisch an der kor­rek­ten Posi­tion vier weit­ere Spal­ten einge­fügt und mit den Werten der einzel­nen „logis­chen” Zeilen gefüllt. Dort, wo weniger Zeilen­schal­tun­gen waren, ste­ht der Wert null im Feld der entsprechen­den Spalte.

Bleibt eigentlich nur noch, die let­zte oder die let­zten bei­den Hil­f­ss­pal­ten zu markieren über das Sym­bol oder Recht­sklick und dann im Kon­textmenü Spal­ten ent­fer­nen zu löschen. – Das allererste Sym­bol oben im Bild­bere­ich anklick­en und der Edi­tor wird geschlossen. In einem neuen Arbeits­blatt wird eine Tabelle erstellt, die genau den Forderun­gen entspricht.

Zugegeben, anfangs etwas Aufwand. Vor allen Din­gen, wenn Sie noch nie Kon­takt zu Pow­er Query hat­ten. Aber der riesige Vorteil: Wenn sich die Basis­dat­en ändern, dann genügt ein Klick auf Abfrage | Aktu­al­isieren und dann ist die Ergeb­nis-Tabelle auf dem neuesten Stand. Ach ja, und nicht eine Zeile VBA-Code (den ich übri­gens 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 ken­nen gel­ernt, wie Sie zum Ziel kom­men. Darauf auf­bauend wer­den Sie gewiss auch Ihr per­sön­lich­es Prob­lem lösen kön­nen.