Filtering out multiple "string" from [field]
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Is 2006 just connected to the wrong filter output?
https://www.linkedin.com/in/adriley/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your help, yes I got it working :)
Evie
