Skip to main content

Hi,


I’m trying to use SWITCH as a way to clean and standardise some data. Here’s what I’m trying:


SWITCH({Company},FIND("Amazon",{Company}),"AMZN",{Company})


So basically if the text entered into {Company} contains the word “Amazon”, the field should switch to “AMZN”.


Unfortunately this formula is not working. Any thoughts besides using a nested IF?


Thanks!

I’m afraid SWITCH won’t work that way. Based on your example, SWITCH won’t actually look for “Amazon” in the {Company} field. It’ll look for the number returned by the FIND function.


Off the top of my head (which is admittedly a little afternoon-fuzzy at the moment), I can’t think of a way to do what you want other than a joyous nested IF.

Hi Justin,


Yes, of course. You’re completely right! In this case the SWITCH will look for the number 0 or 1.


I’ve tried some other options, but the SWITCH function doesn’t take formulas on the pattern parameter. Otherwise I think I could write a formula that would return the search term instead of a number.


For ref:

SWITCH(expression s, pattern, result]... t, default])


I guess I’ll have to tap onto the API.


Thanks!

Reply


Terms & Conditions