TextToColumns in VBA

Forumthread: TextToColumns in VBA

11.06.2015 12:06:39

Senna

Hallo,
ich bin gerade auf ein Problem gestoßen, dass ich nicht ganz verstehe.
Wenn ich in einer Exceldatei manuell den Befehl "Text in Spalten" ausführe, dann macht Excel genau das was ich möchte. Jetzt möchte ich das aber automatisieren mit VBA.
In einer Spalte stehen Zahlen teilweise als Text formatiert. Dezimaltrennzeichen ist das Komma und Tausendertrennzeichen der Punkt. Nachdem ich mein Makro ausgeführt habe, ist das Dezimaltrennzeichen auf einmal der Punkt und die Zahl wird nicht als Zahl erkannt.
Hier mein Code:


Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E:E"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
True

Im System ist ebenfalls der Punkt als Tausender- und das Komma als Dezimaltrennzeichen eingestellt.
Vielleicht kann mir ja einer von euch sagen wo mein Fehler liegt.

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: TextToColumns in VBA

11.06.2015 15:22:21

fcs
AW: TextToColumns in VBA

12.06.2015 07:49:58

Senna

AW: TextToColumns in VBA

11.06.2015 15:22:21

fcs

Hallo Senna,
das ist kein Fehler von dir sondern eine Eigenart VBA (I am American).
Leider schafft es Microsoft seit Jahren nicht, dass die VBA-Variante dieser Funktion genauso arbeitet wie die manuelle Ausführung des Befehls an der nationalen Benutzeroberfläche.
Hier bleibt nichts anderes über, als die Umwandlung Zellenweise per VBA durchzuführen.
Gruß
Franz

Sub aaText_to_Number()
Dim Zelle As Range, Bereich As Range
Dim wks As Worksheet
Dim StatusCalc As Long
With Application
.ScreenUpdating = False
StatusCalc = .Calculation
.Calculation = xlCalculationManual
End With
Set wks = ActiveSheet
With wks
'Zellen mit Daten in Spalte E
Set Bereich = .Range(.Cells(1, 5), .Cells(.Rows.Count, 5).End(xlUp))
Bereich.NumberFormat = "General" 'oder das gewünschte Zahlenformat
For Each Zelle In Bereich.Cells
With Zelle
If IsNumeric(.Value) Then .Value = CDbl(.Value)
End With
Next
End With
With Application
.ScreenUpdating = True
.Calculation = StatusCalc
End With
End Sub

AW: TextToColumns in VBA

12.06.2015 07:49:58

Senna

Hey Franz,
super das funktioniert einwandfrei. Vielen Dank für deine Hilfe :)
Gruß
Senna

Infobox / Tutorial

TextToColumns in VBA: Automatisierung von Excel-Funktionen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die Funktion TextToColumns in VBA zu nutzen, befolge diese Schritte:

Öffne den VBA-Editor: Drücke ALT + F11 in Excel.
Füge ein neues Modul hinzu: Rechtsklicke im Projektfenster auf VBAProject (DeineDatei.xlsx), wähle Einfügen und dann Modul.

Füge den folgenden Code ein:

Sub TextToColumnsBeispiel()
   Columns("E:E").Select
   Selection.TextToColumns Destination:=Range("E:E"), DataType:=xlDelimited, _
       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
       Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
       :=Array(1, 1), DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:=True
End Sub

Anpassen der Parameter: Stelle sicher, dass die Parameter wie DecimalSeparator und ThousandsSeparator deinen regionalen Einstellungen entsprechen.
Führe das Makro aus: Gehe zurück zu Excel, drücke ALT + F8, wähle das Makro TextToColumnsBeispiel und klicke auf Ausführen.

Häufige Fehler und Lösungen

Problem: Nach der Ausführung des Codes wird das Dezimaltrennzeichen nicht korrekt gesetzt.
Lösung: Überprüfe die regionalen Einstellungen deines Excel und stelle sicher, dass die Parameter DecimalSeparator und ThousandsSeparator korrekt definiert sind.

Problem: Die Zahlen bleiben als Text formatiert.
Lösung: Nutze den folgenden VBA-Code, um die Zellen in Zahlen zu konvertieren:

Sub aaText_to_Number()
    Dim Zelle As Range, Bereich As Range
    Dim StatusCalc As Long
    With Application
        .ScreenUpdating = False
        StatusCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    Set Bereich = ActiveSheet.Range("E:E").SpecialCells(xlCellTypeConstants)
    Bereich.NumberFormat = "General"
    For Each Zelle In Bereich.Cells
        If IsNumeric(Zelle.Value) Then Zelle.Value = CDbl(Zelle.Value)
    Next
    With Application
        .ScreenUpdating = True
        .Calculation = StatusCalc
    End With
End Sub

Alternative Methoden

Falls du eine andere Methode als TextToColumns in VBA verwenden möchtest, kannst du auch die Split-Funktion nutzen, um Strings zu teilen und in Zellen zu schreiben. Hier ein Beispiel:

Sub SplitBeispiel()
    Dim Zelle As Range
    For Each Zelle In Range("A1:A10")
        Dim Teile() As String
        Teile = Split(Zelle.Value, ",") ' Trennzeichen anpassen
        Zelle.Offset(0, 1).Value = Teile(0) ' Erstes Teil in die nächste Zelle
        Zelle.Offset(0, 2).Value = Teile(1) ' Zweites Teil in die übernächste Zelle
    Next
End Sub

Praktische Beispiele

Hier ist ein einfaches Beispiel, wie du die Funktion TextToColumns in einem VBA-Makro verwenden kannst:

Sub BeispielTextToColumns()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Tabelle1")

    ws.Range("A1:A10").TextToColumns Destination:=ws.Range("B1"), DataType:=xlDelimited, _
        Comma:=True, FieldInfo:=Array(1, 1)
End Sub

In diesem Beispiel wird der Inhalt von Spalte A in Spalte B aufgeteilt, wobei das Komma als Trennzeichen verwendet wird.

Tipps für Profis

Verwendung des FieldInfo-Arrays: Mit dem FieldInfo-Array kannst du die Formatierung für jede Spalte nach der Trennung festlegen. Beispiel: FieldInfo:=Array(1, 1, 2, 4) (1 für Standardformat, 4 für Text).
Vermeidung von Fehlern: Verwende On Error Resume Next, um sicherzustellen, dass dein Makro nicht bei jedem Fehler stoppt. Dies kann nützlich sein, wenn du mit großen Datenmengen arbeitest.
Optimierung der Performance: Deaktiviere das Bildschirm-Update mit .ScreenUpdating = False, um die Ausführungsgeschwindigkeit zu erhöhen.

FAQ: Häufige Fragen

1. Wie kann ich mehrere Trennzeichen in TextToColumns verwenden?
Du kannst mehrere Trennzeichen nicht direkt in TextToColumns angeben. Stattdessen kannst du den Inhalt zuerst in einem String speichern und dann mit der Split-Funktion aufteilen.

2. Was ist der Unterschied zwischen Selection.TextToColumns und Range.TextToColumns?
Selection.TextToColumns arbeitet nur auf der aktuell ausgewählten Zelle oder dem Bereich, während Range.TextToColumns auf einen definierten Bereich angewendet wird, was oft effizienter ist.

3. Wie kann ich sicherstellen, dass meine Zahlen korrekt formatiert sind?
Nutze die Parameter DecimalSeparator und ThousandsSeparator in deinem VBA-Code, um sicherzustellen, dass die Zahlen entsprechend deiner regionalen Einstellungen formatiert werden.

Beliebteste Forumthreads (12 Monate)