VBA to Split String into Multiple Columns in Excel (2 Ways)

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.

How to Split String into Multiple Columns with VBA in Excel: 2 Ways

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 Function

Your 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

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 Explicit

First, 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 Output

After that, declare the necessary variables for the macro.

On Error GoTo SplitStringErr

If 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 Sub

If 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 With

This 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, vbCritical

The SplitStringErr statement – throws the error message with the description in the MsgBox.

Private Function SplittedStrings(iRng As Range, ByRef MaxLength As Integer) As Variant

Next, 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 Variant

Then, 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 If

This 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 If

Starts 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 If

Splits the stored value and again store it in different variable to utilize later.

continue:
  Next i

Keep continues the code execution process.

If MaxLength = 0 Then
   Exit Function
End If

If a null string is found, then leave the function.

Dim Substring As Variant
Dim x As Integer

Again, 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 i

Again, 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 = Substring

Later, store the produced result, the breakdown of the long string in the function.

End Function

Finally, end the function.

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 pasteit 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 Sub

Your 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 Explicit

First, 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 String

After that, declare the necessary variables for the macro.

On Error Resume Next

If 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 If

If the worksheet name provided is unavailable, then leave the sub-procedure.

On Error GoTo 0

Then, 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 Sub

Finally, end the sub-procedure of the macro.

Download Workbook

You can download the free practice Excel workbook from here.

Split String into Multiple Columns.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!