Text in Spalten - Inhalte von einer Spalte in mehrere aufteilen - Excel Anleitung

Beispielsituation:

In einer Tabelle finden Sie eine Spalte, in der die Adressen von bestimmten Kunden gespeichert sind. Die Zellen enthalten die Adressen in dem folgenden Format: "Postleitzahl, Ort, Straße Hausnummer". Sie benötigen für Ihre Aufgabe jedoch die Adressdaten getrennt voneinander.

Es gibt mindestens 2 Möglichkeiten, wie man die Werte von einer Spalte auf mehrere Spalten aufteilen kann, ohne dies per Hand erledigen zu müssen. Eine dieser Möglichkeiten wird in dieser Anleitung aufgezeigt.

Da sich die Anleitung auf Excel 2003 bezieht, kann es passieren, dass einige Beschreibungen bei Ihnen nicht zutreffend sind oder an anderer Stelle zu finden sind, falls Sie ein neueres oder alternatives Office verwenden.

Lösung über den Menüpunkt "Text in Spalten..."

Markieren Sie zuerst alle Zellen, die Sie auf mehrere Spalten aufteilen wollen. Und wählen Sie anschließend wie auf der Abbildung zu sehen, den Menüpunkt "Text in Spalten" aus. Achten Sie zuvor darauf, dass genügend freie Spalten neben der Ursprungsspalte zur Verfügung stehen, sonst werden u. U. andere Werte überschrieben.

Textkonvertierungs-Assistent

Nun erscheint der Textkonvertierungs-Assistent. Über den Assistenten kann bestimmt werden, wie die Daten aufgeteilt werden sollen. Da in dem Text die Werte mit einem Komma voneinander getrennt sind, wählt man hier am besten "Getrennt" aus und klickt auf "Weiter".

Im zweiten Schritt wählen wir statt dem "Tabstopp" das "Komma" als Trennzeichen aus. Wie man sieht, wird an den Stellen, wo sich das Komma befand, eine Linie eingefügt. Die Linie stellt die Spalten dar, so kann man vorab erkennen, was passiert.

Den dritten Schritt kann man häufig überspringen, jedoch macht es in diesem Fall Sinn, den Schritt ebenfalls auszuführen. Da es sich hier in der ersten Spalte um eine Postleitzahl handelt, kann es vorkommen, dass das erste Zeichen eine "0" ist. Würde man das Datenformat für diese Spalte auf "Standard" lassen, würde die "0" im Ergebnis verschwinden und man hätte an der Stelle eine falsche (4-Stellige) Postleitzahl. Wird das Datenformat auf "Text" gestellt, passiert dies nicht.

Nachdem im Textkonvertierungs-Assistenten die Schaltfläche "Fertig stellen" geklickt wurde, kann man direkt das Ergebnis betrachten. Wie man sieht, existiert hier der Ursprungstext nicht mehr, sondern nur noch die einzelnen Werte jeweils auf eigene Spalten verteilt. Auf den zweiten Blick sieht man, dass vor jedem Wert der Spalten "C" und "D" ein Leerzeichen steht, dies ist natürlich nicht erwünscht und erscheint, weil es vorher im Ursprungstext ebenfalls vorhanden war.

Unerwünschte Leerzeichen löschen

Indem man zwei weitere Spalten einfügt (Rechtsklick auf den Buchstaben der Spalte - "Zellen einfügen") kann man hier mit der Funktion "Glätten" die überflüssigen Leerzeichen entfernen, ohne die Leerzeichen zwischen den einzelnen Wörtern zu entfernen.

Die Funktion ist sehr leicht, man gibt in einer leeren Zelle einfach folgendes ein:

=GLÄTTEN(zu glättende Zelle), im Beispiel also =GLÄTTEN(C2) und in der nächsten Spalte =GLÄTTEN(D2).

Excel trägt dann den Inhalt der Zelle "C2" ein und löscht überflüssige Leerzeichen vor und nach dem eigentlichen Text. Die Formel kann einfach durch herunterziehen auf alle folgenden Zeilen angewendet werden.

Formeln löschen

Durch diesen Vorgang bestehen nun 2 überflüssige Spalten. Bevor diese gelöscht werden, sollten zuerst die Formeln aus den neuen Spalten entfernt werden. Dies geschieht wie folgt:

1. Spalten markieren
2. Rechtsklick auf der Markierung ausüben
3. Den Menüpunkt "Kopieren" anklicken
4. Einen weiteren Rechtsklick auf der Markierung ausüben
5. Den Menüpunkt "Inhalte einfügen" anklicken
6. Im erscheinenden Fenster den Punkt "Werte" im oberen Bereich auswählen
7. Das Fenster mit "OK" bestätigen
8. Spalte "C" und Spalte "D" löschen

Nun rücken die beiden neuen Spalten an die Stelle der alten Spalten.

Straße und Hausnummer trennen

Das Ergebnis bisher ist schon wesentlich verwaltbarer, als der Ursprungstext, aber die Straße und die Hausnummer stehen immer noch jeweils zusammen in einer Zelle. Um diese Werte ebenfalls voneinander zu trennen, wird an dieser Stelle wieder über den Menüpunkt "Text in Spalten" gearbeitet. Gehen Sie dazu vor, wie zu Beginn, wählen Sie aber bei Schritt 2 des Textkonvertierungs-Assistenten diesmal "Leerzeichen" aus. Den dritten Schritt können Sie hier nun überspringen.

Wie zu sehen, gibt es nun ein neues Problem. Da Straßennamen auch aus mehreren Wörtern bestehen können, stehen manche Hausnummern mit teilen des Straßennamens in einer Spalte.

Stellen Sie nun fest, wie viele Spalten durch die Konvertierung mit Werten belegt wurden und lassen für die weitere Bearbeitung eine Spalte frei. Dahinter folgen die Spalten für die Endgültigen Straßennamen und Hausnummern.

Straße und Hausnummer in Ihre eigene Spalte bringen

Für die Bewältigung dieser Aufgabe werden die Wenn-Funktion und die Verketten-Funktion benötigt.

Straßenname

Hier im Beispiel ist zu sehen, dass der Straßenname entweder ein oder zwei Wörter lang ist. Um das passende Ergebnis zu erzielen, reicht im Grunde die folgende Formel aus:

=WENN(F2="";D2;VERKETTEN(D2;" ";E2))

Um auch bei Straßennamen, die aus mehr als 2 Wörtern bestehen, erfolg zu haben, empfiehlt es sich eher eine Formel mit dem folgenden Aufbau zu verwenden:

=WENN(D2="";"";WENN(F2="";D2;WENN(G2="";VERKETTEN(D2;" ";E2))))

Diese Formel ist von der Logik her so aufgebaut, dass man sie immer erweitern kann, bis auf 7 Wenn Formeln.

Hausnummer

Da in dem Beispiel die Hausnummer lediglich in Spalte "E" oder "F" vorkommt, reicht es im Prinzip, die Formel wie folgt aufzubauen:

=WENN(F2<>"";F2;E2)

Aber auch an dieser Stelle kann es bei vielen Datensätzen vorkommen, dass die Hausnummer auch noch andere Spalten belegen kann, daher sollte man die Formel eher so aufbauen:

=WENN(G2<>"";G2;WENN(F2<>"";F2;E2))

Da eine Hausnummer in der Regel pro Zeile nur eine Spalte belegt, kann man sich an dieser Stelle von rechts an das erste gefüllte Feld herantasten und dieses auch ausgeben, genau das übernimmt die obige Formel.

Ergebnis

Nachdem auch hier die Formeln (wie weiter oben beschrieben) entfernt wurden, können Sie alle ungewünschten Spalten löschen und die Spalten nach Wunsch anordnen.