计算单元格中的特定字符 - Excel

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

我想计算一个单元格中的所有特殊字符(!%_ *?+ - ,)。

For example:

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?

使用这个公式= LEN(D2)-LEN(SUBSTITUTE(D2;“!”;“”))我只计算一个字符,但我需要计算单个单元格中的多个字符...有没有办法如何调整这个?

Thanks for the help!

谢谢您的帮助!

4 个解决方案

#1

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

仅使用公式而不是VBA,可以使用以下两种方法:

Consider the text in A1:

考虑A1中的文字:

First approach:

Multiple nested SUBSTITUTE:

多个嵌套的SUBSTITUTE:

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

Second approach:

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:

使用SUMPRDUKT获取数组上下文中的MID函数,以获得A1中每个单个特殊字符的字符串的每个字符串:

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

#2

You can do this with a simple array formula:

您可以使用简单的数组公式执行此操作:

=SUM(LEN(A2) - LEN(SUBSTITUTE(A2,Special_Characters,"")))

Special_Characters is a range listing all your special characters. You could manually enter them as an array constant if you prefer:

Special_Characters是列出所有特殊字符的范围。如果您愿意,可以手动将它们作为数组常量输入:

=SUM(LEN(A2) - LEN(SUBSTITUTE(A2,{"%";"_";"*";"?";"+";"-";",";"!"},"")))

but the named range seems simpler.

但命名范围似乎更简单。

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl + shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

要输入公式,在将公式输入单元格或公式栏后,按住ctrl + shift键同时输入。如果您正确执行此操作,Excel将在公式周围放置大括号{...}。

If you prefer a VBA solution, I would suggest the code below. You will need to modify .Pattern to include any other characters you do NOT want to count. In the code below, any character that is not an upper or lower case letter, or a digit, will be counted as a special character.

如果你更喜欢VBA解决方案,我会建议下面的代码。您需要修改.Pattern以包含您不想计算的任何其他字符。在下面的代码中,任何不是大写或小写字母或数字的字符都将被计为特殊字符。

Option Explicit
Function SpecialChars(S As String) As Long
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "[^A-Za-z0-9]"
    SpecialChars = Len(S) - Len(.Replace(S, ""))
End With
End Function

#3

Here is a simple version I created:

这是我创建的一个简单版本:

Function CountSpecialCharacters(rng As Range) As String
    Dim regEx As New RegExp, matches As MatchCollection

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "[^a-zA-Z0-9]" '~~~> this counts any character not a to z or a number
    End With

    Set matches = regEx.Execute(rng)

    CountSpecialCharacters = matches.Count
End Function

Two points:

In the VBA editor you need to set References > Microsoft VBScript Regular Expressions 5.5

在VBA编辑器中,您需要设置引用> Microsoft VBScript正则表达式5.5

You call the function on your spreadsheet e.g. =CountSpecialCharacters(A2)

您可以在电子表格中调用该功能,例如= CountSpecialCharacters(A2)

#4

=8*LEN(D2)-LEN(SUBSTITUTE(D2;"!";""))-LEN(SUBSTITUTE(D2;"%";""))-LEN(SUBSTITUTE(D2;"_";""))-LEN(SUBSTITUTE(D2;"*";"")) -LEN(SUBSTITUTE(D2;"?";""))-LEN(SUBSTITUTE(D2;"+";""))-LEN(SUBSTITUTE(D2;"-";""))-LEN(SUBSTITUTE(D2;",";""))

#1