Excel VBA: Split String by Character (6 Useful Examples)

This article illustrates how to split a string that is separated by a character using the Split function in Excel VBA. We’ll configure different arguments of the function to show some useful examples. Let’s dive into the examples to explore the techniques that can get your job done fast and easily.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Useful Examples to Split String by Character Using VBA in Excel

Introduction to VBA Split Function
The Split function in Excel VBA is used to split a string into substrings. The function returns a zero-based one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. The syntax of the VBA function is-

Split(expression, [delimiter, [limit, [compare]]])

Here,
expressionThis required parameter represents a text string that contains substrings and delimiter. If the string is empty, the function will also return an empty array.

delimiter– A string character that is used to split the string into substrings. If omitted the function will use a space character as the delimiter. And if it is an empty string, it’ll return the original string as the output.

limit– It represents the number of substrings to return in the output. If omitted, the function will return all the substrings.

compare– It has several values. We can use vbBinaryCompare for a casesensitive delimiter and vbTextCompare for a case-insensitive delimiter in the Split function.

The last three parameters are optional to work with the VBA Split function.

Write Code in Visual Basic Editor
To split string by character, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

  • Go to the Developer tab from the Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.


1. Split Words of a String by Space Character 

Task: Split a text string in cell B3 by space character and output the substrings in cells D3: I3 (the string in cell B3 has 6 words in it).

Solution: Use the Split function without any delimiter. As we know, if we omit the delimiter argument in the function, by default, it’ll use the space character as the delimiter.

Excel VBA Split String by Character

Code: Insert the following code in the visual basic editor and press F5 to run it.

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

Output: The above code outputs all the substrings in cells D3:I3.

Excel VBA Split String by Character

Read More: Excel VBA: Split String into Cells (4 Useful Applications)


2. Specify the Number of Parts to Split a String by Character 

Task: Split a text string in cell B3 by space character and output the 3 substrings in cells D3: F3 (the string in cell B3 has 6 words in it)

Solution: To specify the number of substrings in the Split function, we need to put the limit argument as 3. And use the Split function without any delimiter so that it uses the space character as the default delimiter.

Excel VBA Split String by Character

Code: Insert the following code in the visual basic editor and press F5 to run it.

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

Output: The above code outputs 3 substrings in cells D3:F3.

Excel VBA Split String by Character

Read More: Excel Split Cell by Delimiter Formula


3. Split a String by Character and Return a Specific Part 

Task: Get the flat no from the addresses in cells B3:B5.

Problem Analysis: Here the address is a commaseparated string. So, we need to use the delimiter argument as a comma (“,”).

Solution: The Spit function returns a zero-based (start from 0) one-dimensional array. Each element of the array is a substring separated by a predefined delimiter. So, to solve the above task, we need to output the 2nd element of the array.

Excel VBA Split String by Character

Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub SplitStringbyCharacter()
Range("D3").Value = Split(Range("B3"), ",")(1)
Range("D4").Value = Split(Range("B4"), ",")(1)
Range("D5").Value = Split(Range("B5"), ",")(1)
End Sub

Output: The above code separated the flat no from the addresses and output in cells D3:D5.

Excel VBA Split String by Character

Read More: Excel Formula to Split String by Comma (5 Examples)


4. Use of Non-Printable Character to Split a String

Task: Split a text string into substrings that are separated by a non-printable character Vbcrlf and output the substrings in cells B2:B4.

Solution: Here, the string is: “Excel VBA” & vbCrLf & “Split String by Character” & vbCrLf & “Non-printable”. We need to use the Vbcrlf (Visual Basic Carriage Return Line Feed) as the delimiter in the Split function.

Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub SplitStringbyCharacter()
Dim SubStringArr() As String, SrcString As String
SrcString = "Excel VBA" & vbCrLf & "Split String by Character" & vbCrLf & "Non-printable"
SubStringArr = Split(SrcString, vbCrLf, , vbTextCompare)
For I = 0 To UBound(SubStringArr)
    Cells(I + 2, 2).Value = SubStringArr(I)
Next I
End Sub

Output: The above code separated the string and output in cells B2:B5.

Read More: Excel Formula to Split: 8 Examples


5. Count the Number of Elements in a String Split by Character 

Task: Count the number of words in the text string in cell B3 that is split by the space character.

Solution: Use the UBound function in the code to get the number of substrings in the input array.

Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub SplitStringbyCharacter()
Dim SubStringArr() As String
SubStringArr = Split(Range("B3"))
Range("D3") = UBound(SubStringArr())
End Sub

Excel VBA Split String by Character

Output: In cell D3, we’ve successfully printed the number of words and the output is 6.


6. Split Cell Address to Get Row and Column Numbers of the Active Cell 

Task: Get the row and column number of the active cell in the worksheet using the Split function in Excel VBA.

Problem Analysis: Run the following code in the visual basic editor to get the cell address of the active cell in the worksheet.

Sub GetRowColNumberfromCellAddress()
    MsgBox Selection.Address
End Sub

The output is here-

We see that the cell address shown in the Msg Box is $B$2, in an absolute cell reference form.

Solution:  We can use VBA Split function to split the cell address by the delimiter “$” to separate the row and column number.

Code: Insert the following code in the visual basic editor and press F5 to run it.

Sub GetRowColNumberfromCellAddress()
    rowNumber = Split(Selection.Address, "$")(1)
    colNumber = Split(Selection.Address, "$")(2)
    MsgBox "Row Number: " & rowNumber & vbCrLf & _
    "and" & vbCrLf & "Column Number: " & colNumber
End Sub

Excel VBA Split String by Character

Output: We’ve successfully output the row and column numbers i.e., B and 2 of the active cell address B2 in the Msg Box.

Excel VBA Split String by Character

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


Things to Remember

  • If the specified delimiter doesn’t exist in the source string, the Split function will return the string as it is.
  • If the compare argument of the Split function is omitted, the default value is

Conclusion

Now, we know how to split string by character using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo