How do I create a multi-cell array formula in Excel whose input is just one cell?

Question

I have a cell with a spaced delimited string say "NULL 9"

Say this data is stored in A1.

I have a function called

Function splitCell(str As String) As String()
  splitCell = split(str, " ")
End Function

I am trying to break the cell into a string array, what I then want to do is to have a formula such as

{=splitCell(A1)}

at the locations A2 and A3, so that A2 would contain "NULL" and A3 would contain "9". Where am I going wrong? Please help. Thanks

Answer 1

You can do this entirely with Excel formulas:

A2: =LEFT(A1,FIND(" ",A1))
A3: =MID(A1,FIND(" ",A2)+1,LEN(A1)-FIND(" ",A2))

This should give you the result you are after.

Answer 2

Sorry, missed that you wanted to expand beyond the simple version.

Function splitCell(str As String, pos As Integer) As String
    Dim strarray() As String
    strarray = Split(str, " ")
    splitCell = strarray(pos)
End Function

A2: =splitCell(A1,0)

You will need to put the function into a module (not in the sheet code)

Answer 3

You need to assign the result of the Split to a variant, and if you want the result to be vertical rather horizontal you need to transpose it.

Public Function SplitCell(rng As Range) As Variant
    SplitCell = Application.Transpose(Split(rng))
End Function