How do you count how many times a certain character appears in a cell?

[Skywalker]

How does one count how often a certain character or string appears within another string? For instance counting the number of spaces in a cell, or counting how often the word "the" appears.

Is there a direct function to do this or does one need to play around with combining different functions?

[Trebor76]

Hi Skywalker,

To count the number of "the" in cell J20 (change cell reference to suit) use this...

=SUM(LEN(J20)-LEN(SUBSTITUTE(J20,"the","")))/LEN("the")

...and simiarily to count the number of spaces in cell J20 (again change the cell reference to suit), use this:

=SUM(LEN(J20)-LEN(SUBSTITUTE(J20," ","")))/LEN(" ")

You can change the formula for any character you wish.

HTH

Robert

[Fotis1991]

Hi

With your sting in A1 and the word or the special character in A2, try this.

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),LOWER(A2),"")))/LEN(A2)

Is this, works for you?

[vlady]

Hello
or a udf found here.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=235

=countchar(A1," ")

[Skywalker]

Thanks guys!