# Find a text in a sentence inside a cell

Solved/Closed
Sunil Magesh - Feb 18, 2010 at 01:57 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 24, 2010 at 04:47 AM
Hello,
I have a column which has addresses of different Companies,
eg.
SHRUTIVEER,23/2/1/1 BHAGBAN CHATTERJEE LANE,HOWRAH 711101

2nd Floor, East Wing, Khanija Bhavan, 49, Race Course Road, Bangalore - 560001

No 64 Khorramshahr Ave, Tehran 15539, IRAN

C-2/91, G.I.D.C., Shankar Tekri, Udyog Nagar, Jamnagar 361 004

2 Lansing Square, Suite 503, Toronto, ON, Canada, M2J4P8

Now I need to only filter the addresses which have Bangalore or Jamnagar or Canada in them ...
how can i do that....
Thanks a lot.....

## 3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 18, 2010 at 09:53 AM
As you must have known, for two criteria you can have regular filter. For more than two, you need to get a little creative

For more than two items to be search, you can use this very flexible method

On a new sheet, on each row type in what you want to search for. And then use a combination of search and look up.

Assumptions
1. The address are in Col A, starting from row 2
2. Column B is available to be used for formula for filter
3. The address will not exceed 9998 characters

1. Add on Sheet2 on cell A1 Bangalore and on cell A2 Canada and on cell A3 Jamnagar

2. On cell B2 of main sheet enter this formula
=IF(ISERROR(LOOKUP(9999, SEARCH(Sheet2!A\$1:A\$3,A2), Sheet2!A\$1:A\$3)),0,1)

3. Drag the formula down to all the rows

4. Apply filter and filter for 1 in this new column

Note:
if you have more than 3 items, you need to change A\$3 to what ever new number of rows are.

Also keep in mind if the address is Bangalore House, Lucknow. It still will be a hit as "Bangalore" is found in the address.
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 5
Feb 24, 2010 at 03:22 AM
Hi rizvisa,

Thanks for this share. but have a question here as what "ISERROR" will do in this formulae?
Rgds...seekermay
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 24, 2010 at 04:06 AM
If the item that you are looking for is not found, LOOKUP would have returned error. IsError is saying, evaluate the function to see if there is an error or not
seekermay Posts 28 Registration date Wednesday January 27, 2010 Status Member Last seen May 27, 2013 5
Feb 24, 2010 at 04:38 AM