2

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!

Wisp
  • 198
  • 1
  • 11

1 Answers1

4

Multiple ways, one is to use FILTERXML():

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    Amazing. Thank you so much. Didn't think to use `FILTERXML()` here. The first solution works perfectly. Much appreciated for the quick response. – Wisp Feb 14 '22 at 10:41