Do I need to escape ampersand in a text formula field?

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
I have an existing formula field which produces a number of text values based on different criteria.  I am now creating a new formula field that looks at the first field to produce a reduced set of values.

The problem I have encountered is that two of the values in the first formula field contain an ampersand - & - and my new formula field does not seem to see those values, and so is ignoring them when producing the output values.

eg.

This
That
This & That

My formula

If([ex field] = "This", "One", [ex field] = "That", "Two", [ex field] = "This & That", "Three")
However my output gives me a "One" and a "Two" but for the values that should be "Three" it just shows as "<empty>"

Do I need to somehow escape the ampersand to make it find them in my other field?  I have tried different ways I could think of:

If([ex field] = "This \& That", "Three")
If([ex field] = "This && That", "Three")
If([ex field] = "This &amp; That", "Three")
Is there something else I need to use to make my formula work?  Unfortunately it is too late to go back to the other field to replace the & with an "and"


Photo of Mike

Mike

  • 474 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of Everett Patterson

Everett Patterson

  • 1,026 Points 1k badge 2x thumb

Try something like this

If(Trim(Left([ex field], "&")) = "This" and Trim(Right([ex field], "&")) = "That", "Three", [ex field] = "This", "One", [ex field] = "That", "Two")
Photo of Mike

Mike

  • 474 Points 250 badge 2x thumb
Well that was an unexpected workaround solution, but it worked, thank you!  I am still interested to know if there's a way to escape that character, but until then this has solve my immediate problem.

Thank you