Search function in Excel

  • Thread starter Thread starter siddique
  • Start date Start date
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
 
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
Click to expand...

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


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

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


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

Maybe if you took the time to read the OP's explanation above, you would
know that it isn't.
 
CellShocked said:
cell B3 contains an address 1560 main road Montreal Quebec Canada
Click to expand...
[....]
If "B3" is a "city" "field", then it should already be separated.
Click to expand...

Maybe if you took the time to read the OP's explanation above, you would
know that it isn't.
Click to expand...
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.
 
Back
Top