Hi,
I've a Spread sheet where a word comes in different part of a sentence. Is
there any way to list the find how many times the word is repeated.

Regards,
Praveen..

Praveen,

Array enter (enter using Ctrl-Shift-Enter) a formula like

=SUM(LEN(A2:A100)-LEN(SUBSTITUTE(LOWER(A2:A100),LOWER(D2),"")))/LEN(D2)

Where D2 contains the word you are looking for, and A2:A100 have the sentences.

Note that this will also look at word parts and will ignore case: if you are looking for the word
"Sheet", it will count Sheet, sheet, spreadsheet, sheets, etc.


HTH,
Bernie
MS Excel MVP


"praveen" <(E-Mail Removed)> wrote in message
news:EBA96635-A7F8-4D0D-AB90-(E-Mail Removed)...
> Hi,
> I've a Spread sheet where a word comes in different part of a sentence. Is
> there any way to list the find how many times the word is repeated.
>
> Regards,
> Praveen..

Assuming the sentence is in A1 and the word you want to count is in B1...

=SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),LEN($B$1)),$B$1)))

Note that the search is not case sensitive; if you need it to be, then
change SEARCH to FIND. Also note that the above will count occurrences of
your word if it appears inside another word. For example, if you wanted to
count the number of times "the" appeared in the text in A1, the above
formula would count its occurrence inside the word "mother".

--
Rick (MVP - Excel)


"praveen" <(E-Mail Removed)> wrote in message
news:EBA96635-A7F8-4D0D-AB90-(E-Mail Removed)...
> Hi,
> I've a Spread sheet where a word comes in different part of a sentence.
> Is
> there any way to list the find how many times the word is repeated.
>
> Regards,
> Praveen..

Not sure what I was thinking there... use Bernie's formula for sure.

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Assuming the sentence is in A1 and the word you want to count is in B1...
>
> =SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("A$1:A"&LEN(A1))),LEN($B$1)),$B$1)))
>
> Note that the search is not case sensitive; if you need it to be, then
> change SEARCH to FIND. Also note that the above will count occurrences of
> your word if it appears inside another word. For example, if you wanted to
> count the number of times "the" appeared in the text in A1, the above
> formula would count its occurrence inside the word "mother".
>
> --
> Rick (MVP - Excel)
>
>
> "praveen" <(E-Mail Removed)> wrote in message
> news:EBA96635-A7F8-4D0D-AB90-(E-Mail Removed)...
>> Hi,
>> I've a Spread sheet where a word comes in different part of a sentence.
>> Is
>> there any way to list the find how many times the word is repeated.
>>
>> Regards,
>> Praveen..
>