A client has an Excel file that needs to have some names scrubbed. In particular, we're trying to isolate suffixes on names (e.g., Jr., Sr., III, etc.) Names are ALWAYS formatted LastName, FirstName, Suffix in the cell, and I am trying to count the number of commas in a cell. If the cell has more than one comma in it, I can identify that cell as having a name suffix.
客户端有一个Excel文件，需要擦除一些名称。特别是，我们试图隔离名称上的后缀（例如，Jr。，Sr.，III等）。名称总是格式化为LastName，FirstName，后缀在单元格中，我试图计算逗号的数量一个细胞。如果单元格中有多个逗号，我可以将该单元格标识为具有名称后缀。
However, all of the COUNT functions in Excel count instances of CELLS, not characters within cells. Is there a function that counts occurrences of specific characters in a cell and returns that count?
但是，Excel中的所有COUNT函数都计算CELLS的实例，而不是单元格中的字符。是否有一个函数可以计算单元格中特定字符的出现次数并返回该计数？
2 个解决方案
#1
8
You can get the number of characters in the cell and then compare that to the number of characters in the cell if you substituted out all the commas with empty spaces:
您可以获取单元格中的字符数，然后将其与单元格中的字符数进行比较，如果您用空格代替所有逗号：
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
This is also a neat way to get word counts in excel by counting the number of spaces.
这也是通过计算空格数来获得excel中字数的一种巧妙方法。
#2
1
There may not be much point in counting commas because if you know there will always be two at most you might jump to:
计算逗号可能没什么意义，因为如果你知道最多会有两个逗号，你可能会跳到：
=IFERROR(REPLACE(A1,FIND(",",A1,FIND(" ",A1)+1),99,""),A1)
#1
8
You can get the number of characters in the cell and then compare that to the number of characters in the cell if you substituted out all the commas with empty spaces:
您可以获取单元格中的字符数，然后将其与单元格中的字符数进行比较，如果您用空格代替所有逗号：
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))
This is also a neat way to get word counts in excel by counting the number of spaces.
这也是通过计算空格数来获得excel中字数的一种巧妙方法。
#2
1
There may not be much point in counting commas because if you know there will always be two at most you might jump to:
计算逗号可能没什么意义，因为如果你知道最多会有两个逗号，你可能会跳到：
=IFERROR(REPLACE(A1,FIND(",",A1,FIND(" ",A1)+1),99,""),A1)