Forum Discussion
Resolver IIIHow to use the Switch function to look for strings
Hey All,
I am looking to use the SWITCH function to search thru' a string and then if the string is found, to return the required value. The code is as follows :
Active =
SWITCH('summary table'[Title],
"?calorie control",
"sucralose")But it is returning nothing.
If I use the SEARCH function within the SWITCH function, it is returning an error, stating the SWITCH function cannot work with a TRUE/FALSE value [sreen grab below]
error in switch function with search
Is there a way to solve this - I know this can be done with an IF & SEARCH combination, but the code is getting too cumbersome.
Any help appreciated.
Putting in an OR isn't a problem.
SWITCH ( TRUE (), CONTAINSSTRING ( [Title], "calorie control" ) || CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose", CONTAINSSTRING ( [Title], "natural" ) , "Stevia", CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame" )But this isn't really any different than without the OR.
SWITCH ( TRUE (), CONTAINSSTRING ( [Title], "calorie control" ), "Sucralose", CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose", CONTAINSSTRING ( [Title], "natural" ) , "Stevia", CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame" )Hi,
This calculated column formula works
=LOOKUPVALUE(search_terms[Result],search_terms[Search string],FIRSTNONBLANK(FILTER(VALUES(search_terms[Search string]),SEARCH(search_terms[Search string],Data[Title],1,0)),1))The search terms table looks like this
Search stringResult
Calorie control Sucralose Cook & bake Sucralose natural Stevia sugar control Aspertame Hope this helps.
The SWITCH function returns the first result that matches the first argument (in this case, TRUE). So, in this pattern, it returns the result following the first test that evaluates as being true.
More detail here:
https://p3adaptive.com/2015/03/the-diabolical-genius-of-switch-true/
10 Replies
- Ashish_Mathur
Super User
Hi,
Instead of sharing the formula, explain the question in simple language and show the expected result.
monojchakrabHey Ashish_Mathur ,
I am trying to search thru the column called Title and whereever it matches - something like OR("calorie control","cook & bake"), it replaces that with sucralose, e.g. and so on.
Some workaround with the SWITCH function is giving me a limited solution but I am not able to find a way to use multiple strings within the search function like using a || or , the OR function, like one would do in Excel.
The code I am using now, with limited result is as follows :
Active = SWITCH(TRUE(), SEARCH("calorie control",'summary table'[Title],,0)>1, "Sucralose", SEARCH("natural",'summary table'[Title],,0)>1, "Stevia", SEARCH("sugar control", 'summary table'[Title],,0)>1, "Aspertame", search("cook & bake",'summary table'[Title],,0)>1, "Sucralose", blank())Hope that explains what I am trying to solve for here.
Aprpeciate the time and the help.
- Ashish_Mathur
Hi,
Share some data to work with. Also, share an exhaustive 2 column dataset where the first column contains the search strings such as Calorie Control, natural and the second column which contains the result you would like to see such as Sucralose, Stevia.
Furthermore, what result do you expect to see in a spare column if you have Calorie Control and Natural in the same cell?
- monojchakrab
Title MPU Active Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 5 5 Aspertame Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 1 1 Stevia Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3 3 Aspertame Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 4 4 Aspertame Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 2 2 Aspertame Equal Sweetener, Sugar Free, Zero Calorie, Cook & Bake, 80g Powder Jar, Pack of 1 1 Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 2 2 Aspertame Equal Stevia Natural Sweetener, 100 Tablets - Pack of 2 2 Stevia Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6 6 Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 6 6 Aspertame Equal Stevia Natural Sweetener, Sugar Free, 50 Sachets - Pack of 2 2 Stevia Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 300 Tablets, Pack of 2 2 Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 3 3 Aspertame Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 2 2 Stevia Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 3 3 Aspertame Equal Stevia Natural Sweetener, 100 Tablets - Pack of 4 4 Stevia Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 2 2 Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Tablets + 10 Tablets Free Tablets, Pack of 3 3 Aspertame Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 6 6 Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 3 3 The column called [active] is the target column. The column to search thru' is the 1st column called [Title].
Does this work for you?
To answer the question you have asked at the end - that is precisely the problem I am trying to solve for. Which means there has to be a way to use a kind of OR function, where the search can happen across multiple strings at the same time.
E.g. If [Title] contains "Calorie control" OR "Cook & bake", Then return "Sucralose"
Hope I have been able to explain a little more clearly for you.
Appreciate your time and help