I run into the situation where I concatenate a bunch of User- or Shape Data cells, then display them in a shape's text.
If I am building a text block with multiple lines, I concatenate with CHAR(10) in between. But sometimes values are "", and I don't want blank lines.
It would be nice to have some sort of JOIN function that would take a variable number of arguments, and a list-separator. So I could do something like this:
JOIN( separator, val1, val2, optional val3, optional val4,... )
For an example:
User.txt = JOIN( ";", User.val1, User.val2, User.val3 )
The "SuperJoin" function would allow a 'normal' separator and an 'if-blank' separator. The situation I run into is that I want to build a text block that has many lines from many cells, but if a cell has some sort of null value, then we shouldn't add a blank line. You have to do a LOT of nested if statements to get this right, including checking 'previous' and 'next' values. It is arduous.
The function would look something like this, then:
JOIN( normal_separator, null_separator, null_condition, val1, val2, optional val3, optional val4,... )
For example:
User.txt = JOIN( CHAR(10), "", "", User.val1, User.val2, User.val3 )
I can see that this might be a bit harder to do because of the test-for-null bit.
Maybe a normal JOIN function could have an option to eliminate back-to-back separators. You can almost fake this with SUBSTITUTE, but that will only work for two adjacent separators. If you have three, then you have to re-substitute...