When the “|” character is used as a target to substitute, either by itself or used with other characters, the SUBSTITUTE() function will apply to all spaces as well as the beginning and end of the string. The exact logic is strange.
Here are some test using this substitution
this is also a thing with [ and ] and others.
You can escape it with \
Please find my Post here:
Thanks for the solution Thorsten. This worked for my application.
Would still be curious to hear Tulip’s official diagnosis of this as now that I start to dig deeper, there’s quite a few posts about unexpected behaviors with substitute and trim. It would be nice if there was a knowledge base article about these limitations/findings/proper wrapping techniques for future developers.
