VBA separates a string with spaces

Question

I need a function in excel that I can call and pass to a cell. Input data:

Firstname Lastname [e-mail] Firstname midname Lastname [e-mail] 

The number of gaps between them is random. The output should be just an array. An array can be of any length, since I do not know what the lines look like. The output should be:

 Firstname, Lastname, [e-mail] Firstname, midname, Lastname, [e-mail] 

I will call a function from one cell, for example =MySplitFunction(A1) , and this should put the name in A1, Lastname in B1 and [e-mail] in C1. I created a new module and tried the following code:

 Function MySplitFunction(s As String) As String() MySplitFunction = Split(s, " ") End Function 

Which gives me a conclusion

 Firstname 

How do I get it to return the whole array? Is it even possible to write a function in one cell that will put the material in the cells next to it?

EDIT:

Answer 1

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

To remove multiple spaces, you can change your code as follows (not super efficient, but it 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 space End Function 

Answer 2

Alternative solution:

• use RegEx as a first step to remove all spaces.
• split the result of the step first based on single spaces to the left
• In addition, because you need to return a different text element to different cells, which will allow the additional parameter of the function to be resolved.

This is the suggested function:

 Public Function MySplitFunction(sMark As String, nTh As Integer) As String On 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 Function EH: MySplitFunction = "" End Function 

and this screenshot shows how it works:

Important! When calling a function in Excel, use a comma to separate the parameters (instead of the presented half-column because of the locally-national version of excel that I use).