Counting the number of times a text string occurs in a cell

**Geoff1184** · 10-11-2012, 12:14 PM

This may sound dumb but i'm having trouble getting this to work. I want to return the number of times a text appears in a single cell. So, i have a paragraph of text, within which "text" appears 5 times. What's the best formula to get that value?

Example paragraph:

"This article explains how you can use worksheet functions in Microsoft Excel for Mac to count the number of occurrences of a specific number or text string in a range of cells on a worksheet. The "More Information" section of this article contains sample formulas that you can use to do this."

Key text: "this"

Output value: 3

I've thought about using a string of "Find" formulas and starting the nested finds +1 of the previous start number but that seems like a hassle. Thoughts?

**Richard Buttrey** · 10-11-2012, 12:25 PM

Hi,

One way

Formula:

Please Login or Register  to view this content.

**Geoff1184** · 10-11-2012, 01:07 PM

Thanks Richard.

**ChemistB** · 10-11-2012, 01:23 PM

Richards' formula counts the difference in letters and only replaces the capital "This".

=(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"This",""),"this","")))/LEN("this")

**daddylonglegs** · 10-11-2012, 01:57 PM

You could take care of the case issue by using LOWER function like this

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

That will count this, This or THIS or any case combination (but note it would also count thistle or similar.....)

**Richard Buttrey** · 10-12-2012, 03:57 PM

Originally Posted by ChemistB

Richards' formula counts the difference in letters and only replaces the capital "This".

In my version of Excel it calculates the required value 3 which seems to imply it's case insensitive.

**daddylonglegs** · 10-12-2012, 04:07 PM

Originally Posted by Richard Buttrey

In my version of Excel it calculates the required value 3 which seems to imply it's case insensitive.

Richard, SUBSTITUTE is definitely case-sensitive (in any version), so in your formula

=LEN(A1)-LEN(SUBSTITUTE(A1,"This","|"))

SUBSTITUTE finds the only instance of "This" (right at the start) and replaces it with a single character "|", so that makes the resulting string 3 characters shorter, so in this case, co-incidentally, you are getting the right result, but in fact you are counting 3 for every instance of exactly "This".....so if A1 contains just

"This Life"

You'd still get 3....and if you used "this" in place of "This" in your original formula (on the original text) you get 6!