Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing a word sequence as part of a text in a spreadsheet cell
Hi all
I wonder if you can help me with a query I have. I want to find the number of cells in a row (for example row A) which contain the word London. The problem is that in some cell the word is contained as part of other text so if for example the cell A1 contains the text 50 Kingsway, London, WC1 the function =if(A1="London",1,0) returns the value of 0 failing to capture the word London which is contained in the cell. Can you propose a solution? Can I do that with VB? The problem is that I have a spreadsheet with 80,000 cells in row A so I need to have 80,000 cells in row B that will check the values of row A (eg B2 will perform the check in A2). This would make my desktop really slow to perform the checks. Can you help with that issue as well? Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing a word sequence as part of a text in a spreadsheet cell
Try this:-
=SUMPRODUCT(--(NOT(ISERROR(SEARCH("London",A1:A1000))))) Mike "T.Mad" wrote: Hi all I wonder if you can help me with a query I have. I want to find the number of cells in a row (for example row A) which contain the word London. The problem is that in some cell the word is contained as part of other text so if for example the cell A1 contains the text 50 Kingsway, London, WC1 the function =if(A1="London",1,0) returns the value of 0 failing to capture the word London which is contained in the cell. Can you propose a solution? Can I do that with VB? The problem is that I have a spreadsheet with 80,000 cells in row A so I need to have 80,000 cells in row B that will check the values of row A (eg B2 will perform the check in A2). This would make my desktop really slow to perform the checks. Can you help with that issue as well? Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing a word sequence as part of a text in a spreadsheet
Thanks Mike it worked.
"Mike H" wrote: Try this:- =SUMPRODUCT(--(NOT(ISERROR(SEARCH("London",A1:A1000))))) Mike "T.Mad" wrote: Hi all I wonder if you can help me with a query I have. I want to find the number of cells in a row (for example row A) which contain the word London. The problem is that in some cell the word is contained as part of other text so if for example the cell A1 contains the text 50 Kingsway, London, WC1 the function =if(A1="London",1,0) returns the value of 0 failing to capture the word London which is contained in the cell. Can you propose a solution? Can I do that with VB? The problem is that I have a spreadsheet with 80,000 cells in row A so I need to have 80,000 cells in row B that will check the values of row A (eg B2 will perform the check in A2). This would make my desktop really slow to perform the checks. Can you help with that issue as well? Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing a word sequence as part of a text in a spreadsheet cell
On Tue, 3 Jul 2007 02:08:01 -0700, T.Mad
wrote: Hi all I wonder if you can help me with a query I have. I want to find the number of cells in a row (for example row A) which contain the word London. The problem is that in some cell the word is contained as part of other text so if for example the cell A1 contains the text 50 Kingsway, London, WC1 the function =if(A1="London",1,0) returns the value of 0 failing to capture the word London which is contained in the cell. Can you propose a solution? Can I do that with VB? The problem is that I have a spreadsheet with 80,000 cells in row A so I need to have 80,000 cells in row B that will check the values of row A (eg B2 will perform the check in A2). This would make my desktop really slow to perform the checks. Can you help with that issue as well? Thank you in advance. One way is to use wild cards in the COUNTIF function: =COUNTIF(rng_to_check,"*London*") --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing a word sequence as part of a text in a spreadsheet
And the last question: Adopting Rons proposal I want check three cells in the same row to make sure that I didnt count the word more than once. Thus, I will use the function =IF(COUNTIF(I1:L1,"*London*")1,1,COUNTIF(I1:L1,"* London*")). And as a last step I want to add all the results without using 80,000 functions thus something like: =SUM(IF(COUNTIF(I1:L1,"*London*")1,1,COUNTIF(I1:L 1,"*London*")):IF(COUNTIF(I80000:L80000,"*London*" )1,1,COUNTIF(I80000:L80000,"*London*"))). How can I do that? "Ron Rosenfeld" wrote: On Tue, 3 Jul 2007 02:08:01 -0700, T.Mad wrote: Hi all I wonder if you can help me with a query I have. I want to find the number of cells in a row (for example row A) which contain the word London. The problem is that in some cell the word is contained as part of other text so if for example the cell A1 contains the text 50 Kingsway, London, WC1 the function =if(A1="London",1,0) returns the value of 0 failing to capture the word London which is contained in the cell. Can you propose a solution? Can I do that with VB? The problem is that I have a spreadsheet with 80,000 cells in row A so I need to have 80,000 cells in row B that will check the values of row A (eg B2 will perform the check in A2). This would make my desktop really slow to perform the checks. Can you help with that issue as well? Thank you in advance. One way is to use wild cards in the COUNTIF function: =COUNTIF(rng_to_check,"*London*") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
2 part macro question (sequence & order) | Excel Worksheet Functions | |||
Find part of a word in cell | Excel Discussion (Misc queries) | |||
Indicate missing number in a sequence (Part II) | Excel Discussion (Misc queries) | |||
SUMPRODUCT CAPTURING DATA FROM ANOTHER SPREADSHEET | Excel Worksheet Functions |