How to use SPLIT function in VBA

Split function in VBA is used to split a text string based on a delimiter. This delimiter can be a special character like “,”, ” -“, “&”  or even text delimiters like “and”, “or”, “the”, “sample” etc. For example, lets takes a data set with Employee name, Designation and sales figures combined in one string as shown in the image below. This data is not laid out properly. Instead of manually editing such strings, we can use Split function. Split function in VBA will split the text and put it in respective columns. Let’s see how it works.

Syntax for this function: Split (text_string, delimiter, limit, compare)

Text String: String expression contains substrings and delimiters.

Delimiter: Characters used to identify substring limits. If omitted, the space character (” “) is considered as delimiter.

Limit: Maximum number of substrings into which the input string should be split. The default –1, indicates that the input string should be split at every occurrence of the Delimiter string.

Compare: Numeric value indicating the comparison to use when evaluating substrings.

We have data from Row 1 to 3 as shown in image above. We are interested in splitting the data and transfer it into a tabular format.

VBA Code to split a text string:

Step 1a: Define Variables

In the above code snippet, we define variables. we assign variable ‘splitnamesrow’  as 7. As we need the code to populate names from row 7.

Step 1b: Split function

In this step, we are looping from row 1 to row 3 (as we have data in these rows) and reading their values into the ‘txt’ variable. Now, we are using split function to split the text by taking its value to limiter variable.

Here argument 1 is ‘txt’. Argument 2 is ‘delimiter’ which is space (“ “) in the text string. Limit is ‘-1’ as default. So the text is split at every occurrence of space (our delimiter) in the string.

Step 1c: Populating the text after splitting

In this step, we are using For loop to transfer the data into a tabular format. UBound helps to find the size of an array.

Now we populate the data from row 7 to 10 by adding 1 to the variable ‘splitnamesrow’ and populating data from the limiter to the desired cells.

Example 2:

In this example, lets check on how we can use delimiter as text string and compare arguments.

As we can see from the above image, the data set contains “John and Peter” and “Rob AND Nick”. As we can observe that “and” in the data set is not same. We can use “and” as delimiter but this will ignore ‘AND’ in cell (A2), as Delimiter is case sensitive. To avoid being case sensitive, we can use text compare. With text compare active, the delimiter will not be case sensitive and matches the string “and”.

Here is the code snippet for limiter variable. The remaining vba code is same as in the Example 1 except the datarows are from 1 and 2 instead of 1 to 3 as we have data in Row1 and Row2 .

“limiter = split(txt, “and”, , vbTextCompare)”

Here ‘and’ is the delimiter and “vbTextCompare” is used as compare argument. The result is as shown in the image below.

Example 3:

In this example, lets check, how we can use delimiter and limit in Split function.

As we can see from the above image, the data set contains “Programmer is Kevin Peterson” and “Tester is Robert Miller”. We use ” “ (Space) as delimiter to split the text at every occurrence of delimiter but we are interested in keeping First Name and Last Name intact in one cell. To achieve this, we use limit argument.

Here is the code snippet for limiter variable. The remaining vba code is same as in the Example 1  except the “datarows” are from 1 and 2 instead of 1 to 3 as we have data in Row1 and Row2 only .

“limiter = split(txt, ” “, 3)”

Here, txt is the text string. ” ” is the delimiter and we are limiting the delimiter at 3rd occurrence from being split. The result is as shown in the image below.

Differences between “Text to Columns” & “Split” function:

1) Split function has Limit feature, where we can limit the text getting split precisely.

2) We can use more than one character as delimiter. Such as, “and”, “is” etc.

3) We can use “compare” to do a text compare or a binary compare in the string.

If you have any queries feel free to contact us here.