Excel / VBA Split – Break a String with Examples

The Split function is used to break a given string. It returns an array of substrings.

What is Split function in VBA

  • The Split function is used to break a given string.
  • It returns an array of substrings.
  • A delimiter can be specified e.g. dash, comma, dot. Default is space
  • You may also limit the number of splits.
  • The returned array is one-dimensional.
  • It’s index starts at 0.
  • The examples below show its usage in VBA as well as Excel data


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

An example of VBA Split with expression only

We only passed the required argument in the Split function i.e. string to split.

See the code and output:



How did it work?

  • The string is passed to the Split function.
  • It broke the whole string as we did not limit the number of splits.
  • As no delimiter is specified, so string is broken by space.
  • In the message box, we specified returned array’s zero element. It’s value “VBA” is displayed.

Displaying all elements of the array returned by Split

This time we displayed all elements of the array in the message box. For that, a For..Each loop is used after splitting the string.

VBA code:



Splitting a string by dash delimiter in an Excel sheet

For this example, we will get the A2 cell text and use it in the Split function.

The text contains dashes. We will break the text by dash delimiter in the Split function and display the split values as follows:



Limiting the number of splits example

In this example, we used the third argument limit in the Split function.

We will specify the dash as a delimiter and 2 splits. For the text to be broken, we used the same Excel cell as in the above example:



You can see, the string is split into two parts only. The second part has a dash.

Using compare argument in Split

The compare argument allows you to perform a case-insensitive split, if you provide a string as a delimiter.

The default split is case-sensitive.

The compare argument has four possible values:

  • vbUseCompareOption
  • vbBinaryCompare
  • vbTextCompare
  • vbDatabaseCompare

By using the third option, vbTextCompare, you may perform case-insensitive split.

To understand, consider this string:

str = “This is a car. this is a toy. This is good. this is bad. This is awesome.”

We will perform default and by vbTextCompare in the Split function and see the difference in results:




By vbTextCompare:



As we used “this” as the delimiter, the default compare value that performs case-sensitive split – it broke only where “this” “occurred.

On the other hand, as used vbTextCompare, it broke string from all instances of “this” or “This”.