Skip to main content

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

  • April 17, 2019
  • 2 replies
  • 55 views
G
Forum|alt.badge.img+10

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

Justin_Barrett
Forum|alt.badge.img+21

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.

G
Forum|alt.badge.img+10
  • Gustavo_VD
  • Author
  • Known Participant
  • April 18, 2019

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!

Terms & ConditionsAccessibility statement