Text to Columns in Excel VBA (4 Easy Examples)

In this article, we are going to discuss the Excel VBA Text to Columns method (TextToColumns). The TextToColumns method works the same way as the Text to Column feature in the Excel worksheet. But in VBA, we can have more control over the end result by setting up numerous argument values for the TextToColumns method. Here, we will try to discuss each argument. To learn more, go through the articles.

Why Do We Need Text to Columns Feature?

The text-to-column feature is greatly helpful when it comes to post-processing imported data from various sources, such as Text files or CSV files. After loading data in Excel from different sources, many times the data is concentrated in one column. They are usually separated with a common delimiter such as comma, space, hyphen, or any other symbol. Most of the time, we need to split up a single column into multiple columns to make the data more readable and more operable. For example, below, we have separated a column with space-delimited data into two distinct columns.

Syntax of Excel VBA Range.TextToColumns Method

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

All the arguments are optional. Below, their descriptions are given.

Destination: This argument specifies the location where the split data will be placed. Here you can either give a Range type object or a string that represents the address of a range. If we leave the argument, it will take the location of the original range as the default.
DataType: This argument specifies the type of data that we are going to split up. We have four options for this argument.

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

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

iii. xlTextFormat: When the data needs to be converted into another text format, use this option.

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

TextQualifier: It 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 point then we need to specify the TextQualifier. You can either use xlTextQualifierDoubleQuote for a double quotation or xlTextQualifierNone.
Consecutive Delimiter: This option determines whether successive delimiters should be considered as a single delimiter. Give 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 your delimiter, you need to insert TRUE in the Comma argument and other arguments will have FALSE.
OtherChar: If you give TRUE in the Other argument, you need to insert the delimiter character here.
FiledInfo: 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 given below.

xlGeneralFormat (1)

xlTextFormat (2)

xlMDYFormat (3)

xlDMYFormat (4)

xlYMDFormat (5)

xlMYDFormat (6)

xlDYMFormat (7)

xlYDMFormat (8)

xlSkipColumn (9)

A typical array of FiledInfo would look like this

FiledInfo= Array(Array(1, 1), Array(2, 1), Array(3,4))

DecimalSeparator: When converting text to numbers, this argument specifies the character where we used the decimal separator.
ThousandsSeparator: When converting text to numbers, this argument specifies the character that will be used as the Thousand separators.
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

In this section, we will demonstrate some examples of using VBA Text to Columns (Range.TextToColumns method). Let’s explore them one by one.

1. Use of Range.TextToColumn Method to Split Column with Space Delimiter

Here, we will split a column that has space as a delimiter. Let’s take a look at our dataset.

Here, we have some employees’ full names office. Our target is to 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 space.

Now, 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

📌How Does the Code Work?

Application.DisplayAlerts = False

This line of code stops any alerts from being displayed when the code runs.

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

Here, we declared a Range type variable, rng, and assigned the range B5:B14 to it.

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

Here, we have set the Destination to cell D5

DataType:=xlDelimited

As our data is delimited, hence we set the DataType as xlDelimited.

TextQualifier:=xlTextQualifierNone

We do not have any data point containing the delimiter within itself, we set the TextQualifier to xlTextQualifierNone.

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

As the delimiter here is Space, hence we set Space to True. Naturally, the rest of the Delimiters (Tab, Semicolon, Comma, Other) are set to False.

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

Here, as our data is of xlGeneralFormat(1) and will be broken into two columns, we set the arrays accordingly. In Array(1, 1), the first argument 1 stands for the 1st column, and 2nd argument 1 stands for the data type of that column.

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

Here, the Decimal and Thousands separators are set to dot(.) and comma(,) respectively. Moreover, the trailing minus signs are considered negative numbers.

Application.DisplayAlerts = True

The alert is again turned on for being displayed after completing the running of the code.

Now, if we run the code, we will get our desired result.

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

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), and they are enough to produce the exact same result as we saw in the previous code.

We can also use a 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

In this piece of code, we inserted only three arguments serially. After inserting the value of the last used argument (Space), we stopped there without going forward. This code will also yield similar results.

📌How Does the Code Work?

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

Here, we assigned the range B5:B14 in a Range type variable named rng.

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

We used For Each loop to loop through each cell in rng. For the worksheet function, we used Substitute to convert the forward slash (/) into space.

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

Here, we used the TextToColumns method to extract the first and last names. As the diameter is space, we assigned True to the Space argument.

2. Convert Excel VBA Text to Columns with Multiple Delimiters

In this section, I will show you how you can separate a column that has more than one delimiter. Here, I have taken the same data set used above but with three delimiters.

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

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

Now if we run the code, we will have the following result.

📌How Does the Code Work?

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

Here, we assign the range B5:B14 in a Range type variable named rng.

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

Here, we used the TextToColumns method to extract the first and last names. As the present delimiters are comma, space, and other, we assigned True to those arguments. In the OtherChar argument, we assigned the delimiter “/”.

3. Excel VBA Text to Columns for Multiple Delimiters Using SUBSTITUTE Function

There is a drawback to the previous example. In the previous method, 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 present as arguments in the rng.TextToColumns method. For example, below, we used ampersand(&) in cell B9 as another type of delimiter.

In this case, 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 like this.

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

📌How Does the Code Work?

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

Here, we assign the range B5:B14 in a Range type variable named rng.

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

We used For Each loop to loop through each cell in rng. For the worksheet function, we used Substitute to convert the ampersand (&) into space.

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

Here, we used the TextToColumns method to extract the first and last names. As the present delimiters are comma, space, and other, I assigned True to those arguments. In the OtherChar argument, I assigned the delimiter “/”.

Now, if we run the code, we will get our desired result.

4. Excel VBA Text to Columns for Fixed Width

In this section, I am going to show you how we can use VBA Text to Columns (rng.TextToColumns method) in case of a data set that will not be separated by a common delimiter rather than using a Fixed Width. For example, here is a dataset where the first 4 characters in a cell represent the ID no and the next characters are Names.

Now, for fixed width type data, we need to assign xlFixedWidth in the DataType argument of rng.TextToColumns method in VBA code. In addition to that, we need to set the FiledInfo argument which will specify the width of each column. The code will look 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 have the following result, as expected.

📌How Does the Code Work?

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

I used the rng.TextToColumns and set Destination to D5.

DataType:=xlFixedWidth,

The DataType is to xlFixedWidth.

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

In the FieldInfo, I inserted an array. There are two subarrays for two resultant columns in the array. In the first column, I filled the cells with the characters from the beginning ( 0) and it will have xlGeneralFormat (1). Hence, the subarray for the first column is Array(0,1). In the 2nd column, I filled the cells from the 5th character of the parent column (4) and it will have xlGeneralFormat (1). Hence, the subarray for the 2nd column is Array(4,1).

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

In this section, I am going to show you how we can 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. This is nothing special as we will only need to assign the rng variable to selection. The rest of the code will be 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 you exclude this argument, the code will replace the selected column with the resulting columns.

Things to Remember

You should properly define the range and make sure that the range includes the cell that you want to convert before applying the rng.TextToColumns.
If you want to replace the original data with resultant columns, make sure to back up the original data before applying the rng.TextToColumns method.
Be careful while 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. You need to be extra cautious while 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

Download this practice workbook to exercise while you are reading this article.

VBA Text to Columns.xlsm

Conclusion

That is the end of this article regarding Excel VBA Text to Columns (TextToColumns method). If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

Get FREE Advanced Excel Exercises with Solutions!

Text to Columns in Excel VBA (4 Easy Examples)

Why Do We Need Text to Columns Feature?

Syntax of Excel VBA Range.TextToColumns Method

Text to Columns in Excel VBA: 4 Suitable Examples

1. Use of Range.TextToColumn Method to Split Column with Space Delimiter

2. Convert Excel VBA Text to Columns with Multiple Delimiters

3. Excel VBA Text to Columns for Multiple Delimiters Using SUBSTITUTE Function

4. Excel VBA Text to Columns for Fixed Width

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

Things to Remember

Frequently Asked Question on Excel VBA Text to Columns

Conclusion

Related Articles

Leave a reply Cancel reply