array to text


is there a way to format an array as one string in a cell?

right now i am using this formula: =TEXTJOIN(", ",TRUE,E64:E76)

and get this result: 21, 23, 24, 25, 26, 27, 28, 21, 0,  ,  ,  ,  , 0.

but what i want is this: 21, 23, 24, 25, 26, 27, 28, 21

7 Replies


And what is in E71:E76 - zeroes, blanks or something else?

here is somewhat of what i am looking for

in the firs cell is actually a different formula creating the array.

best response confirmed by Josh_Waldner (Contributor)


So, you need to exclude cells with zero, blank cells and cells with non-breaking space. That could be like

=TEXTJOIN(", ", 1, IF( ( K8:K22=0 ) + ( K8:K22=UNICHAR(160) ), "", K8:K22 ) )
thanks. that works perfect.

@Josh_Waldner , you are welcome


Sorry for jumping on this Mr @Sergei Baklan  but I found it quite interesting and I would like to propose some approach as well.


@Josh_Waldner Considering your data range  E64:E76.








@Juliano-Petrukio That's okay, more variants the better