Concatenate a range but ignore any cell with 0.

[slimjimbo21]

Hi there,

I'm concatenating (is that a word?!) a range of data into one cell using the =CONCATENATE function.

However, I want the function to ignore and skip any cell which has a '0' in.

for example, data:

A1= 7
A2= 0
A3= 9

A5 =CONCATENATE(A1,A2,A3)

I want to see A5= 79, but have A5= 709

 

[IkBenNietHier]

Yes, you are right. I actually don't know why I didn't put the || next to each other

I eventually used the =TEXTJOIN command. This makes it even more efficient. Thanks

 

[Peter_SSs]

Just trying to help here. I didn't even notice that the answer I reply'ed to was 9 years old. I tried to implement it in my sheet end found out it wasn't working properly, so I fixed it and posted it here so someone else won't have the same problems I had.

Fair enough.

But thank you for the =TEXTJOIN tip. I used that and it makes the formula a bit shorter and efficient :wink:

Especially if you had 10 or 200 cells to deal with in one formula!
You're welcome.