Table of Contents
VBA (Visual Basic for Applications) is a programming language used to automate tasks within Excel. One of the common tasks that can be accomplished with VBA is the conversion of text to columns. This can be done by using a combination of the Split function and the Range.Value property to separate the data into columns based on delimiters. The Split function can be used to create an array of the data that can then be used to assign the values to the columns. This process can be automated to quickly convert text to columns for multiple sheets.
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 .
The following tutorials explain how to perform other common tasks using VBA: