hie,
I want to count the number of occurences of a text string in an Excel column. Does anybody has any method or formula to do the same ?
Please suggest.... thank you
Printable View
hie,
I want to count the number of occurences of a text string in an Excel column. Does anybody has any method or formula to do the same ?
Please suggest.... thank you
The formula to count the number of occurrences of a text string in a cell range is given by -
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")
where,
range - specifies the selected cell range.
"text" - specifies the particular text string that you want to count.
If you want to count the occurence of the text string say for example, "word" in the cell range of "A1 to A20", then try this ::
=COUNTIF(A1:A20;"word")
Start Excel
Open a new workbook.
Type the following on sheet1:
A1: Fruit
A2: apple
A3: orange
A4: grape
A5: apple,apple
A6: orange,orange
A7: grape,grape
A8: apple,orange
A9: orange,grape
A10: grape, apple
A11: =SUM(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,"orange","")))/LEN("orange")
The value of cell A11 will be 5 because the text "orange" appears five times in the cell range A2 to A10...