Find a text in a sentence inside a cell

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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.....

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
Hi rizvisa,

Thanks for this share. but have a question here as what "ISERROR" will do in this formulae?
Rgds...seekermay
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well it all depends on what data look like and what needs to be extracted. You may have to use combination of left, right, mid, search and find