Count Word in long string
 

Dears

I want to count how many specific word is repeted in long string in one cell i will give you example

Red , green , red , blue , ,black ,green , red, red,

what i want to do is to count how many times the word red repeted starting from the right hand side till the fourth coma only not through the whole sting only till the fourth coma

Count Word in long string
 

Hi,

Am Sat, 23 Mar 2013 10:55:18 +0000 schrieb tamer:

Red , green , red , blue , ,black ,green , red, red,

what i want to do is to count how many times the word red repeted
starting from the right hand side till the fourth coma only not through
the whole sting only till the fourth coma

your string in A1. Then try:
=(LEN(RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,",","#",4))-2))-LEN(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,",","#",4))-2),"red",)))/LEN("red")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Will it always be until the fourth comma?

If it is to the fourth comma, the following will perform the search

this searches the text in A1, counts the number of occurrences of the text in B1, up until the fourth comma

=(LEN(LEFT(A1,FIND(",",A1, FIND(",", A1,FIND(",", A1, FIND(",", A1, 1)+1) +1)+1)))-LEN(SUBSTITUTE(LOWER(LEFT(A1,FIND(",",A1, FIND(",", A1,FIND(",", A1, FIND(",", A1, 1)+1) +1)+1))),LOWER(B1),"")))/LEN(B1)