VBA: How to Convert Text to Columns

You can use the Range.TextToColumns method in VBA to convert a cell of text into columns.

The following examples show how to use this method in practice in different scenarios.

Example 1: Convert Text to Columns (Space as Delimiter)

Suppose we have the following list of names in the range A1:A9 in Excel:

We can create the following macro to convert the text in each cell into separate columns, using spaces as delimiters:

Sub TextToCols()
    Range("A1:A9").TextToColumns _
    ConsecutiveDelimiter:=True, _
    Space:=True
End Sub

When we run this macro, we receive the following output:

Notice that the text in each cell has been converted into separate columns.

Note: The argument ConsecutiveDelimiter:=True tells VBA to consider consecutive delimiters together as one single delimiter.

Example 2: Convert Text to Columns (Comma as Delimiter)

Suppose we have the following list of names in the range A1:A9 that are separated by commas:

We can create the following macro to convert the text in each cell into separate columns, using commas as delimiters:

Sub TextToCols()
    Range("A1:A9").TextToColumns _
    ConsecutiveDelimiter:=True, _
    Comma:=True
End Sub

When we run this macro, we receive the following output:

Notice that the text in each cell has been converted into separate columns.

Example 3: Convert Text to Columns (With Text in Double Quotes)

Suppose we have the following list of names in the range A1:A9 that are separated by spaces and have double quotes:

We can create the following macro to convert the text in each cell into separate columns:

Sub TextToCols()
    Range("A1:A9").TextToColumns _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Space:=True
End Sub

When we run this macro, we receive the following output:

Notice that the text in each cell has been converted into separate columns.

Note that we used the argument TextQualifier:=xlDoubleQuote to tell VBA that the text was surrounded by double quotes.

Note: You can find the complete documentation for the TextToColumns method in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks using VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text