计算单元格中的特定字符-Excel [英] Count specific characters in a cell - Excel

问题描述

我想计算一个单元格中的所有特殊字符(！％_ *?+-).

I would like to count all special characters (!%_*?+-,) in a cell.

例如:

使用此公式=LEN(D2)-LEN(SUBSTITUTE(D2;"!";""))，我只能计算一个字符，但是我需要在单个单元格中计算多个字符...有没有办法调整此字符?

With this formula =LEN(D2)-LEN(SUBSTITUTE(D2;"!";"")) i can only count one character, but i need to count multiple characters in single cell...is there a way how to tweak this?

感谢您的帮助！

推荐答案

仅使用公式，而不使用VBA，可以通过以下两种方法实现:

Using formulas only, not VBA, this is possible with the following two approaches:

考虑A1中的文本:

第一种方法:

多个嵌套SUBSTITUTE:

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"-",""),"+",""),"?",""),"*",""),"_",""),"%",""),"!",""))

第二种方法:

使用SUMPRDUKT在数组上下文中获取MID函数，以将A1中字符串的每个单个字符与每个单个特殊字符进行比较:

Using SUMPRDUKT for getting the MID function in array context to get each single charachter of the string in A1 compared with each single special character:

=SUMPRODUCT(--(MID(A1,ROW($1:$1000),1)={"!","%","_","*","?","+","-",","}))

这篇关于计算单元格中的特定字符-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！