Sometimes we have to deal with pretty long strings in our Excel dataset and that is not quite convenient to work with always. Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to split the string into multiple columns in Excel with the VBA macro.
Download Workbook
You can download the free practice Excel workbook from here.
2 Ways to Split String into Multiple Columns with VBA in Excel
Following this section, you will learn how to split a string from one cell, multiple strings from multiple cells and a specific range of strings into multiple columns with VBA in Excel.
1. Embed VBA to Split String into Multiple Columns in Excel
Consider the following dataset. We have one long string, John Cena,23/04/1977,Wrestler,USA in a worksheet named “String” in our Excel workbook. We added sub-strings such as, “John Cena”, “23/04/1977”, “Wrestler” and “USA” together and put the comma (,) symbol between them to produce this long string.
You will learn how to split this string into multiple columns with VBA code in Excel.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Next, in the pop-up code window, from the menu bar, click Insert -> Module.
- Then, copy the following code and paste it into the code window.
Option Explicit
Private Const SourceColumn As String = "A"
Private Const Separator As String = ","
Public Sub SplitString()
Dim SheetName As String
Dim iSheet As Worksheet
Dim EndRow As Long
Dim MaxULimit As Integer
Dim Output
On Error GoTo SplitStringErr
SheetName = VBA.InputBox("Please Enter Worksheet Name")
If SheetName = "" Then Exit Sub
Set iSheet = Worksheets(SheetName)
With iSheet
EndRow = .Range(SourceColumn & .Rows.Count).End(xlUp).Row
Output = SplittedStrings(iRng:=.Range(.Cells(1, SourceColumn), .Cells(EndRow, SourceColumn)), MaxLength:=MaxULimit)
If Not IsEmpty(Output) Then
.Range(.Cells(1, SourceColumn), .Cells(EndRow, MaxULimit)).value = Output
End If
End With
SplitStringErr:
If Err.Number <> 0 Then MsgBox Err.Description, vbCritical
End Sub
Private Function SplittedStrings(iRng As Range, ByRef MaxLength As Integer) As Variant
Dim i As Integer
Dim Item As Variant
Dim iData As Variant
Dim iValue As Variant
Dim Result As Variant
If Not IsArray(iRng) Then
ReDim iData(1 To 1, 1 To 1)
iData(1, 1) = iRng.value
Else
iData = iRng.value
End If
ReDim Result(LBound(iData) To UBound(iData))
For i = LBound(iData) To UBound(iData)
iValue = iData(i, 1)
If IsEmpty(iValue) Then
GoTo continue
End If
Item = VBA.Split(iValue, Separator)
Result(i) = Item
If UBound(Item) + 1 > MaxLength Then
MaxLength = UBound(Item) + 1
End If
continue:
Next i
If MaxLength = 0 Then
Exit Function
End If
Dim Substring As Variant
Dim x As Integer
ReDim Substring(LBound(Result) To UBound(Result), LBound(Result) To MaxLength)
For i = LBound(Result) To UBound(Result)
Item = Result(i)
For x = 0 To UBound(Item)
Substring(i, x + 1) = Item(x)
Next x
Next i
SplittedStrings = Substring
End FunctionYour code is now ready to run.
- Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.
- If the code executes successfully, then there will be an Excel pop-up input box.
- Write the worksheet name where the values to be split exist. For instance, we want to split the string from the “String” worksheet, so we inserted “String” in the input box.
- After that, click OK.
If you successfully perform all those steps above, your long string that is separated by the comma (,) will be split.
As you can see from the above picture, the string, John Cena,23/04/1977,Wrestler,USA is now split into multiple columns. Sub-strings that were separated by comma (,) (“John Cena”, “23/04/1977”, “Wrestler” and “USA”) to construct that long string is placed in different columns (“John Cena” in column A, “23/04/1977” in column B, “Wrestler” in column C and “USA” in column D).
Read More: Excel VBA: Split String into Rows (6 Ideal Examples)
Similar Readings
- Excel Formula to Split String by Comma (5 Examples)
- How to Split Cells in Excel (5 Easy Tricks)
- Excel Formula to Split: 8 Examples
VBA to Split Multiple Strings into Multiple Columns in Excel
By utilizing the above macro, you can also split multiple strings into multiple columns. Consider the following example. Here, we have multiple long strings separated by comma (,) in multiple cells in a worksheet named “Strings”.
If you run the code provided above for this case, all these strings will be split, and each sub-string will take place in separate columns.
- Don’t forget to insert the relevant sheet name in the pop-up input box.
After entering the appropriate sheet name where the data to be split are stored, the multiple strings from your dataset will be split into multiple columns.
As you can see from the above image, all the multiple strings from the worksheet named “Strings” are now split into multiple columns where each sub-string separated by the comma (,) is placed in separate columns.
VBA Code Explanation
Option ExplicitFirst, forces to declare all the variables explicitly of the file.
Private Const SourceColumn As String = "A"Then, define the source column, Column A, the column that holds the strings to split. You can pass the column address according to your dataset.
Private Const Separator As String = ","After that, define the separator. We worked with the comma (,) as our separator. You can store the separator that your dataset has.
Public Sub SplitString()Next, provide a name for the sub-procedure of the macro.
Dim SheetName As String
Dim iSheet As Worksheet
Dim EndRow As Long
Dim MaxULimit As Integer
Dim OutputAfter that, declare the necessary variables for the macro.
On Error GoTo SplitStringErrIf any error occurs, then go to the SplitStringErr statement.
SheetName = VBA.InputBox("Please Enter Worksheet Name")Later, store the input box in a declared variable to insert the worksheet name from the user.
If SheetName = "" Then Exit SubIf the user provides a null worksheet name, then leave the sub-procedure.
Set iSheet = Worksheets(SheetName)
With iSheet
EndRow = .Range(SourceColumn & .Rows.Count).End(xlUp).Row
Output = SplittedStrings(iRng:=.Range(.Cells(1, SourceColumn), .Cells(EndRow, SourceColumn)), MaxLength:=MaxULimit)
If Not IsEmpty(Output) Then
.Range(.Cells(1, SourceColumn), .Cells(EndRow, MaxULimit)).value = Output
End If
End WithThis piece of code first takes the provided sheet under consideration. Then, count all the rows of the dataset, start splitting the source column and pass the value as the arguments of the SplittedStrings function.
SplitStringErr:
If Err.Number <> 0 Then MsgBox Err.Description, vbCriticalThe SplitStringErr statement – throws the error message with the description in the MsgBox.
Private Function SplittedStrings(iRng As Range, ByRef MaxLength As Integer) As VariantNext, we initiate the SplittedStrings function mentioned above.
Dim i As Integer
Dim Item As Variant
Dim iData As Variant
Dim iValue As Variant
Dim Result As VariantThen, declare the necessary variables for the function.
If Not IsArray(iRng) Then
ReDim iData(1 To 1, 1 To 1)
iData(1, 1) = iRng.value
Else
iData = iRng.value
End IfThis IF statement works with the array of data and throws the selected range inside the declared variable.
ReDim Result(LBound(iData) To UBound(iData))
For i = LBound(iData) To UBound(iData)
iValue = iData(i, 1)
If IsEmpty(iValue) Then
GoTo continue
End IfStarts iterating with the FOR Loop for the updated data from the smallest subscript to the largest subscript of the array and stores the value in the variable. It continues doing this until no value is left there to scan.
Item = VBA.Split(iValue, Separator)
Result(i) = Item
If UBound(Item) + 1 > MaxLength Then
MaxLength = UBound(Item) + 1
End IfSplits the stored value and again store it in different variable to utilize later.
continue:
Next iKeep continues the code execution process.
If MaxLength = 0 Then
Exit Function
End IfIf a null string is found, then leave the function.
Dim Substring As Variant
Dim x As IntegerAgain, declare the variables.
ReDim Substring(LBound(Result) To UBound(Result), LBound(Result) To MaxLength)
For i = LBound(Result) To UBound(Result)
Item = Result(i)
For x = 0 To UBound(Item)
Substring(i, x + 1) = Item(x)
Next x
Next iAgain, perform a FOR Loop for the updated values from the smallest subscript to the largest subscript of the array and store the result in the variable. It continues doing this until it finishes scanning through the whole set of data.
SplittedStrings = SubstringLater, store the produced result, the breakdown of the long string in the function.
End FunctionFinally, end the function.
Read More: Excel VBA: Split String into Cells (4 Useful Applications)
2. Insert Macro to Split a Range of String into Multiple Columns
Previous code splits all the strings that are available in your worksheet. But what if you don’t want to split all the strings from your spreadsheet? What if you want a specific range of strings to be split?
In this section, you will learn how to do that with VBA in Excel.
Steps:
- Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Then, copy the following code and paste it into the code window.
Option Explicit
Sub SplitStringRange()
Dim iSheet As Worksheet
Dim iRng As Range
Dim iValue As String
On Error Resume Next
Set iSheet = Worksheets(Application.InputBox(Prompt:="Please Enter Worksheet Name", Title:="Worksheet Name", Default:=ActiveSheet.Name, Type:=2))
If Err.Number <> 0 Then
iValue = MsgBox("Worksheet Not Available", vbRetryCancel)
If iValue = vbRetry Then SplitStringRange
Exit Sub
End If
On Error GoTo 0
Set iRng = (Application.InputBox(Prompt:="Please Select Range to Split", Title:="Range Selection", Default:=Selection.Address, Type:=8))
Set iRng = iSheet.Range(iRng.Address)
iRng.TextToColumns DataType:=xlDelimited, textqualifier:=xlTextQualifierDoubleQuote, consecutivedelimiter:=False, Tab:=False, semicolon:=False, comma:=True, Space:=False, other:=False, fieldinfo:=Array(Array(1, xlYMDFormat))
End SubYour code is now ready to run.
- After that, Run the macro as we showed you in the above section.
- If you have multiple macros in your Excel workbook under the same VBA project, then you will be asked to select the specific macro name that you want to execute. Select the relevant Macro Name from the Macros window and click Run.
- Then, there will be a pop-up Input box. Write the worksheet name where the values to be split exist. For instance, we want to split the string from the “String Range” worksheet, so we inserted “String Range” in the input box.
- Next, click OK.
- Again, a pop-up input box will appear. In that box, you have to insert the range of strings that you want to split.
- Select the range that you want to split the strings from by dragging. For instance, we want to split strings only from the range A1 to A3. So, we dragged only through the range A1 to A3 to define it as our input value.
- After you provide the range inside the input box, click OK.
Now, look at the following image.
Strings only from the range A1 to A3 are split now whereas the rest of the strings remain as they were before.
VBA Code Explanation
Option ExplicitFirst, forces to declare all the variables explicitly of the file.
Sub SplitStringRange()Then, provide a name for the sub-procedure of the macro.
Dim iSheet As Worksheet
Dim iRng As Range
Dim iValue As StringAfter that, declare the necessary variables for the macro.
On Error Resume NextIf any error occurs, then go to the next statement.
Set iSheet = Worksheets(Application.InputBox(Prompt:="Please Enter Worksheet Name", Title:="Worksheet Name", Default:=ActiveSheet.Name, Type:=2))Next, store the input box in a declared variable to insert the worksheet name along with the prompt message, title and other attributes.
If Err.Number <> 0 Then
iValue = MsgBox("Worksheet Not Available", vbRetryCancel)
If iValue = vbRetry Then SplitStringRange
Exit Sub
End IfIf the worksheet name provided is unavailable, then leave the sub-procedure.
On Error GoTo 0Then, disable the error handling in the current procedure.
Set iRng = (Application.InputBox(Prompt:="Please Select Range to Split", Title:="Range Selection", Default:=Selection.Address, Type:=8))
Set iRng = iSheet.Range(iRng.Address)
iRng.TextToColumns DataType:=xlDelimited, textqualifier:=xlTextQualifierDoubleQuote, consecutivedelimiter:=False, Tab:=False, semicolon:=False, comma:=True, Space:=False, other:=False, fieldinfo:=Array(Array(1, xlYMDFormat))
After that, store the input box in a declared variable to insert the range to split along with the prompt message, title and other attributes. Notice here, that we passed False as the value in all the properties except the property comma. As our separator is the comma (,), so comma holds the boolean value True in our code. You can modify this line of code according to your requirement.
End SubFinally, end the sub-procedure of the macro.
Read More: Excel VBA: Split String by Number of Characters (2 Easy Methods)
Conclusion
This article showed you 2 effective ways how to split the string into multiple columns in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
Related Articles
- How to Split a Cell into Two Rows in Excel (3 ways)
- Split Cell by Delimiter Using Excel Formula (8 Ways)
- How to Make Two Lines in One Cell in Excel (4 Methods)
- Excel VBA: Split String by Character (6 Useful Examples)
- How to Split One Cell into Two in Excel (5 Useful Methods)
- Split a single cell in half in Excel (diagonally & horizontally)
- How to Split Data with Formula in Excel (8 Easy Ways)
