Skip to content

Excel VBA – Text to Columns with VBA Macro

    Text to Columns

    Use the TextToColumns method of the Range object.

    expression.TextToColumns(Destination, DataType,TextQualifier, ConsecutiveDelimiter, Tab, _
    Semicolon, Comma, Space, Other,OtherChar, FieldInfo, DecimalSeparator, _
    ThousandsSeparator,TrailingMinusNumbers)

    The code below relates to the data shown above. To perform a simple text to columns procedure you only need to use the Destination & DataType parameters and then specify the delimiter eg Space:= True. DataType defaults to delimited rather than fixed width, so strictly not needed in the code below.

    Range("B1")= "First Name"
    Range("C1")= "Last Name"
    Range("A2",Range("A2").End(xlDown)).TextToColumns _
    Destination:=Range("B2"), DataType:=xlDelimited,Space:=True
    Columns("B:C").AutoFit
    Range("A1",Range("A1").End(xlDown)).Copy
    Range("B1",Range("B1").End(xlToRight).End(xlDown)).PasteSpecial _
    Paste:=xlPasteFormats
    Application.CutCopyMode= False