Data segregation..

[praveen]

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..

 

[Bernie Deitrick]

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

 

[Rick Rothstein]

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 Rothstein]

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