How to use vba split string in Excel

In VBA, the Split function is used to divide a string into multiple substrings based on a specified delimiter. It returns an array of substrings extracted from the original string. Each substring is separated by the delimiter character or string. If you have a string containing comma-separated values or any other delimited data, you can use Split to extract individual values for further processing.

In the context of a tutorial, the usage of the Split function in VBA for a data set that involves the sentence “The code Splits a string in Excel” could be to demonstrate how to split a sentence into individual words and count the occurrence of each word. For this, we have 5 methods

Method 1: Split String By Character Using VBA

In this method, we are going to use the Split function of VBA to split the text by each word.

Step 1 – Type Substring

• First type the word Substring in the cell D2

Step 2 – Go to the Developer Tab

• Now go to the Developer tab in the ribbon
• Afterward, from the developer tab click on Visual Basic
• After clicking on Visual Basic a window will be appeared

Step 3 – Click on Insert

• Now click on Insert, and a drop-down menu will appear
• From the drop-down menu Click on the Module
• Afterward, a Module window will appear 

Step 4 – Type the Code

• Now Type the code in the Module window

Sub SplitStringbyWord() Dim SubStringArr() As String SubStringArr = Split(Range(“A3”)) For I = 0 To UBound(SubStringArr) Cells(3, I + 4).Value = SubStringArr(I) Next I End Sub

Step 5 – Click on the Run Button

• After typing the code click on the Run Button and then click on the Sheet 
• After clicking on the Sheet you will get the result

Method 2: Split string by space character Using VBA

Step 1 – Type Substring

• First type the word Substring in the cell D2

Step 2 – Go to the Developer Tab

• Now go to the Developer tab in the ribbon
• Afterward, from the developer tab click on Visual Basic
• After clicking on Visual Basic a window will be appeared

Step 3 – Click on Insert

• Now click on Insert, and a drop-down menu will appear
• From the drop-down menu Click on the Module
• Afterward, a Module window will appear 

Step 4 – Type the Code

• Now Type the code in the Module window

Sub SplitStringbyCharacter() Dim SubStringArr() As String SubStringArr = Split(Range(“A3”), , 3) For I = 0 To UBound(SubStringArr) Cells(3, I + 4).Value = SubStringArr(I) Next I End Sub

Step 5 – Click on the Run Button

• After typing the code click on the Run Button and then click on the Sheet 
• After clicking on the Sheet you will get the result

Explanation of the code used in method 1:

The code used in the first method is given below;

Sub SplitStringbyWord() Dim SubStringArr() As String SubStringArr = Split(Range(“A3”)) For I = 0 To UBound(SubStringArr) Cells(3, I + 4).Value = SubStringArr(I) Next I End Sub

This code defines a subroutine named “SplitStringbyWord” that splits a string in cell A3 into separate words and stores them in an array. The words are then written to cells starting from column D (4th column) in row 3.

Explanation of the code used in method 2:

The code used in the second method is as follows;

Sub SplitStringbyCharacter() Dim SubStringArr() As String SubStringArr = Split(Range(“A3”), , 3) For I = 0 To UBound(SubStringArr) Cells(3, I + 4).Value = SubStringArr(I) Next I End Sub

The code declares an array of strings named SubStringArr to store the split substrings. The Split function is used to split the contents of cell A3 into individual characters. The additional argument Limit is set to 3, limiting the function to return a maximum of three substrings.

A For loop is initiated to iterate over each element in the SubStringArr array. Inside the loop, each character is assigned to cells in the same row (row 3) but in consecutive columns, starting from column D (column index 4)

The loop continues to the next element until all characters have been placed in separate cells