Nov 25 2021 09:15 AM
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
Nov 25 2021 09:24 AM
And what is in E71:E76 - zeroes, blanks or something else?
Nov 25 2021 10:45 AM - edited Nov 25 2021 10:48 AM
here is somewhat of what i am looking for
in the firs cell is actually a different formula creating the array.
Nov 25 2021 11:07 AM
SolutionSo, 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 ) )
Nov 25 2021 03:50 PM - edited Nov 25 2021 03:55 PM
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.
=TEXTJOIN(",",TRUE,FILTER(E64:E76,ISNUMBER(E64:E76)*(E64:E76>0)))
Nov 25 2021 11:07 AM
SolutionSo, 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 ) )