Textjoin and skip cells with zero

[andrew_milonavic]

Hi,

Is it possible to textjoin and skip cells with the number zero. I understand I can skip blank cells with true/false etc but zero's are not blank.

Example below:

1

2

0

3

4

1,2,3,4

<tbody>
</tbody>

Thanks

Andrew

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER not just enter. If done correctly Excel will put {} around the formula.

=TEXTJOIN(",",TRUE,IF(A1:E1>0,A1:E1,""))

 

[andrew_milonavic]

Thanks Scott! Works perfectly.

I really appreciate the help!

Andrew

 

[andrew_milonavic]

Hey Scott,

Any way to make it return blank if all the cells are zero?

Thanks

Andrew

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(1/A1:E1),A1:E1,""))

 

[andrew_milonavic]

Thanks Aladin!

works great. I appreciate the help.

Andrew

 

[Aladin Akyurek]

Thanks Aladin!

works great. I appreciate the help.

Andrew

You are welcome.

 

[andrew_milonavic]

Hey Aladin / Scott,

Last followup.

Any way to make the formula skip zero's with other cells containing text?

Example:

Cat

Dog

0

Fish

Cat,Dog,Fish

<tbody>
</tbody>

Thanks

Andrew

 

[Aladin Akyurek]

Hey Aladin / Scott,

Last followup.

Any way to make the formula skip zero's with other cells containing text?

Example:

Cat

Dog

0

Fish

Cat,Dog,Fish

<tbody>
</tbody>

Thanks

Andrew

Control+shift+enter:

=TEXTJOIN(",",TRUE,IF(ISTEXT(A1:D1),A1:D1,""))

 

[andrew_milonavic]

Thanks Aladin! Works perfect.

Andrew