Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data segregation..
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data segregation..
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" wrote in message ... 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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data segregation..
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" wrote in message ... 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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data segregation..
Not sure what I was thinking there... use Bernie's formula for sure.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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" wrote in message ... 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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save 60% on Data Entry, Data Conversion, Data Processing Services byOffshore-Data-Entry | Excel Programming | |||
Save 60% on Data Entry, Data Conversion, Data Processing Services byOffshore-Data-Entry | Excel Programming | |||
Reliable Data Conversion,Data Formats and Data Entry Services by DataEntry India | Excel Programming | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
Excel Macro for taking data external data and populating it on a sheet and deleting unwanted data | Excel Programming |