Die Forderung klingt recht einfach: Eine oder mehrere Zellen einer Spalte haben mehrzeilige Einträge, wo innerhalb der Zelle mehrere Zeilen durch AltEingabe erzeugt worden sind. Die Herausforderung ist nun, jede der einzelnen Zeilen in einer Zelle so zu separieren, dass mehrere (echte) Excel-Zeilen daraus entstehen.
Laden Sie diese Datei und sie erkennen in Tabelle1 unter der Überschrift Texte verschiedene Zellen mit unterschiedlicher Anzahl von Zeilen. In Tabelle2 sind die gleichen Zellen enthalten, jedoch mit dem erschweren den Umstand, dass auch eine 2. Spalte mit in jeder Hinsicht einzeiligen Daten berücksichtigt werden muss.
Tabelle1 (nur 1 Spalte)
Hier erst einmal das Vorgehen in Tabelle1:
- Erstellen Sie aus den Daten eine Intelligente Tabelle (mit Überschriften). Sie könnten das aber auch Power Query beim Import überlassen, weil dieses Tool aus zu importieren den Daten stets eine Formatierte Tabelle generiert. Wegen der besserem Kontrollmöglichkeit erstelle ich jedoch stets selbst die Formatierte Tabelle. 😎
- Importieren Sie die Daten in den Abfrage-Editor per Daten | Aus Tabelle.
- Spalte teilen | Nach Trennzeichen und Power Query erkennt von sich aus, dass es ein benutzerdefiniertes Zeichen sein sollte, welches hier in das entsprechende Feld mit #(lf) eingefügt worden ist. Das lf ist übrigens das Synonym für Linefeed, Zeilenschaltung und entspricht dem ZEICHEN(10).
- Mit OK bestätigen.
- Spalte hinzufügen | Indexspalte
- Rechtsklick in die Überschrift Index und im Kontextmenü Andere Spalten entpivotieren wählen.
- Die Spalten Index und Attribut löschen.
- Schließen & laden.
Das Ergebnis steht nun als Tabelle in einem neuen Arbeitsblatt, bei Bedarf können oder sollten Sie natürlich noch die Überschrift anpassen und/oder die Liste an einen beliebigen Ort (auch eines anderen Blattes) verschieben.
Tabelle2 (mehrere Spalten)
Etwas anders ist das Vorgehen, wenn sie die Daten der Tabelle2 auf diese Weise verarbeiten wollen. Es stellt sich nämlich erst einmal die Frage, was mit den Werten in Spalte B geschehen soll. Ich gehe einmal davon aus, dass die Einträge jeder der neu erzeugten Zeilen zugeordnet, also dorthin kopiert werden sollen. Die Anleitung Schritt für Schritt:
- Laden Sie auch diese Auflistung in den Abfrage-Editor, natürlich hat auch diese Tabelle Überschriften.
- Geben Sie dieser Abfrage einen neuen Namen, beispielsweise Mehrere Spalten.
- Rechtsklick in die Überschrift Texte und auch hier: Spalte teilen | Nach Trennzeichen und selbstverständlich auch die Vorgaben mit OK bestätigen.
- Wie gehabt auch hier eine Index-Spalte hinzufügen.
- Menü Start, Gruppe Abfrage | Verwalten | Duplizieren, und es wird eine Kopie dieser Abfrage erstellt. Der Name der Abfrage ist identisch nur mit dem Zusatz (2).
- Geben Sie dieser Kopie gleich einen sinnvollen Namen, beispielsweise Spalte B.
- Wechseln sie wieder zur Abfrage Mehrere Spalten.
- Entfernen Sie die Spalte mehr Text (Vorsicht, nicht die Abfrage löschen!).
- Rechtsklick in die Überschrift der Spalte Index und andere Spalten entpivotieren.
- Jetzt (nur) die Spalte Attribut löschen.
- Wechseln Sie zur Abfrage Spalte B.
- Markieren Sie die beiden letzten Spalten mehr Text und Index.
- Rechtsklick in eine der beiden markierten Überschriften und Andere Spalten entfernen.
- Wechsel zur Abfrage Mehrere Spalten.
- Menü Start, Kombinieren | Abfragen zusammenführen
| Abfragen als neue Abfrage zusammenführen. - Wählen Sie im unteren (leeren) Dropdown den Eintrag Spalte B:
Auswahl der Abfrage „Spalte B”
- Markieren Sie im oberen sowie im unteren Block die Spalte Index und bestätigen Sie mit OK.
- Löschen Sie die Spalte Index.
- Klicken Sie nun in der neu erstellten Abfrage Merge1 in der Überschrift Spalte B rechts auf den Doppelpfeil
und entfernen Sie in den Kästchen Index sowie ganz unten bei Ursprünglichen Spaltennamen als Präfix das Häkchen. - Schließen Sie das Fenster.
- Löschen Sie die Spalte Index.
- Passen Sie bei Bedarf die beiden Überschriften noch an und anschließend Schließen & laden.
Damit ist die Aufgabe erfüllt. Zugegeben, etwas mehr Aufwand als vielleicht erwartet. Aber immerhin ist diese Vorgehensweise für viele Anwender noch transparenter als wenn eine mehr oder weniger komplexe Formel aus dem Internet kopiert wird oder gar selber erstellt werden muss.