在不使用 VBA 的情况下在 Excel 中拆分字符串(单元格)(例如对于数组公式) [英] Split a string (cell) in Excel without VBA (e.g. for array formula)

问题描述

我在 Excel 电子表格中有数据，其中包含分隔字符串.为简单起见，可以想像 "4#8#10" 之类的东西，具有任意数量的片段.

I have data in an Excel spreadsheet with delimited strings. For simplicity, think of something like "4#8#10", with an arbitrary number of pieces.

有什么方法可以将其拆分为数组以与数组公式一起使用?例如，类似于 SUM(SplitStr(A1,"#")).(这不起作用 - 它只返回 "4".)

Is there any way to split this into an array for use with an array formula? For example, something like SUM(SplitStr(A1,"#")). (This does not work - it just returns "4".)

有什么好的方法可以解决这个问题吗?我的理想解决方案最终会得到一个数组，就好像用户输入了 {4,8,10} 用于数组公式一样.

Is there a good way of approaching this? My ideal solution would end up with an array, as if the user entered {4,8,10} for use in array formulas.

我的工作表的应用程序不允许基于分隔符的文本列和求和.如果可能，我宁愿不使用 VBA，因为我不会成为工作表的主要消费者.

The application of my sheet does not allow for delimiter-based text to columns and summing that. I would prefer to not use VBA if possible as I will not be the primary consumer of the sheet.

谢谢，特里

推荐答案

总结条目 4,8,10 你可以使用类似的东西:

To sum the entries 4,8,10 you could use something like:

=SUMPRODUCT(1*TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99)))

返回的数组是一个文本数组，所以开头的1*是将它们转成数字的一种方式

The array that is returned is a text array, so the 1* at the beginning is one way to convert them to numbers

这部分:

TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99))

返回数组:

{"4";"8";"10"}

在它前面加上 1*:

{4;8;10}

编辑 六年后，可以使用更简洁的公式来创建数组:

Edit After six years, more succinct formulas are available to create the array:

使用 Excel O365 和 SEQUENCE 函数:

With Excel O365 and the SEQUENCE function:

=1*(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),IF(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)=1,1,(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)-1)*99),99)))

使用 Excel 2010+ for Windows (非 MAC 版本) 和 FILTERXML 函数:

With Excel 2010+ for Windows (not MAC versions) with the FILTERXML funcdtion:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"#","</s><s>") & "</s></t>","//s")

请注意，FILTERXML 函数将值提取为数字，而第一个公式将数字提取为文本，在用于数字公式之前必须将其转换为数字.

Note that the FILTERXML function extracts the values as numbers, whereas the first formula extracts the numbers as text which must be converted to numbers before being used in a numeric formula.

这篇关于在不使用 VBA 的情况下在 Excel 中拆分字符串(单元格)(例如对于数组公式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！