Search code examples
joingoogle-sheetsarray-formulasgoogle-sheets-formulatextjoin

Join character to columnar array


I want to add a character ("~ ") to the front of each value of a columnar array, but every formula I've tried concatenates the values into a single cell rather than back to the column array. Do I need to add SPLIT? What am I doing wrong?

This is what I've tried most recently

=JOIN("~ ",FILTER(Categories!A2:A,LEN(Categories!A2:A)))

=ArrayFormula(TEXTJOIN("~ ",TRUE,Categories!A2:A))

=ArrayFormula(JOIN("~ ",{Categories!A2:A}))

Ultimately, what I would like to see in a single column is:

~ Category 1

~ Category 2

etc.


Solution

  • =ARRAYFORMULA(IFERROR(SPLIT(IF(Categories!A2:A<>"", "~ ♦"&Categories!A2:A, ), "♦")))
    

    0


    =ARRAYFORMULA(IF(Categories!A2:A<>"", "~ "&Categories!A2:A, ))
    

    0