Having recently moved from full-time Google Sheets -> Excel, I’m still getting used to a few things being missing… I’m needing to split cells in Excel using a delimiter of space (" ").
I’m looking for a way to do this with a formula in Excel. The Excel equivalent of =SPLIT(#REF, " ") in Google Sheets. I’m aware Excel has a "Text to Columns" feature, but I’m trying to fully automate a project, to reduce the chance of human error, so would be after a formula instead.
Additionally, I need to maximise 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? Thanks!
>Solution :
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.
More information on FILTERXML() and another custom SPLIT() function, see this Q&A.