Search code examples
excelgoogle-sheetsgoogle-sheets-formulaarray-formulastextjoin

How to "textjoin" with arrayformula (including ignoring empty cells)?

I love textjoin because of the "ignore empty cells" capability, it's really clean. However, it won't work with ArrayFormula to auto-populate a column for me. So it seems a messier workaround is needed. This is what I have come up with. Does anyone know of a simpler solution?

Formula in cell A2 should combine values in cells B, C, & D with a delimiter, and not have any extra delimiters like leading or trailing _s, or _ _ in the middle.

+----------------------+------------+------+-----------+
|       NiceName       | first_name | year |  country  |
+----------------------+------------+------+-----------+
| Kaylee_1999          | Kaylee     | 1999 |           |
| Selma_2003_Indonesia | Selma      | 2003 | Indonesia |
| Babbette_Morocco     | Babbette   |      | Morocco   |
| Erhart_1985_Japan    | Erhart     | 1985 | Japan     |
| 1997_France          |            | 1997 | France    |
| Derward_1985_China   | Derward    | 1985 | China     |
+----------------------+------------+------+-----------+


=arrayformula(substitute(if(isblank(B2:B),,B2:B&"_")&if(isblank(C2:C),,C2:C)&if(isblank(D2:D),,"_"&D2:D),"__","_"))

screenshot of data & formula

Solution

  • For your current set of data try

    =ArrayFormula(substitute(trim(transpose(query(transpose(B2:D),,rows(B2:B)))), " ", "_"))

    and see if that works?

    Example