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

Method 1 – Embed VBA to Split a String into Multiple Columns in Excel

We have one long string, John Cena,23/04/1977,Wrestler,USA in a worksheet named “String” in our Excel workbook. We want sub-strings “John Cena”, “23/04/1977”, “Wrestler” and “USA” separated into columns. The substrings are divided (delimited) by commas.

Steps:

Press Alt + F11 or go to the Developer tab and select Visual Basic to open Visual Basic Editor.

From the menu bar, click Insert and select Module.

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, select Run and choose Run Sub/UserForm, or click on the small Run icon in the sub-menu bar to run the macro.

You should get an Excel pop-up input box.
Write the worksheet name where the values to be split exist. We want to split the string from the “String” worksheet, so we inserted “String” in the input box.
Click OK.

The long string that is separated by commas (,) will be split.

Read More: Excel VBA: Split String into Rows

Part 2 – VBA to Split Multiple Strings into Multiple Columns in Excel

We have multiple long strings with a comma delimiter (,) 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.
Insert the relevant sheet name in the pop-up input box.

The multiple strings from your dataset will be split into multiple columns.

VBA Code Explanation

Option Explicit

Declares all the variables explicitly of the file.

Private Const SourceColumn As String = "A"

Defines the source column, column A, as the column that holds the strings to split. You can pass the column address according to your dataset.

Private Const Separator As String = ","

Defines the separator. We worked with the comma (,) as our separator.

Public Sub SplitString()

Provides 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

Declares the necessary variables for the macro.

On Error GoTo SplitStringErr

If any error occurs, goes to the SplitStringErr statement.

SheetName = VBA.InputBox("Please Enter Worksheet Name")

Stores 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, leaves 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. It counts all the rows of the dataset, then starts splitting the source column and passes 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

Initiates 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

Declares 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

Continues the code execution process.

If MaxLength = 0 Then
   Exit Function
End If

If a null string is found, leaves the function.

Dim Substring As Variant
Dim x As Integer

Declares 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

Performs 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

Stores the produced result, the breakdown of the long string in the function.

End Function

Ends the function.

Method 2 – Insert a Macro to Split a Range of Strings into Multiple Columns

Steps:

Open the Visual Basic Editor from the Developer tab and insert a Module in the code window.
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 Sub

Your code is now ready to run.

Run the macro.
If you have multiple macros in your Excel workbook under the same VBA project, 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.

You will get a pop-up Input box. Write the worksheet name where the values to be split exist. We want to split the string from the “String Range” worksheet, so we inserted “String Range” in the input box.
Click OK.

A pop-up input box will appear. Insert the range of strings that you want to split.
Select the range that you want to split the strings from by dragging. We want to split strings only from the range A1 to A3, so we dragged through the range A1 to A3 to define it as our input value.
Click OK.

Here’s the result.

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

Declares all the variables explicitly of the file.

Sub SplitStringRange()

Provides a name for the sub-procedure of the macro.

Dim iSheet As Worksheet
Dim iRng As Range
Dim iValue As String

Declares the necessary variables for the macro.

On Error Resume Next

If any error occurs, goes to the next statement.

Set iSheet = Worksheets(Application.InputBox(Prompt:="Please Enter Worksheet Name", Title:="Worksheet Name", Default:=ActiveSheet.Name, Type:=2))

Stores 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, leaves the sub-procedure.

On Error GoTo 0

Disables 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))

Stores the input box in a declared variable to insert the range to split along with the prompt message, title and other attributes. 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.