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 |