Filtering out multiple "string" from [field]

[Evie]

Hi 

I'm trying to filter out multiple string values from a field and tried this but it's not working:-

Contains("Total",[Country]) or
Contains("Total EU3",[Country]) or
Contains("Total(EU + EFTA)",[Country]) or
Contains("Total(EU15 + EFTA)",[Country])

I would need to strip out all the text and not sure if there is a AND OR function?

....also note that if I try this below then I get "malformed Function Call" :-

Contains(,[Country],"Total") or
Contains([Country],"Total EU3") or
Contains([Country],"Total(EU + EFTA)") or
Contains([Country],"Total(EU15 + EFTA)")

Please help?

Many thanks 

Evie

[MarqueeCrew]

@Evie

Contains([Country],"Total")

That should find any of the matches that are in your request.

Cheers,

Mark

[jdunkerley79]

You have an extra comma in the second example:

Contains([Country],"Total") or
Contains([Country],"Total EU3") or
Contains([Country],"Total(EU + EFTA)") or
Contains([Country],"Total(EU15 + EFTA)")
 

If you are removing all Totals then I would use StartsWith:

StartsWith([Country], 'Total')

[Evie]

Thank Mark,

I did try that initially within the "Basic Filter", but for some years it would not populate, hence my thinking was that I needed a muti-string formula.  I've now amended the file with your command within the "function" section, and its working for all years except 2006 - even after I have created a new excel output file.  Hence, my thinking is that its not a function issue, but perhaps a bug in the way it processes the data. 

I've attached the files, can you please take a look.  I'm really curious to why its happening?

( just a note I could not do a dynamic batch sheet import due to the data having different headers and needing to be transposed for each year)

Many thanks 

Evie

[AdamR_AYX]

Is 2006 just connected to the wrong filter output?

[jdunkerley79 · Accepted solution]

Think Adam is right

Attached a trick to show how could simplify using sheet names and filters to make it one path through.

[Evie]

This is really helpful, thank you very much :))  I like the first two filters in selecting which specific sheets need cleansing.  

But...

I'm replicating this to make sure I fully understand, and I'm stuck on the the 3rd filter ( after the union ) I can't figure out where you got "[F1]", I assume it's field 1.  And if I put this in I get "Unkown variable F1".  It probably comes from the Union, but I can't see anything here:-

Thanks again :)

[jdunkerley79]

I specified that the first row of the spreadsheet contains data in the dynamic inputs then the columns come through as F1 to F14/15/16

The first filter picking country is to select the headers of each sheet and join back to the data.

Hope that helps make sense.

Get Outlook for iOS

[Evie · Accepted solution]

Thank you for your help, yes I got it working :)

Evie