Find a text in a sentence inside a cell Solved/Closed

Question

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....
Please help
Thanks a lot.....

rizvisa1 · Accepted Answer

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.


Taking your example

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 · Answer

Hi rizvisa,

Thanks for this share. but have a question here as what "ISERROR" will do in this formulae?
Rgds...seekermay

seekermay · Answer

Thanks are always been helpful.
If need to extract the name searched in a string of text e-g "Bangalore" and some other names in each cell.

rgsds...seekermay

Find a text in a sentence inside a cell

3 responses

Similar discussions

Subscribe To Our Newsletter!