Learn about Split Function

Learn about VBA Split Function

In This Article, we will lear about following topic of Type Mismatch in VBA

1. Introduction about VBA Split
2. VBA Code Example for Basic understanding
3. VBA Code for Using a Comma as the Delimiter
4. VBA Code for Case-Sensitive and Case-Insensitive
5. VBA Code for Splitting by Multiple Delimiters
6. VBA Code for Combining Split with Join

Introduction

The Split function in VBA is used to break a string into an array of smaller strings, based on a specified delimiter (e.g., a comma, space, or any other character). It’s a powerful tool for parsing strings and extracting meaningful parts.

Syntax:

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

Parameters

1. expression (Required):
   The string you want to split.
2. delimiter (Optional):
   The character or string that determines where to split the expression.

• Default is a space (” “).

1. limit (Optional):
   The maximum number of substrings to return.

• Default is -1, meaning no limit (splits the entire string).

1. compare (Optional):
   Specifies the type of comparison for the delimiter:

• vbBinaryCompare (Default): Case-sensitive comparison.
• vbTextCompare: Case-insensitive comparison.

Return Value

• Returns a zero-based array of substrings.
  • The first substring is at index 0.
  • If the delimiter is not found, the entire string is returned as a single-element array.

Example

1. Basic Split Example:

Split a sentence into words using a space as the delimiter:

VBA Code:

Sub BasicSplit()

Dim text As String

Dim words() As String

Dim i As Integer

text = "VBA is Powerful"

words = Split(text, " ")

' Split the string into words

For i = LBound(words) To UBound(words)

MsgBox words(i)

' Outputs: "VBA", then "is", then "powerful"

Next i

End Sub

2. Using a Comma as the Delimiter

Split a comma-separated list:

VBA Code:

Sub SplitCommaSeprater()

Dim text As String

Dim item() As String

Dim i As Integer

text = "Apple,Banana,Mango"

item = Split(text, ",")

For i = LBound(item) To UBound(item)

MsgBox item(i)

Next i

End Sub

3. Case-Sensitive and Case-Insensitive Delimiters

VBA Code:

Sub CaseInsensitiveSplit()

Dim result() As String

Dim i As Integer

Dim text As String




text = "Hello,hello,HELLO"

result = Split(text, "hello", , vbBinaryCompare)




' Splits into: "Hello,", ",", ",HELLO" (case-insensitive)

For i = LBound(result) To UBound(result)

MsgBox result(i)




Next i




End Sub

4. Specifying a Limit

Only split the first two parts of a string:

VBA Code:

Sub SplitWithLimit()

Dim result() As String

result = Split("A-B-C-D", "-", 2)




MsgBox result(0) ' Outputs: "A"

MsgBox result(1) ' Outputs: "B-C-D"

End Sub

5. Splitting by Multiple Delimiters

VBA Split works with one delimiter at a time. For multiple delimiters, use Replace or loop through characters:

VBA Code:

Sub SplitMultipleDelimiters()

Dim text As String

Dim result() As String




text = "Apples,Oranges|Bananas Grapes"

text = Replace(text, "|", ",") ' Replace "|" with ","

text = Replace(text, " ", ",") ' Replace space with ","




result = Split(text, ",")




Dim i As Integer

For i = LBound(result) To UBound(result)

MsgBox result(i) ' Outputs: "Apples", "Oranges", "Bananas", "Grapes"

Next i

End Sub

6. Handling No Matches

If the delimiter isn’t found, the entire string is returned:

Sub NoDelimiterFound()

Dim result() As String

result = Split("HelloWorld", "-")




MsgBox result(0) ' Outputs: "HelloWorld"

MsgBox UBound(result) ' Outputs: 0 (Only one element in the array)

End Sub

7. Combining Split with Join

After splitting, you can use the Join function to combine the array back into a single string:

VBA Code:

Sub SplitAndJoin()

Dim items() As String

Dim result As String

Dim text As String




text = "Apple,Orange,Banana"




items = Split(text, ",")

result = Join(items, " & ") ' Combine with " & " as delimiter




MsgBox result ' Outputs: "Apple & Orange & Banana"

End Sub

Excel File for Download

Key Points

1. Zero-Based Array: The Split function returns a zero-based array, so the first item is array(0).
2. Delimiter Matters: If no delimiter is specified, it defaults to splitting by spaces.
3. Error Handling: Always check for out-of-bound errors when working with the resulting array (e.g., when the delimiter isn’t found).

The Split function is an essential tool in VBA for processing and manipulating strings. Use it to handle everything from simple string parsing to complex data extraction tasks.

Other Related Topics to Read

1. VBA String Operator : In VBA, the primary string operator is the concatenation operator (&), which is used to join two or more strings together.
2. VBA Loops : In VBA (Visual Basic for Applications), loops are used to repeat a block of code multiple times until a certain condition is met.
3. VBA For Loop : A For Loop in VBA (Visual Basic for Applications) is used to repeat a block of code a specified number of times. There are two main types of For loops in VBA
4. VBA Do Until Loop : In VBA (Visual Basic for Applications), the Do Until loop is used to repeat a block of code until a specified condition is True. Unlike the Do While loop, which runs while a condition is True, the Do Until loop keeps running as long as the condition is False and stops once the condition becomes True