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