Can I use SWITCH() to change a field if it contains a certain string?

[Gustavo_VD]

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!

[Justin_Barrett]

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.

[Gustavo_VD]

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 [, pattern, result]... [, default])

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

Thanks!