帆软 substitute_用SUBSTITUTE计数单元格中的项目

帆软 substitute

Do you use Excel's SUBSTITUTE function very often? It's a handy way to count items in a cell, when they're separated by commas or spaces. The examples below show different ways to use this function – have you tried the variation in the last example?

您是否经常使用Excel的SUBSTITUTE函数？ 当它们之间用逗号或空格分隔时，这是一种方便的方法，可对单元格中的项目进行计数。 以下示例显示了使用此功能的不同方法-您是否在上一个示例中尝试了变体？

计算单元格中的单词 (Count Words in a Cell)

In the first example, we'll count the items in a cell, when they are separated by commas. The items are in cell A2, and this formula is entered in cell B2:

在第一个示例中，当它们之间用逗号分隔时，我们将对它们进行计数。 这些项目在单元格A2中，并且此公式在单元格B2中输入：

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

= LEN(A2)-LEN(SUBSTITUTE(A2，“，”，“”))+ 1

This formula uses LEN and SUBSTITUTE to count the items.

此公式使用LEN和SUBSTITUTE来计数项目。

1. LEN(A2) – Counts the number of characters in cell A2
   LEN(A2)–计算单元格A2中的字符数
2. SUBSTITUTE(A2,",","") – Replace each comma with an empty string
   SUBSTITUTE(A2，“，”，“”)–用空字符串替换每个逗号
3. LEN(SUBSTITUTE(A2,",","") – Count characters in cell A2, with all the commas are removed
   LEN(SUBSTITUTE(A2，“，”，“”)–计数单元格A2中的字符，并删除所有逗号
4. Subtract amount 2 from amount 1, to get the number of commas
   从数量1中减去数量2，以得到逗号数
5. Add 1, to get