Find a text in a sentence inside a cell
Solved/Closed
Sunil Magesh
-
Feb 18, 2010 at 01:57 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 24, 2010 at 04:47 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 24, 2010 at 04:47 AM
Related:
- Find a text in a sentence inside a cell
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
- Which key is used to make multiple line in a single cell - Guide
- Count if cell contains number - Excel Forum
- Compare 2 text cells and show difference in the third cell ✓ - Excel Forum
- If cell contains date then return value ✓ - Office Software Forum
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 18, 2010 at 09:53 AM
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.
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.
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
Posts
28
Registration date
Wednesday January 27, 2010
Status
Member
Last seen
May 27, 2013
5
Feb 24, 2010 at 03:22 AM
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
Thanks for this share. but have a question here as what "ISERROR" will do in this formulae?
Rgds...seekermay
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 24, 2010 at 04:06 AM
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
Feb 24, 2010 at 04:38 AM
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
If need to extract the name searched in a string of text e-g "Bangalore" and some other names in each cell.
rgsds...seekermay
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 24, 2010 at 04:47 AM
Feb 24, 2010 at 04:47 AM
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
