VBA-Excel: Array Functions – Split()

Description:

 Split() Function returns one dimensional array containing the substrings, after splitting the main string based upon the delimiter provided.

Format:

Split(strMainString [, delimiter[,intlimit[, vbCompare]]])

Arguments:

• str­Main­String
  • Manda­tory
  • Type: String
  • String which will be split

• delimiter
  • Optional
  • Type: Any
  • The expression based on which the strMainString will get split
• intLimit
  • Optional
  • Type : Numeric
  • No of substring to be returned.
• Com­pare
  • Optional
  • Type: Numeric
  • The type of com­par­i­son to find the string in the main string, like vbBina­ryCompare ( Value =0), vbTextCom­pare (value=1).

Example :

 
Function FnSplit()
    Dim strMainString
    Dim strTemp    
    strMainString = "This is Split Function Example"    
    arrSplit = Split(strMainString, " ")  
    For i = 0 To UBound(arrSplit)
        strTemp = strTemp & arrSplit(i) & "  " & vbCrLf
    Next    
    MsgBox strTemp
End Function