How to automate split by delimiter in Excel equivalent of SPLIT in gSheets

Question

I recently switched from using Google Sheets exclusively to using Excel, so I'm still getting used to several items being missing. I must divide cells in Excel using a space delimiter (" ").

I'm trying to figure out how to do this using an Excel formula. The Google Sheets equivalent of =SPLIT(#REF, "") in Excel. Although Excel includes a "Text to Columns" option, I would like a formula because I'm aiming to totally automate a project to lower the likelihood of human error.

Additionally, I need to maximize the number of splits to 4, so it ignores everything after the 4th split.

I've tried a few things, such as using

 =LEFT(#REF,FIND(" ",#REF)),=RIGHT(#REF,FIND(" ",#REF)), and =MID(#REF, SEARCH(" ",#REF) + 1, SEARCH(" ",#REF,SEARCH(" ",#REF)+1) - SEARCH(" ",#REF) - 1). 

The issue is, the number of spaces within the cell can vary. Please see an example table below:

ToSplit	Split #1	Split #2	Split #3	Split #4
Hello	Hello
World	World
Hello World	Hello	World
Hello World FOO BAR BAZ	Hello	World	FOO	BAR
This Data Wants To Be Different	This	Data	Wants	To

Is there any way to obtain this functionality within Excel, please? 

Answer 1

Multiple ways, one is to use FILTERXML():

 

Formula in B2:

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A2," ","</s><s>")&"</s></t>","//s[position()<5]"))

This assumes ms365's spilling dynamic arrays. However, you could also use, for example:

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A2," ","</s><s>")&"</s></t>","//s["&COLUMN(A1)&"]"),"")

Drag down and right.

---

For more information on FILTERXML() and another custom SPLIT() function, see this Q&A.