"Clean" join() without excess delimiters?

Question

I'd like a function in Google Sheets to concatenate elements with a chosen delimiter like join(), but without excess delimiters at beginning, end, and between null elements. I accomplished this with REGEXREPLACE() as below:

With Formula 3, delimiters are neither first, last, nor repeated. However, the formula is long and unwieldy.

Is there a better approach? On my small spreadsheet, speed is not an issue. I know I could write a custom function, but would like to avoid that if possible.

---

Use of filter() as suggested by NormalHuman solves the problem when the elements to be joined are in a contiguous range. Is there also a good solution without resorting to join(split(join(...))) when selecting individual cells as in:

=join(char(10), g3, f3, h3, i3)

Answer 1

I describe two ways to do this.

Using Filter command

You can join while ignoring empty cells with

=JOIN("~", FILTER(B3:E3, LEN(B3:E3)))

The command FILTER selects only the entries with nonzero value of LEN, i.e., nonempty one.

For non-contiguous input range, such as g3, f3, h3, i3, you can use {array} syntax:

=JOIN("~", FILTER({g3, f3, h3, i3}, LEN({g3, f3, h3, i3})))

Using Join-Split-Join

The previous method requires the range to be entered twice, which can be annoying in the second situation above. Here is another method:

=JOIN("~", SPLIT(JOIN(CHAR(60000), B3:E3), CHAR(60000)))

First, it joins using a private use Unicode codepoint (which does not correspond to any standard character and therefore should not be in the input). Then it splits by the same codepoint. The command Split omits blank cells in its output, which can be annoying in other contexts, but helps here. Then the final Join works as intended.

With this method, the range only has to be entered once.

Answer 2

There is a built-in function that will accomplish this:

=TEXTJOIN("~", true, E2:B2)

Documentation by Google is here.