Count the occurrences of text, characters, or words in Excel

Hi All,

I need a formula to find the occourance of a text in a cell.

for Example: In cell there is a text "Rarrurrlas" i want to find the number occourance of "rr" in the given text.

and ans should be : 2

I am using below formula: =LEN("Rarrurrlas")-LEN(SUBSTITUTE("Rarrurrlas","rr",""))

but it is giving me ans : 4

Thanks
Rahul Kumar Singh

Hi,

Try

=(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),UPPER(C1),"")))/LEN(C1)

where C1 houses rr

Thanks for Quick Response,

However I don't want to put my search string in another cell.
Is there any other formula, which can give me the appropriate result?

Thanks in Advance.

Hi,

Replace the C1 with the string "rr"

littleiitin,

Try:

In A1:
Rarrurrlas

In B1:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2
The result in B1 would be:
2


The last number in the formula, the 2, is the actual length of the string rr that you are searching for.
=(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2



If cell A1 contained:
RarruXXlas

And cell B1 containd the same formula:
=(LEN(A1)-LEN(SUBSTITUTE(A1,"rr","")))/2

The result in B1 would be:
1



Have a great day,
Stan

Hi All,

SUBSTITUTE is case sensitive. So =SUBSTITUTE("RaRRurrlas","rr","") would become "RaRRulas" (without quotes)

The following would give you the count within a range

=SUMPRODUCT((LEN(A2:A10)-LEN(SUBSTITUTE(UPPER(A2:A10),UPPER(C2),"")))/LEN(C2))

where C2 houses the search string


@ Stan, Thanks for explaining the formula in detail.

Thanks to all for your valuable solutions.

Special thanks to Admin for identifying that if cell contains number only then formula given by us will not work.
Also thanks for proper solution.