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

Topic Labels: Formulas
2019 2
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator


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


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?


2 Replies 2

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.

6 - Interface Innovator
6 - Interface Innovator

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.