JoinText(a, i, separator, finalSeparator, default, textForNull)

Returns the elements of array «a» as text concatenated over index «i».

If any elements are numeric, they are converted to strings using the number format settings for the current node.

Null items are ignored, unless «textForNull» is specified.

Optional parameters

Insert this text between each item.
Insert this text instead of «separator» between the penultimate and last item.
Return this as the result if «a» has length zero or contains only Null.
Use this text to replace any Null values in the result.


Suppose A is this array, indexed by J

J ▶
1 2 3 4 5
"one" "two" "three" "four" "five"


JoinText(A, J) → "onetwothreefourfive"
JoinText(A, J, ', ') → "one, two, three, four, five"
JoinText(A, J, ", ", " and ") → "one, two, three, four and five"
JoinText(Exp(J^2), J, ', ') → "2.718, 54.6, 8103, 8.886M, 72G"

Treatment of Null. Suppose B is this array indexed by J:

J ▶
1 2 3 4 5
"one" «null» "three" «null» "five"
JoinText(B, J, ", ") → "one, three, five"
JoinText(B, J, ", ", textForNull: "") → "one, , three, , five"
JoinText(B, J, ", ", textForNull: "NULL") → "one, NULL, three, NULL, five"
JoinText([Null, Null, Null])→ ""
JoinText([Null, Null, Null], default: null) → «null»
JoinText([]) → ""
JoinText([], default: "empty") → "empty"


The All-Null case

If all values in «a» are Null, or «a» has zero items, the result is the empty text, "". This is not consistent with the general Analytica convention that the result of an array function should be Null when there are no non-null items in the array. It does mean that the result is always text, which is often convenient.

We'd like to change this to be consistent, but are reluctant to do so because of backward compatibility.

Instead of relying on this, we recommend that you make use of the «default» parameter to specify the behavior explicitly. By doing so, you'll be immune to any change to this default behavior. If you want it returning Null then specify default: null. If you want it returning the empty text, then specify default:"". If array «a» does not contain «null» values and always has a length of at least 1, you don't have to worry about this.

If you want to concatenate all elements in a 2-D array, ignoring all «null» cells, then you'll want to use default: null, e.g:

JoinText(JoinText(A, I,",", default: Null), J, ",", default: Null)

If you don't use default: null in this case, you'll get an extra comma when an all-null row appears.

See Also


You are not allowed to post comments.