Tutorials

Excel VBA: Text in Spalten (Text to Columns) automatisch anwenden

Melden

Dieses Tutorial zeigt dir, wie du die Excel-Funktion „Text in Spalten“ (Text to Columns) mithilfe von VBA automatisierst. Das ist besonders hilfreich, wenn du regelmäßig CSV-Daten oder exportierte Listen bereinigen musst.

Die Methode TextToColumns in VBA ist das Gegenstück zum Assistenten, den du in Excel unter dem Reiter Daten > Text in Spalten findest.

1. Die grundlegende Syntax

Der Befehl wird immer auf ein Range-Objekt (meist eine Spalte) angewendet.

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False

Die wichtigsten Parameter erklärt:

  • Destination: Wo sollen die getrennten Daten beginnen? (Meist die erste Zelle der Quellspalte).
  • DataType: xlDelimited (getrennt durch Zeichen) oder xlFixedWidth (feste Breite).
  • Semicolon, Comma, Space, Tab: Setze diese auf True, je nachdem, welches Trennzeichen deine Daten nutzen.
  • FieldInfo: Ein Array, das bestimmt, welches Datenformat die neuen Spalten haben (Text, Datum, Zahl).

2. Beispiel: Daten mit Semikolon trennen

In Deutschland ist das Semikolon (;) das Standard-Trennzeichen für CSV-Dateien. Hier ist ein fertiges Makro:

Sub SpaltenTrennenSemikolon()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Tabelle1") ' Name deiner Tabelle anpassen

    ' Wir nehmen an, die Daten stehen in Spalte A
    ws.Columns("A:A").TextToColumns _
        Destination:=ws.Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        Semicolon:=True, _
        Comma:=False, _
        Tab:=False, _
        Space:=False, _
        Other:=False

    MsgBox "Daten wurden erfolgreich getrennt!", vbInformation
End Sub

3. Fortgeschritten: Datentypen festlegen (FieldInfo)

Ein häufiges Problem: Excel wandelt Zahlen mit führenden Nullen (z. B. Postleitzahlen wie "01234") automatisch in Zahlen um und löscht die Null. Mit FieldInfo kannst du das verhindern, indem du die Spalte als Text formatierst.

  • 1 = Standard
  • 2 = Text
  • 4 = Datum (MTJ)

Beispiel für 3 Spalten (Spalte 1: Standard, Spalte 2: Text, Spalte 3: Text):

Sub SpaltenTrennenMitFormat()
    Columns("A:A").TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        Semicolon:=True, _
        FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2))
End Sub

Erklärung: Array(2, 2) bedeutet: Die 2. Spalte der Ergebnisdaten soll Datentyp 2 (Text) sein.

4. Daten mit fester Breite trennen

Wenn deine Daten keine Trennzeichen haben, sondern immer an der gleichen Stelle umbrechen (z.B. Zeichen 1-5 ist ID, 6-20 ist Name):

Sub FesteBreiteTrennen()
    Range("A:A").TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(20, 1))
End Sub

Hier gibt die erste Zahl im Array an, bei welchem Zeichen der Schnitt erfolgt.

5. Anleitung: So baust du den Code ein

  1. Öffne dein Excel-Dokument.
  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  3. Gehe im Menü auf Einfügen > Modul.
  4. Kopiere einen der obigen Codes in das leere Fenster.
  5. Schließe den VBA-Editor.
  6. Drücke ALT + F8, wähle das Makro aus und klicke auf Ausführen.

Tipps für die Praxis

  • Zellen leeren: Wenn rechts neben deiner Datenspalte bereits Daten stehen, werden diese überschrieben. Stelle sicher, dass genügend leere Spalten vorhanden sind.
  • Dynamische Range: Statt Columns("A:A") kannst du auch Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row) nutzen, um nur den tatsächlich belegten Bereich zu bearbeiten.
  • Aufzeichnung nutzen: Wenn du ein spezielles Trennzeichen hast, kannst du den Makro-Rekorder starten, die Funktion manuell ausführen und dir den erzeugten Code ansehen. Er ist oft etwas langatmig, liefert aber die exakten Parameter.
0