VBA split string by spaces

• Enter you input data in A1
• Select the B1:D1 range
• enter your formula =MySplitFunction(A1)
• make it an array formula by pressing CTRL + SHIFT + ENTER instead of just ENTER.

To remove the multiple spaces, you could amend your code like this (not super efficient but works):

Function MySplitFunction(s As String) As String()    Dim temp As String    Do      temp = s      s = Replace(s, "  ", " ") 'remove multiple white spaces    Loop Until temp = s    MySplitFunction = Split(Trim(s), " ") 'trim to remove starting/trailing spaceEnd Function

Alternative solution is to:

1. use RegEx as a first step to remove all spaces
2. split result of step first based on single spaces left
3. moreover, because you need to return different element of the text in different cells than additional function parameter will solved that.

This is proposed function:

Public Function MySplitFunction(sMark As String, nTh As Integer) As StringOn Error GoTo EH    'regexp declaration    Dim objRegExp As Object    Set objRegExp = CreateObject("vbscript.regexp")    Dim tmpTXT As String    Dim tmpArr As Variant    With objRegExp        .Global = True        .Pattern = "\s+"        tmpTXT = .Replace(sMark, " ")    End With    tmpArr = Split(tmpTXT, " ")    MySplitFunction = tmpArr(nTh - 1)Exit FunctionEH:    MySplitFunction = ""End Function

and this is screen shot presenting how it works:

Important! when calling function in Excel use comma to separate parameters (instead of presented semi-colon due to local-national version of excel I use).