Capturing a word sequence as part of a text in a spreadsheet cell

G

Guest

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

Ron Rosenfeld

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.
Click to expand...



One way is to use wild cards in the COUNTIF function:

=COUNTIF(rng_to_check,"*London*")


--ron
 
G

Guest

And the last question:
Adopting Ron’s proposal I want check three cells in the same row to make
sure that I didn’t 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:L1,"*London*")):IF(COUNTIF(I80000:L80000,"*London*")>1,1,COUNTIF(I80000:L80000,"*London*"))).
How can I do that?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Typed text appears in wrong cell 0
Count values in column only if value of another column is true 4
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
How do I use a percentage formula? 2
Search for specific text within last non blank cell in a range 2
How do I put a variable column number in the sum() function? 7
How to use number in some cell as a part of other cells reference? 4
Conditionally formatting just part of a cell contents - how? 1

Top