Search code examples
excelexcel-formulatextjoin

Is it possible to join a range of cells into one cell and remove 0 values?

I want to copy a range of cells into one cell with line break. My range is A1:A59. Using the following formula I managed to copy the range in the desired cell. My problem is that in this range there are some cells that have 0 and I want to leave them out.

=TEXTJOIN(CHAR(10);TRUE;A3:A59)

Is it possible to use a TEXTJOIN along with an IF statement that leaves the 0 out? I can use any other formula that provides the desired result but not vba.

Solution

  • You can use either:

    =TEXTJOIN(CHAR(10),,FILTER(A3:A59,A3:A59<>0))

    Or:

    =TEXTJOIN(CHAR(10),,IF(A3:A59<>0,A3:A59,""))