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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]1,2,3,4
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cat
[/TD]
[TD]Dog
[/TD]
[TD]0
[/TD]
[TD]Fish
[/TD]
[TD]Cat,Dog,Fish
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

Andrew

 

[Aladin Akyurek]

Hey Aladin / Scott,

Last followup.

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

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]0[/TD]
[TD]Fish[/TD]
[TD]Cat,Dog,Fish[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

Andrew

Control+shift+enter:

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

 

[andrew_milonavic]

Thanks Aladin! Works perfect.

Andrew