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