Excel’s text to columns feature is one of the most popular features in Excel. Text to columns feature is applicable for delimited and fixed width type data. This is a built-in feature of Excel and you can avail of this from the toolbar. You can also apply this feature using the VBA code. With the help of VBA macro, things may seem much easier to do. In this article, we have demonstrated the use of Excel VBA text to columns for fixed-width data type.
Download Practice Workbook
Download this practice workbook.
Overview of VBA TextToColumns Method
TextToColumns Syntax:
expression.TextToColumns (Destination, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers)
TextToColumns is used to separate texts from a range and return those separated texts or values into different columns. The text type can be either delimited or fixed width type. For the fixed width data type, you have to set the width of each column’s character.
Excel VBA Text to Columns for Fixed Width: A Suitable Example
We will be using the following dataset as an example to explain the process of Excel VBA text to columns for fixed width data type. The dataset represents some countries’ general form of phone numbers with country code, area code, and number.
- First, you have to activate the VBA macro editor if it isn’t activated yet. Have a look at this article How to Open VBA Macro Editor in Excel to find out that.
- After entering the VBA window, go to the Insert tab, click on Module, and paste the following code in the Module 1 section.
Sub Text_To_Columns()
'created by ExcelDemy
Set Rng = Application.InputBox(Prompt:="Select a range", Type:=8)
c = Int(InputBox("Enter the Number of Columns: ", "ExcelDemy"))
Arr = InputBox("Enter the Numbers by Which You Want to Break the Columns. Separate by Commas.", "ExcelDemy")
Arr = Split(Arr, ",")
Set Rng2 = Application.InputBox(Prompt:="Select a range", Type:=8)
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Text = Rng.Cells(i, j)
Start = 0
For k = 1 To c
Rng2.Cells(i, ((j - 1) * c) + k) = Mid(Text, Start + 1, Int(Arr(k - 1)))
Start = Start + Int(Arr(k - 1))
Next k
Next j
Next i
End Sub
VBA Breakdown
Set Rng = Application.InputBox(Prompt:="Select a range", Type:=8)
- This line asks the user to choose a range of cells from the worksheet, then it assigns that selection to the variable “Rng“.
c = Int(InputBox("Enter the Number of Columns: ", "ExcelDemy"))
- This line asks the user how many columns they want to divide the text into and put that value to the variable “c“.
Arr = InputBox("Enter the Numbers by Which You Want to Break the Columns. Separate by Commas.", "ExcelDemy")
- This line asks the user to define the length of each column in a list of integers separated by commas and assign this value to the variable “Arr“.
Arr = Split(Arr, ",")
- This line divides the user’s string of numbers into an array of distinct values using the Split function based on the comma.
Set Rng2 = Application.InputBox(Prompt:="Select a range", Type:=8)
- In order to insert the split text in this range of cells, the user is prompted in this line, and the split text range is then assigned to the variable “Rng2“.
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Text =Rng.Cells(i, j)
Start = 0
- These lines of code start a loop that repeatedly iterates through each row in the user-selected range. The value of i starts from 1 to the row number of Rng.
- Another for loop will run, where the value of j ranges from 1 to column number of Rng. The current cell’s value is then evaluated to the variable “Text“. The “Start” variable, which will be used to monitor the starting point of each new column, is then initialized to zero.
For k = 1 To c
Rng2.Cells(i,((j - 1) * c) + k) = Mid(Text, Start + 1, Int(Arr(k - 1)))
Start= Start + Int(Arr(k - 1))
Next k
- Another for loop starts considering the value of k from 1 to c. The “Text” variable is used to extract a substring starting at the “Start” location and having a length determined by the value at the “k-1” index of the “Arr” array using the Mid function. Next, the value of Start is considered to be the sum of Start and the integer value of Arr.
Next j
- This line ends the loop that iterates through each column in the range the user selects.
Next i
- This line ends the loop that iterates through each row in the range the user selects.
- After that, simply click on F5 key on your keyboard to run the code.
- The following dialog box tilted Input will appear on your worksheet and you will be asked to select the range from where you want the texts to be separated.
- Select range C6:C10 and click on OK.
- Next, enter the number of columns that is 3 and click on OK.
- Now, you have to set the number of characters for each column. The country code, area, and phone numbers consist of 2,2 and 8 characters respectively. So simply type 2,2,8 and click on OK.
- After that, you will be asked for the output range. Select cell D6, then click on OK.
- Finally, you will get the output as follows.
Excel VBA Text to Columns with Delimiter
You can use Excel VBA text to columns for delimited type data as well. We will be using the following dataset to illustrate the processes. We will separate name, id, and email address based on delimiter comma (,).
- As shown previously, go to the Module section. Then copy and paste the following code.
Sub TextToColDelimiter()
'Presented By ExcelDemy
Dim input_rng As Range
Dim output_rng As Range
Dim delimiter As String
On Error Resume Next
Set input_rng = Application.InputBox("Select the Range:", "ExcelDemy", Type:=8)
On Error GoTo 0
If input_rng Is Nothing Then Exit Sub
On Error Resume Next
Set output_rng = Application.InputBox("Select the Output Range:", "ExcelDemy", Type:=8)
On Error GoTo 0
If output_rng Is Nothing Then Exit Sub
delimiter = Application.InputBox("Enter the Delimiter Type:", "ExcelDemy")
input_rng.TextToColumns _
Destination:=output_rng, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=delimiter, _
fieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
End Sub
VBA Breakdown
Sub TextToColDelimiter()
- The “TextToColDelimiter” subroutine’s definition starts with this line.
Dim input_rng As Range
Dim output_rng As Range
Dim delimiter As String
- Input_rng and output_rng are Range type objects that will be used to hold the input and output ranges, respectively, and delimiter is a String variable that will contain the delimiter character. These lines define three variables.
On Error Resume Next
Set input_rng = Application.InputBox("Select the Range:", "ExcelDemy", Type:=8)
On Error GoTo 0
- These lines display an Excel dialog box and ask the user to choose a set of cells using the InputBox function. To handle any mistakes that might arise during the user’s selection process, use the “On Error Resume Next” and “On Error GoTo 0” statements.
If input_rng Is Nothing Then Exit Sub
- This line determines if input_rng has a value. If not, the procedure terminates right away.
On Error Resume Next
Set output_rng = Application.InputBox("Select the Output Range:", "ExcelDemy", Type:=8)
On Error GoTo 0
- These lines create a different dialog box and ask the user to choose an output range of cells in Excel using the InputBox function.
If output_rng Is Nothing Then Exit Sub
- This line determines whether output_rng has a value. If not, the procedure terminates right away.
delimiter = Application.InputBox("Enter the Delimiter Type:", "ExcelDemy")
- This line displays a dialog box and asks the user to type a delimiter character using the InputBox function. The delimiter variable will be updated with the character entered.
input_rng.TextToColumns _
Destination:=output_rng, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, _
OtherChar:=delimiter, _
fieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
- input_rng.TextToColumns, divide the text in the input range into distinct columns.
- Destination:=output_rng is the range to which the output should be written indicated.
- DataType:=xlDelimited indicates that a particular character serves as the text’s delimiter.
- TextQualifier:=xlTextQualifierNone instructs Excel to ignore any text qualifiers that may be present, such as quotes.
- ConsecutiveDelimiter:=False instructs Excel to treat multiple delimiters as one delimiter.
- Tab:=False, Semicolon:=False, Comma:=False, and Space:=False arguments specify that Excel should not treat these specific characters as delimiters (i.e. only the OtherChar delimiter should be used).
- Other:=True specifies that Excel should use the delimiter character specified in the OtherChar argument.
- OtherChar:=delimiter specifies the delimiter character.
- fieldInfo:=Array(Array(1, 1), Array(2, 1)) specifies how the columns should be split. In this case, it specifies that the first column in the input range should be split into two columns in the output range.
- TrailingMinusNumbers:=True specifies that Excel should treat any minus sign at the end of a cell as a negative number.
- Run the code by pressing F5 and you will be asked for the input range.
- Select range B5:B13, then click on OK.
- After that, you will be asked for the output range. Select cell C5 and click on OK.
- Now, you have to insert the delimiter type. As you can see, the major delimiter here is the comma(,). Insert a comma(,) and click on OK.
- And finally, the output is as follows.
Conclusion
It can take a lot of time and effort to complete a text to columns fixed width operation in Excel using the text-to-columns feature from the toolbar. However, this process may be automated with Excel VBA, which will save both time and work. You may quickly create a macro that will carry out this operation for you by following the instructions in this article, which will make your data processing activities more effective and quick. Hope you are all set with all the necessary information for Excel VBA text to columns for fixed width data type. Visit our site ExcelDemy to explore more relevant articles.