Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

Topic Labels: Formulas
1706 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

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.

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!