VBA Function: Split

The VBA function Split allows you to divide a string into an array of values based on a delimiter.

Usage:

Split(text, delimiter)

or

Split(text, delimiter, limit)

Example of Usage

Dividing the string www.excel-pratique.com with the delimiter . to obtain an array of the 3 substrings:

Sub example()

    myText = "www.excel-pratique.com"

    myArray = Split(myText, ".")

    MsgBox myArray(0) 'Returns: www
    MsgBox myArray(1) 'Returns: excel-pratique
    MsgBox myArray(2) 'Returns: com

End Sub

If necessary, you can limit the number of substrings in the array:

Sub example()

    myText = "www.excel-pratique.com"

    myArray = Split(myText, ".", 2)

    MsgBox myArray(0) 'Returns: www
    MsgBox myArray(1) 'Returns: excel-pratique.com

End Sub

If you only need to retrieve a single substring (in this example, the middle substring excel-pratique), you can directly specify the element of the array to retrieve:

Sub example()

    myText = "www.excel-pratique.com"

    middle = Split(myText, ".")(1)

    MsgBox middle 'Returns: excel-pratique

End Sub