是否有Excel函数来计算单元格中特定字符的出现次数？ [英] Is there an Excel function to count occurrences of a certain characters in a cell?

问题描述

客户有一个Excel文件，需要清理一些名称。特别是，我们试图隔离名称后缀（例如，Jr.，Sr.，III等）。在名称中始终格式化姓氏，名字，后缀单元格，我试图计算单元格中逗号的数量。如果单元格中有多个逗号，我可以将该单元格标识为具有名称后缀。然而，Excel中的所有 COUNT 函数都会计数CELLS的实例，而不是单元格内的字符。是否有一个函数可以计算单元格中特定字符的出现次数并返回该计数值？

解决方案

您可以获取字符数然后将其与单元格中的字符数进行比较，如果用空格替换所有逗号： = LEN（A1）-LEN（SUBSTITUTE（A1，，，））

< sub>这也是一个很好的方法，通过计算空格的数量来计算excel中的字数。

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.

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?

解决方案

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函数来计算单元格中特定字符的出现次数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！