VBA Function: Split


The VBA Split function is used to split a string of characters according to a delimiter to produce an array of values.

Split syntax

Split(text, separator)


Split(text, separator, limit)

VBA Split example

Separating the site name "" with the delimiter "." to get 4 substrings in the array:

Sub SplitExample1()
      text = ""
      array = Split(text, ".")
      MsgBox array(0) 'Returns: www
      MsgBox array(1) 'Returns: moonexcel
      MsgBox array(2) 'Returns: com
      MsgBox array(3) 'Returns: ua
End Sub

If necessary, you can limit the number of elements in the array:

Sub SplitExample2()
      text = ""
      array = Split(text, ".", 2)
      MsgBox array(0) 'Returns: www
      MsgBox array(1) 'Returns:
End Sub

If you only need to retrieve a single substring (in this example, the middle substring "moonexcel"), you can directly specify an array element to retrieve it:

Sub SplitExample3()
      text = ""
      middle = Split(text, ".")(1)
      MsgBox middle 'Returns: moonexcel
End Sub
The reverse function that groups values from an array into a string is the JOIN function.