Search function in Excel

S

siddique

Search function
I need a help with the search function in Excel one or more text
strings

Example

cell B3 contains an address 1560 main road Montreal Quebec Canada

I would like such result to be True if either Montreal or Quebec or
both found in the D3 cell

I tried the two search formula but it does not work, both formula
looks for both conditions

=AND(SEARCH("montreal",B3),SEARCH("quebec",B3))

=OR(AND(SEARCH("montreal",B3),SEARCH("quebec",B3)))

Any help would be appreciated
 
C

Claus Busch

Hi,

Am Sat, 7 Jan 2012 06:25:12 -0800 (PST) schrieb siddique:
cell B3 contains an address 1560 main road Montreal Quebec Canada

I would like such result to be True if either Montreal or Quebec or
both found in the D3 cell

try:
=COUNT(SEARCH({"montreal","quebec"},B3))>0


Regards
Claus Busch
 
R

Ron Rosenfeld

Search function
I need a help with the search function in Excel one or more text
strings

Example

cell B3 contains an address 1560 main road Montreal Quebec Canada

I would like such result to be True if either Montreal or Quebec or
both found in the D3 cell

I tried the two search formula but it does not work, both formula
looks for both conditions

=AND(SEARCH("montreal",B3),SEARCH("quebec",B3))

=OR(AND(SEARCH("montreal",B3),SEARCH("quebec",B3)))

Any help would be appreciated


=OR(ISNUMBER(SEARCH({"montreal","quebec"},A1)))
 
J

joeu2004

siddique said:
cell B3 contains an address 1560 main road Montreal Quebec Canada
I would like such result to be True if either Montreal or Quebec
or both found in the D3 cell

Ostensibly the following normal (non-array) formula (just press Enter):

=OR(ISNUMBER(SEARCH({"montreal","quebec"},B3)))

But beware: that will return TRUE for the address 1560 Montreal Road Ottawa
Canada, among others.

I wonder if you would need a more sophisticated search/comparison function.
 
C

CellShocked

Ostensibly the following normal (non-array) formula (just press Enter):

=OR(ISNUMBER(SEARCH({"montreal","quebec"},B3)))

But beware: that will return TRUE for the address 1560 Montreal Road Ottawa
Canada, among others.

I wonder if you would need a more sophisticated search/comparison function.


If "B3" is a "city" "field", then it should already be separated.
 
J

joeu2004

CellShocked said:
[....]
If "B3" is a "city" "field", then it should already be separated.

Maybe if you took the time to read the OP's explanation above, you would
know that it isn't.
 
C

CellShocked

CellShocked said:
cell B3 contains an address 1560 main road Montreal Quebec Canada
[....]
If "B3" is a "city" "field", then it should already be separated.

Maybe if you took the time to read the OP's explanation above, you would
know that it isn't.
Some things should be pre-parsed, and if they are not, we should not
let those who would have us run circles to get to a point of consistency,
get away with 'improper behavior' from a basic computer science
perspective, when what they should be doing is modifying their basic
paradigm.

There is too much "It's all good" going on in too many places these
days.
 

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

Top