How do you count the number of special characters in one cell in Microsoft Excel?

Question

For example. In Cell A1 is written 'this is a test'. I want to know how many times the 's' is in Cell A1. Where is the Excel Guru ;)

Answer 1

I like XLman's idea but you can simplify it further as:

=LEN(A1) - LEN( SUBSTITUTE( LOWER(A1), "s", ""))

(assuming your text is in Cell A1 as per your example)

Also if you were to type in your character 'S' or 's' in Cell B1 so that you can actually change it, do as follows:

=LEN(A1) - LEN( SUBSTITUTE( LOWER(A1), LOWER(B1),""))

Answer 2

My favorite solution for this is to create a UDF (User Defined Function)

--Open your spreadsheet
--Alt+F11 (to open the Visual Basic Editor)
--If the Project Explorer isn't showing in the upper left, press Ctrl+R
--Right-click on your spreadsheet in the list and Insert | Module
--Click in the right hand pane and paste the following macro

Function COUNTCHARS(Txt As String, Char As String) As Integer
COUNTCHARS = Len(Txt) - Len(Replace(Txt, Char, ""))
End Function

--Then return to your worksheet and use the following formula:
=COUNTCHARS(LOWER(A1),"s")

***Since "S" and "s" are different characters, the LOWER function is used to make Excel see all the letters in the cell the same***

Answer 3

nope. doesn't work for me. i'm trying to cound the hyphens ( - ) in the cell. returns 0.

Answer 4

O Caçador is right and got a good point.
BUT
Why you do it in VBA when you can do it in Excel formula?

Assume you have the text you want to search for "S", "s" in B3
then, Paste this in C3
=LEN( B3)-LEN( SUBSTITUTE( B3,"s","")) + LEN( B3)-LEN( SUBSTITUTE( B3,"S",""))
This became complicated because that "SUBTITUTE" function is case sensetive

Good luck

Read my profile, I am the XLMan