Excel VBA Split Function
Excel VBA Split Function: The Split function in VBA is used to split a string into several substrings and return a one-dimensional array of substrings.
Syntax
Split (Expression, [Delimiter], [Limit], [Compare])
Parameter
Expression (required) – This parameter represents a text string that you want to split.
Delimiter (optional) – This parameter specifies where the supplied Expression should be split. By default, it set to space “ “.
Limit (optional) – This parameter specifies the maximum number of substrings to be returned. By default, this parameter is set to -1.
Compare (optional) – This parameter represents the type of comparison that should be used for the substrings. The default value is set to vbBinaryCompare.
It can have the following values:
vbBinaryCompare – It performs a binary comparison
vbTextCompare – It performs a text comparison
vbDatabaseCompare – It performs a database comparison
Return
This function returns a one-dimensional array of substrings after splitting a string into a number of substrings.
Example 1
Sub SplitFunction_Example1()
' Splitting the specified string into substrings.
Dim names() As String
names = Split("Amar Akbar Anthony")
' The array "names" now has split and has length 3
'it will return Amar
Cells(1, 1).Value = names(0)
'it will return Akbar
Cells(2, 1).Value = names(1)
'it will return Anthony
Cells(3, 1).Value = names(2)
End Sub
Output
| Amar |
| Akbar |
| Anthony |

Example 2
Sub SplitFunction_Example2()
' Split the specified string into substrings.
Dim names() As String
'passing delimiter as comma ","
address = Split("House No:823, C-Block, Plot-2, Rajouri Garden, Delhi", ",")
' The array "names" now has split and has length 5
'it will return House No:823
Cells(1, 1).Value = address(0)
'it will return C-Block
Cells(2, 1).Value = address(1)
'it will return Plot-2
Cells(3, 1).Value = address(2)
'it will return Rajouri Garden
Cells(4, 1).Value = address(3)
'it will return Delhi
Cells(5, 1).Value = address(4)
End Sub
Output
| House No:823 |
| C-Block |
| Plot-2 |
| Rajori Garden |
| Delhi |

