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 the number of items
   加1，得到项目数

修剪空间 (TRIM the Spaces)

If you're using space characters to separate the items in a cell, be sure to use the TRIM function, to clean up any leading, trailing, or extra spaces.

如果要使用空格字符分隔单元格中的项目，请确保使用TRIM函数清除所有前导，尾随或多余的空格。

Without TRIM, the item count could be incorrect. Here is an example without TRIM:

没有TRIM，项目计数可能不正确。 这是一个没有TRIM的例子：

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

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

The counts in rows 4 and 5 are too high, because of extra spaces.

由于多余的空间，第4行和第5行的计数太高。

Here's the revised formula to count items in a cell with space character separators:

这是修改后的公式，用于计算带有空格字符分隔符的单元格中的项目：

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

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

The counts with this formula, in column B, are correct, and the incorrect counts (with the old formula), are in column C, for comparison

用于比较的B列中此公式的计数正确，而C列中不正确的计数(具有旧公式)进行比较

检查最小计数 (Check for Minimum Count)

Instead of just counting the items in the cell, you can change the end of the formula, and check for a minimum item count.

您不仅可以对单元格中的项目进行计数，还可以更改公式的结尾并检查最小项目数。

In this example, a street name is entered in column A.

在此示例中，在A列中输入街道名称。

• There should be at least 3 items entered in the cell, such "123 Main Street".

  单元格中至少应输入3个项目 ，例如“ 123 Main Street”。

• If there are 3 or more items, there will be 2 or more spaces.

  如果有3个或更多的项目，将有2个或更多的空格 。

Change the end of the formula – instead of adding 1, to get the word count, check that the number of spaces is 2 or more

更改公式的末尾–代替加1来获取单词数，请检查空格数是否为2或更大

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))>=2

= LEN(A2)-LEN(SUBSTITUTE(A2，“，”，“”))> = 2

The results are TRUE or FALSE, and you could use a formula like this as a custom rule for  data validation in a cell.

结果为TRUE或FALSE，您可以使用像这样的公式作为自定义规则来进行单元格中的数据验证 。

检查最小计数版本2 (Check Minimum Count Version 2)

Another way to check for a minimum word count is with this shorter formula – thanks to UniMord for suggesting it.

检查最小字数的另一种方法是使用较短的公式-感谢UniMord的建议。

Instead of checking the cell contents with LEN, the formula replaces the 2nd space character (if there is one), and then compares with the original value.

该公式不使用LEN检查单元格内容，而是替换了第二个空格字符(如果有)，然后与原始值进行比较。

=TRIM(A2)<>SUBSTITUTE(TRIM(A2)," ","",2)

= TRIM(A2)<> SUBSTITUTE(TRIM(A2)，“”，“”， 2 )

这个怎么运作 (How It Works)

This formula uses 2 as the 3rd argument in SUBSTITUTE, to specify which space character (instance number) to replace.

该公式使用2作为SUBSTITUTE中的第三个参数，以指定要替换的空格字符(实例号)。

The second space is removed, and you can see the results of that step in column C(it's there as a demo only).

第二个空格已删除，您可以在C列中看到该步骤的结果(此处仅作为演示)。

Usually, we omit the 3rd argument, so ALL instances of the text are replaced. But in this case, it's helpful to just replace the 2nd space character, as our test.

通常，我们忽略第3个参数，因此将替换文本的所有实例。 但是在这种情况下，仅替换第二个空格字符作为测试是有帮助的。

• We want a minimum of 3 items, so there should be at least 2 space characters.

  我们至少需要3个项目，因此至少应有2个空格字符。

• If we replace the 2nd space, the result is different from the original value

  如果替换第二个空格，结果将与原始值不同

• So, if the original is not equal to the revised value, there are 3 or more items.
  因此，如果原件不等于修订值，则有3件或更多件。

下载样本文件 (Download the Sample File)

To download the Count Items in a Cell sample file, go to the Excel Files page on my Contextures website. In the Functions section, look for FN0053 - Check Item Count with SUBSTITUTE

要下载单元格样本文件中的计数项目，请转到Contextures网站上的Excel Files页面。 在“功能”部分中，查找“ FN0053-用SUBSTITUTE检查项目计数”

There are more SUBSTITUTE examples on my Contextures Blog., with a workbook that you can download, to try the examples from that article.

我的Contextures博客上还有更多SUBSTITUTE实例 ，您可以下载一个工作簿来尝试该文章中的实例。