Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Aug 03, 2022
Solved

How To Filter Data Based On Substring In Excel?

Hi,

I want to filter with criteria in column B that include substrings "pickup" and "failed" in main string

I write a formula:

=INDEX(FILTER(A2:B7,(B2:B7="*pickup")+(B2:B7="*failed"),""),0,{1,2})

But it hasn't shown the expect result

Hope for your help

Thank you

https://docs.google.com/spreadsheets/d/1BfTl9InAPqScRFvz70Z8XTRYPdPhDOMS/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Aug 03, 2022

    Hi littlevillage 

     

    According to your requirements 842160461155 shouldn't be reported, it doesn't contain "pickup" and "failed")

     

    =FILTER(A2:B7, ISNUMBER(SEARCH("pickup",B2:B7)) * ISNUMBER(SEARCH("failed",B2:B7)))

3 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Aug 03, 2022

    Hi littlevillage 

     

    According to your requirements 842160461155 shouldn't be reported, it doesn't contain "pickup" and "failed")

     

    =FILTER(A2:B7, ISNUMBER(SEARCH("pickup",B2:B7)) * ISNUMBER(SEARCH("failed",B2:B7)))

Resources