excel - 如何在 Excel 中自动按分隔符拆分(相当于 gSheets 中的 =SPLIT)

Question

最近从全职 Google Sheets -> Excel 转移，我仍然习惯于缺少一些东西......我需要使用空格分隔符(“”)在 Excel 中拆分单元格。
我正在寻找一种使用 Excel 中的公式执行此操作的方法。 =SPLIT(#REF, " ") 的 Excel 等价物在谷歌表格中。我知道 Excel 具有“文本到列”功能，但我正在尝试完全自动化项目，以减少人为错误的机会，因此将在公式之后。
此外，我需要将拆分次数最大化为 4，因此它会忽略第 4 次拆分后的所有内容。
我已经尝试了一些东西，例如使用 =LEFT(#REF,FIND(" ",#REF)) , =RIGHT(#REF,FIND(" ",#REF)) , 和 =MID(#REF, SEARCH(" ",#REF) + 1, SEARCH(" ",#REF,SEARCH(" ",#REF)+1) - SEARCH(" ",#REF) - 1) .问题是，单元格内的空格数可能会有所不同。请参阅下面的示例表:


分开
拆分 #1
拆分 #2
split #3
split #4


你好
你好



世界
世界



Hello World
你好
世界


Hello World FOO BAR BAZ
你好
世界
食品
酒吧

这个数据想要与众不同
这个
数据
想要
至


请问有什么方法可以在Excel中获得这个功能吗？谢谢!

Answer 1

多种方式，一种是使用FILTERXML() :
B2 中的公式:

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

这假设 ms365 的溢出动态数组。但是，您也可以使用，例如:

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

向下和向右拖动。

更多信息 FILTERXML()和另一个自定义 SPLIT()功能见 this问答。