Split Strings based on Delimiter in Excel VBA

In this article, you will learn about split strings based on Delimiter in Excel Sheet using Excel VBA.

Split Function in Excel VBA

It helps in separating the component parts from within a standard text string where each component uses a specific delimiter character. It is easier to use than writing code to search for the delimiters in the string and then extracting the values. Split function can be used in reading a line from a Comma-Separated Value (CSV file) or a  list of mailing address that is all on one line.

Syntax

Split expression, delimiter[optional], limit[optional], compare[optional]

Let us understand the parameters in detail

• Expression – The string of text.
• Delimiter– It is a string or non-printable character. In case no delimiter character provided, and then the default of space is used.
• Limit– It indicates the number of splits to be made
• Compare – When the delimiter is a text character, it is used to toggle whether the delimiter is case-sensitive or not.

Split Strings based on Delimiter in Excel VBA

Let us see with an example how to split strings based on Delimiter.

Sample program to split strings based on Delimiter in Excel VBA.

Code:

Sub DelimiterSplit()

Dim MyArray() As String, MyString As String, I As Variant, N As Integer

MyString = "[email protected];[email protected];[email protected];[email protected]"

MyArray = Split(MyString, ";")

ActiveSheet.UsedRange.Clear

For N = 0 To UBound(MyArray)

    Range("A" & N + 1).Value = MyArray(N)

Next N

End Sub