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

 

[andrew_milonavic]

Hey Aladin / Scott,

Is it possible to offset the range every 4 columns?. Data range would be a1:d1 and if I copied that cell over a column the next range would be e1:h1.

My results are on a separate page in case your wondering where the result cell is going in relation to how the data is laid out.

Thanks

Andrew

 

[Aladin Akyurek]

Hey Aladin / Scott,

Is it possible to offset the range every 4 columns?. Data range would be a1:d1 and if I copied that cell over a column the next range would be e1:h1.

My results are on a separate page in case your wondering where the result cell is going in relation to how the data is laid out.

Thanks

Andrew

In B1 control+shift+enter, not just enter, and copy across:

=TEXTJOIN(", ",TRUE,IF(ISTEXT(OFFSET($A$1:$D$1,0,(COLUMNS($A:A)-1)*4)),OFFSET($A$1:$D$1,0,(COLUMNS($A:A)-1)*4),""))

See also Jonmo1's response in your additional "follow-up" thread.

 

[andrew_milonavic]

Thanks Aladin! It works!, so does Jonmo1's on my followup thread.

Now I just need to understand "how" they work

I really appreciate all your help

Andrew

 

[Aladin Akyurek]

Thanks Aladin! It works!, so does Jonmo1's on my followup thread.

Now I just need to understand "how" they work

I really appreciate all your help

Andrew

You are welcome.

 

[andrew_milonavic]

Hey Aladin,

The formula works when the results are on the same sheet as the data, but when the results are calculated on another sheet it will not skip the zeros from being returned in the results.

Thoughts?

Thanks

Andrew

 

[Aladin Akyurek]

Hey Aladin,

The formula works when the results are on the same sheet as the data, but when the results are calculated on another sheet it will not skip the zeros from being returned in the results.

Thoughts?

Thanks

Andrew

Let A1:Z1 of Sheet1 house the data.

In A1 of Sheet2 control+shift+enter and copy across:

=TEXTJOIN(", ",TRUE,IF(ISTEXT(OFFSET(Sheet1!$A$1:$D$1,0,(COLUMNS($A$1:A1)-1)*4)),OFFSET(Sheet1!$A$1:$D$1,0,(COLUMNS($A$1:A1)-1)*4),""))

 

[andrew_milonavic]

Thanks Aladin.. It works!

Sorry for the slow reply. been away.

Andrew

 

[Aladin Akyurek]

Thanks Aladin.. It works!

Sorry for the slow reply. been away.

Andrew

No problem. Thanks for the update.