Discussions

Expand all | Collapse all

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

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

    Posted 02-15-2019 21:25
    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"




  • 2.  RE: Do I need to escape ampersand in a text formula field?

    Posted 02-15-2019 22:21

    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")


  • 3.  RE: Do I need to escape ampersand in a text formula field?

    Posted 02-15-2019 22:32
    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