Text to Columns in Excel VBA (4 Easy Examples)

In this article, we will discuss the Excel VBA Text to Columns method (TextToColumns). The TextToColumns method works the same way as the Text to Column feature in an Excel worksheet, but in VBA we can have more control over the end result by setting up numerous argument values for the TextToColumns method.

Why Do We Need Text to Columns Feature?

The text-to-column feature is valuable when it comes to post-processing imported data from various sources, such as Text files or CSV files. After loading data in Excel from these sources, many times the data is concentrated in one column, separated with a common delimiter such as comma, space, hyphen, or another symbol. We will need to split this single column into multiple columns to make the data more readable and operable. For example, below we have separated a column with space-delimited data into two distinct columns.

Syntax of Excel VBA Range.TextToColumns Method

All the arguments are optional.

• Destination: Specifies the location where the split data will be placed. Either a Range type object or a string that represents the address of a range can be provided. If left blank, the location of the original range will be taken by default.
• DataType: Specifies the type of data that we are going to split. There are four options:

i. xlDelimited: Used when the data is separated with a delimiter such as comma, hyphen, etc.

ii. xlFixedWidth: Used when the data is not delimited by any symbols but rather has a fixed width for each column.

iii. xlTextFormat: Used when the data needs to be converted into another text format.

iv. xlGeneralFormat: Used when the data needs to be transformed into a general text format.

• TextQualifier: Specifies the character used to enclose the string within a cell. The default value is xlTextQualifierDoubleQuote. If the delimiter used in the data is also present as a part of the data then we need to specify the TextQualifier. Either use xlTextQualifierDoubleQuote for a double quotation or xlTextQualifierNone.
• Consecutive Delimiter: Specifies whether successive delimiters should be considered as a single delimiter. Set it to TRUE for considering multiple successive delimiters as one. The default value is FALSE.
• Tab, Semicolon, Comma, Space, Other: These parameters indicate the delimiter characters that are to be used to separate the data. They take only TRUE or FALSE as arguments. For example, if a Comma is the only delimiter, insert TRUE in the Comma argument and other arguments as FALSE.
• OtherChar: If TRUE is given in the Other argument, specify the delimiter character here.
• FieldInfo: This option allows us to set the format for each split data column. It is an array of arrays where the parsing settings for each column are specified in each sub-array. Each sub-array’s first element defines the column number (beginning with 1), while the second element specifies the data type of the column. The options for the second element of the array are as follows:

xlGeneralFormat (1)

xlTextFormat (2)

xlMDYFormat (3)

xlDMYFormat (4)

xlYMDFormat (5)

xlMYDFormat (6)

xlDYMFormat (7)

xlYDMFormat (8)

xlSkipColumn (9)

A typical array for FieldInfo would look like this:

• DecimalSeparator: When converting text to numbers, this argument specifies the character where the decimal separator appears.
• ThousandsSeparator: When converting text to numbers, this argument specifies the character that will be used as the Thousand separator.
• TrailingMinusNumbers: Whether trailing minus signs are considered negative numbers is determined by this argument. Either True or False is valid.

Text to Columns in Excel VBA: 4 Suitable Examples

Now let’s demonstrate some examples of using VBA Text to Columns (Range.TextToColumns method).

Example 1 – Splitting a Column with the Space Delimiter

Suppose we have some employees’ full names. We’ll use the VBA Text to Columns feature (Range.TextToColumns methods) to separate the first names and last names. So the delimiter by which the data is separated is the space character.

We can use the following VBA code to transform the column in range B5:B14 into multiple columns in D5:E14:

VBA Code Syntax:

Sub TextToColumns()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
rng.TextToColumns Destination:=Range("D5"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlTextQualifierNone, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=True, _
    Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
    DecimalSeparator:=".", _
    ThousandsSeparator:=",", _
    TrailingMinusNumbers:=True
Application.DisplayAlerts = True
End Sub

Application.DisplayAlerts = False

Dim rng As Range
Set rng = Range("B5:B14")

rng.TextToColumns Destination:=Range("D5")

DataType:=xlDelimited

TextQualifier:=xlTextQualifierNone

Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False , _

FieldInfo:=Array(Array(1, 1), Array(2, 1))

DecimalSeparator:="."
ThousandsSeparator:=","
TrailingMinusNumbers:=True

Application.DisplayAlerts = True

Run the code to return the following result:

In the above code, not all the arguments were necessary as most of them had a default or False value. So, we can use a more concise code like this:

VBA Code Syntax:

Sub TextToColumns()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
rng.TextToColumns Destination:=Range("D5"), _
    Space:=True
Application.DisplayAlerts = True
End Sub

Here, we have used only 2 arguments (Destination and Space), which are sufficient to produce the exact same result as above.

We can use an even shorter version of the code by not explicitly mentioning the name of the arguments, rather only writing the value of those arguments serially or simply skipping them. The code would look like this:

VBA Code Syntax:

Sub TextToColumns3()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
rng.TextToColumns Range("D5"), xlDelimited, , , , , , True
Application.DisplayAlerts = True
End Sub

Here, we inserted only three arguments serially. After inserting the value of the last used argument (Space), we stopped there without defining the rest. This code will also yield similar results.

Dim rng As Range
Set rng = Range("B5:B14")

For Each cell In rng
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "/", " ")
Next

rng.TextToColumns _
    Destination:=Range("D5"), _
    Space:=True

Example 2 – Using Multiple Delimiters

Now we’ll separate a column that has more than one delimiter. We’ll use the same dataset as above, but with three delimiters.

The forward-slash (/), comma (,), and space are used as delimiters in different cells. For this type of dataset, we need to set True for each type of delimiter that is already available as an argument in the Range.TextToColumns method (Tab, Semicolon, Comma, and Space). For delimiters other than these four, we need to set True to the Other argument and then assign the specific delimiter to the OtherChar argument.

The VBA code for this type of dataset is as follows:

VBA Code:

Sub TextToColumn_Multi_Deli()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
rng.TextToColumns _
    Destination:=Range("D5"), _
    Comma:=True, _
    Space:=True, _
    Other:=True, _
    OtherChar:="/"
Application.DisplayAlerts = True
End Sub

If we run the code, the following result will be returned:

Dim rng As Range
Set rng = Range("B5:B14")

rng.TextToColumns _
    Destination:=Range("D5"), _
    Comma:=True, _
    Space:=True, _
    Other:=True, _
    OtherChar:="/"
Application.DisplayAlerts = True

Method 3 – Multiple Delimiters Using SUBSTITUTE Function

There is a drawback to the previous example; we can only use one other type of delimiter (that is, symbols other than Tab, Semicolon, Comma, and Space). If we have more than one other type of delimiter, we need to convert each of them into any of the delimiters already present as arguments in the rng.TextToColumns method. For example, below we used ampersand (&) in cell B9 as a different delimiter.

We will use the Worksheet function Substitute to convert the Ampersand (&) into space and then use the rng.TextToColumns method. So the VBA Code will be as follows:

VBA Code:

Sub TextToColumn_Multi_Deli()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
For Each cell In rng
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "&", " ")
Next
rng.TextToColumns _
    Destination:=Range("D5"), _
    Comma:=True, _
    Space:=True, _
    Other:=True, _
    OtherChar:="/"
Application.DisplayAlerts = True
End Sub

Dim rng As Range
Set rng = Range("B5:B14")

For Each cell In rng
    cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "&", " ")
Next

rng.TextToColumns _
    Destination:=Range("D5"), _
    Comma:=True, _
    Space:=True, _
    Other:=True, _
    OtherChar:="/"

If we run the code, the desired result is returned.

Method 4 – Splitting by Fixed Width

Now let’s use VBA Text to Columns (rng.TextToColumns method) on a dataset that cannot be separated by a common delimiter but rather by using a Fixed Width. For example, here is a dataset where the first 4 characters in a cell represent the ID no and the following characters are Names.

For fixed width type data, we need to assign xlFixedWidth in the DataType argument of rng.TextToColumns method in VBA code. We also need to set the FieldInfo argument which specifies the width of each column.

The code looks like this:

Sub TextToColumn_FixedWidth()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Range("B5:B14")
rng.TextToColumns Destination:=Range("D5"), _
    DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(4, 1))
Application.DisplayAlerts = True
End Sub

If we run the code, we will receive the following result, as expected:

rng.TextToColumns Destination:=Range("D5")

DataType:=xlFixedWidth,

FieldInfo:=Array(Array(0, 1), Array(4, 1))

Convert Text to Columns for a Dynamic Range (Selection) with Excel VBA

We can also apply the VBA Text to Columns (rng.TextToColumns method) to a range of cells that we will select dynamically. In other words, we will apply the rng.TextToColumns method on the selected range.

We only need to assign the rng variable to the selection. The rest of the code remains the same:

Sub TextToColumn_Dynamic_Selection()
Application.DisplayAlerts = False
Dim rng As Range
Set rng = Selection
rng.TextToColumns _
    Destination:=Range("D5"), _
    Space:=True
Application.DisplayAlerts = True
End Sub

Here, we set the destination to cell D5. If we exclude this argument, the code will replace the selected column with the resulting columns.

Things to Remember

• Properly define the range and make sure that the range includes the cells that you want to convert before applying rng.TextToColumns.
• When replacing the original data with resultant columns, its a good idea to first back up the original data before applying the rng.TextToColumns method.
• Be careful specifying the correct delimiter in the rng.TextToColumns argument.
• Set the correct data type in the DataType argument. Do not mix delimited data with fixed-width data.
• Provide the FieldInfo array correctly if required. Be extra cautious specifying the data type of the resultant columns.

Frequently Asked Question on Excel VBA Text to Columns

• Can I use the VBA Text to Columns (TextToColumns method) to split text into more than one column?

Sure. We can use the VBA Text to Columns (TextToColumns method) to create as many columns as we want.

• Can I Use Multiple Delimiters in VBA Text to Columns (TextToColumns method)?

Yes, you can use more than one delimiter at once.

• Can I use the TextToColumns method to split text in a non-adjacent range?

Yes, by using the Destination argument, you can split the text at any location in the worksheet you want.

Download Practice Workbook

Related Articles

• How to Convert Text to Columns in Excel
• How to Convert Column to Text with Delimiter in Excel
  
• How to Convert Text to Columns with Multiple Delimiters in Excel
• How to Convert Text to Columns in Excel with Multiple Spaces
• How to Convert Text to Columns Without Overwriting in Excel
• How to Use Line Break as Delimiter in Excel Text to Columns
• How to Split Text to Columns Automatically with Formula in Excel
• [Fixed!] Excel Text to Columns Is Deleting Data
  
• How to Undo Text to Columns in Excel
• Excel Text to Columns Not Working
  
• Excel VBA Text to Columns for Fixed Width
• Excel VBA Text to Columns with Multiple Delimiters