2

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

For example:

enter image description here

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?

Thanks for the help!

Alex P
  • 12,249
  • 5
  • 51
  • 70
makaroN
  • 305
  • 1
  • 4
  • 16
  • Have you considered `Regex`? See [this](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) for a great starting point... – Alex P Aug 14 '16 at 11:36

4 Answers4

7

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

Consider the text in A1:

First approach:

Multiple nested 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:

=SUMPRODUCT(--(MID(A1,ROW($1:$1000),1)={"!","%","_","*","?","+","-",","}))
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • like the sumproduct option with the static array list – Forward Ed Aug 14 '16 at 12:10
  • Good non-VBA solution. See [this](https://www.owasp.org/index.php/Password_special_characters) for permissible special characters (not sure if OP only wants to count characters he specifies...) – Alex P Aug 14 '16 at 12:12
  • Hi @AxelRichter, did you test your second formula? It's not working for me. I'd suggest `=SUMPRODUCT({1}*NOT(ISNA(MATCH(MID(A1,ROW($1:$1000),1),{"!","%","_","*","?","+","-",","},0))))`. – Ulli Schmid Aug 14 '16 at 12:59
  • @Ulli Schmid: Yes, I have tested it and it works. With German Excel it is: `=SUMMENPRODUKT(--(TEIL(A6;ZEILE($1:$1000);1)={"!"."%"."_"."*"."?"."+"."-".","}))`. Note, the array literal must be a row vector. – Axel Richter Aug 14 '16 at 13:05
  • Ah yes, that makes sense. Thanks ;). {"!"\"%"\"_"\"*"\"?"\"+"\"-"\","} worked for me. – Ulli Schmid Aug 14 '16 at 13:50
5

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:

=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.

enter image description here

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.

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
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
0

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:

  1. In the VBA editor you need to set References > Microsoft VBScript Regular Expressions 5.5
  2. You call the function on your spreadsheet e.g. =CountSpecialCharacters(A2)
Alex P
  • 12,249
  • 5
  • 51
  • 70
0

=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;",";""))