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

 

[Joe4]

Glad you got it figured out.

Here is a little advice for posting future questions. It is almost always better to post a brand new thread than to pull up an old one and tack on to that. The reason being is many people use the "Zero Reply Posts" to look for new, unanswered questions. However, since you are asking your question on to the end of an existing thread, it will never show up on that list. So many people will not see it.

I usually advise people to post a new thread, and they can include a link to the other thread (if they think it may be helpful to those who might respond).

 

[claidheamdanns]

Thanks. I hadn't thought of it this way. I'm assuming this would apply more to reviving decades old threads, whereas 10 posts within the same current week, by different people, with regards to the same/similar question would be frowned upon, right?

 

[Joe4]

I'm assuming this would apply more to reviving decades old threads, whereas 10 posts within the same current week, by different people, with regards to the same/similar question would be frowned upon, right?

Not really, unless it happens to be the exact same question (which is usually an indication of a bunch of people taking the same Excel class!). As matter as fact, some people call it "thread hijacking" when people post on to other people's threads (especially if the original question has not been answered to satisfaction yet).

Most of the time, it is best to post to your own thread. An exception I can see is if someone came up with a very elaborate solution (i.e really long VBA code or a really long formula), and you are hoping that particular person can help you. But understand if you are posting to old threads, sometimes many people on those threads haven't been active here for a long time.

 

[claidheamdanns]

Okay, well, I'll try and keep this in mind. In most other forums I am part of, multiple people asking the same/similar question is met with a "did you search the forum and see if someone already asked this question?"

LOL … when in Rome, though, eh?

 

[Joe4]

"did you search the forum and see if someone already asked this question?"

Oh, we do recommend that you do that to see if you can find a solution. Many times people are able to find what they need and never have to post a question.
And there is no rule saying you cannot post to old questions. Just bear in mind that doing that will probably reduce the number of people who see it, thus reducing the chance of getting an answer.
So the advice I am giving you is to maximize the chances of getting an answer to your question, that's all.

 

[claidheamdanns]

Thanks again for the advice. Nice to know the ropes.

P.S. Totally off topic, but I love your "icon" — one of my fave cartoons.

 

[IkBenNietHier]

Provided only that none of the cells contain numbers longer than 1 digit! See cell B5 below.

A more robust formula is in A14. Both formulas copied across.

Of course Chris has posted a fine solution already. My suggestion below may become more useful if there are more cells involved.

Ignore 0 cells

	A	B

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]101[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]119[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]101[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]1019[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A5 =SUBSTITUTE(A1&A2&A3,0,"")+0 A12 =SUBSTITUTE(SUBSTITUTE("|"&A8&"|"&A9&"|"&A10&"|","|0|",""),"|","")+0 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The Formula for A12 should actually be:

=SUBSTITUTE(SUBSTITUTE("|"&A8&"|"&"|"&A9&"|"&"|"&A10&"|","|0|",""),"|","")+0

This makes sure every cell value will be surrounded by "|" .

In your formula the "|" inbetween A8 and A9 will only be used for A8 or A9. With an extra "|" inbetween the value's they are both surrounded by horizontal stripes. So if one |0| is deleted the next one wil still be surrounded.

Example:
With your formula if the red part of |10|0|0| will be substituted by "" , |100| will be left and the result will be 100.

With my addition to you formula if the red part of |10||0||0| will be substituted by "" , |10||0| will be left, then the next |0| can be substatuted by "" and the result will be 10.


I hope this helps,

And btw,
I Was Not Here

 

[jasonb75]

If you're reviving a dormant thread by trying to improve on an 9 year old answer, you could at least suggest a more up to date method such as =TEXTJOIN("",1,A8:A10)

 

[Peter_SSs]

The Formula for A12 should actually be:

=SUBSTITUTE(SUBSTITUTE("|"&A8&"|"&"|"&A9&"|"&"|"&A10&"|","|0|",""),"|","")+0

You are quite right that my formula was not robust and would produce the incorrect result with an example like 10,0,0

However, I'm not sure that the A12 formula should be as you stated. Whilst it could be that, your formula could be written more efficiently as

=SUBSTITUTE(SUBSTITUTE("|"&A8&"||"&A9&"||"&A10&"|","|0|",""),"|","")+0

or replaced with the even simpler

=(IF(A8=0,"",A8)&IF(A9=0,"",A9)&IF(A10=0,"",A10))+0

or as jasonb75 indicated, using TEXTJOIN it could be the the following array formula with the added advantage of easily dealing with a larger range.

{=TEXTJOIN("",1,IF(A8:A10=0,"",A8:A10))+0}

 

[IkBenNietHier]

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.

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