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)

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.

  • Thank you. This is a great answer to the question as asked. However, I also have need to use this with a non-contiguous range. I have edited my question to reflect this additional desire. – Dave Bezaire Jul 30 '15 at 23:24
  • Great. Thank you again. Could you please say why NOT to use the join(split(join(....))) solution? It seems a bit more compact which might be helpful with a long list of non-contiguous elements. – Dave Bezaire Jul 31 '15 at 1:00
  • I edited my answer to rectify the issue in the previous comment. – user79865 Jul 31 '15 at 15:00
  • The additional method using the private Unicode codepoint is very helpful, because I can now safely use the Join(Split(Join(...))) technique. This avoids a #NA error when using the Filter() method and there are no cells that satisfy the filter. – Dave Bezaire Aug 10 '15 at 13:29

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

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

Documentation by Google is here.

  • 1
    The perfect solution, thanks – Jon L. Jul 26 '19 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.