[Office] Excel计算单元格中由逗号分隔的值个数

背景

现在有一组数据，特点为在单个单元格的数据通过 , 分隔，例如A1，A2，A3，A4，A5。现需要统计各行单元格中逗号分隔的值的个数。例如上述例子中，逗号分隔值个数应为5。

方法

键入如下公式：

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1

其中A1表示数据所在位置，通过具体情况指定即可，图片中为B2。

效果如下：

解释

以apple, orange,pair,peach为例

1. TRIM(A1) 表示将字符串中所有的空格剥除，结果为apple,orange,pair,peach

2. LEN(TRIM(A1))表示计算处理后的字符串长度，结果为24

3. SUBSTITUTE(TRIM(A1),",","") 表示将TRIM(A1)中所有的 , 替换为空，结果为appleorangepairpeach

4. LEN(SUBSTITUTE(TRIM(A1),",",""))表示计算步骤3结果的长度，结果为21

5. 步骤2结果与步骤4结果相减后，再加一，结果为4。

总结

即计算有多少个分隔符。如果n个值，则存在n-1个分隔符，所以最终结果需要+1。

此外，步骤3中的替换操作，可以根据需求灵活指定任意的分隔符，不仅仅为,