Search function in Excel
 

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

Search function in Excel
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Search function in Excel
 

On Sat, 7 Jan 2012 06:25:12 -0800 (PST), siddique wrote:

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)))

Search function in Excel
 

"siddique" wrote:
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 bewa 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.

Search function in Excel
 

On Sat, 7 Jan 2012 10:24:49 -0800, "joeu2004" wrote:

"siddique" wrote:
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 bewa 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.

Search function in Excel
 

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote:
"siddique" wrote:
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.

Search function in Excel
 

On Sat, 7 Jan 2012 23:13:49 -0800, "joeu2004" wrote:

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote:
"siddique" wrote:
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.