Forum Discussion

MikeMike's avatar
MikeMike
Qrew Cadet
6 years ago

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

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"


3 Replies


  • 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")
    • MikeMike's avatar
      MikeMike
      Qrew Cadet
      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
      • ShawnShawn's avatar
        ShawnShawn
        Qrew Member

        I had something similar in where I am editing a field by adding a text value containing an ampersand through a URL api link. To get the ampersand to work as text in the formula I substituted the & symbol with %26 and it worked flawlessly.

        Using &amp did not work in my case; everything behind & or &amp in the text string, would get truncated.



        ------------------------------
        Shawn Somerlot
        ------------------------------