Excel 2007 : couting how many ? marks in one cell

**artner0112** · 03-26-2009, 02:12 AM

Hi,

I would like to know if there is a way to count how many Question marks or specific word is in one cell.

For example in one cell:
A2:
-----------------------------------------------------
|　Is Mark home yet? 　　　　　　　　　　|
|　No, Mark is not here. 　　　　　　　　 |
|　Why Mark? 　　　　　　　　　　　　　　 |
-----------------------------------------------------

What I am looking for would be something like this:

=COUNTIF(A2:A2,"?") 　　　　　　->2
OR
=COUNTIF(A2:A2,"MARK") 　　->3

COUNTIF was just an example I choose, but does anyone know how I can make this happen?

Thank You

**arthurbr** · 03-26-2009, 03:21 AM

Hi,
the following formula finds the number of ? in cell A1
=(LEN(A1)-LEN(SUBSTITUTE(A1;"?";"")))/LEN("?")
Replace ; with , if needed

**DonkeyOte** · 03-26-2009, 03:25 AM

You can in theory use LEN

For specific chars

B1: =LEN($A1)-LEN(SUBSTITUTE($A1,$C$1,""))
where C1 holds ?

For specific words & exact matches it's safer to use something like

B1: =(LEN(" "&$A1)-LEN(SUBSTITUTE(" "&$A1," "&$C$1,"")))/LEN(" "&$C$1)
where B1 holds Mark

Even then however this is not watertight ... eg Marks would equal 1 for Mark... you can't use " Mark " as criteria given "Mark?" would be ignored in the count.

Hope that helps some...