Display multiple data in one cell without summing, whilst also excluding 0s

[erinsjoy]

I have attached an example of what I am trying to achieve. I have several rows of data, each having 3 columns of data, some with required figures, some with zeros. For each row, I would like to have one cell that shows the figures from the other three columns, next to each other, not added together, but would also like to exclude any zero values.
30, 0, 0 would show as 30
30, 34, 0 would show as 30,34 or 30/34
30, 0 240 would show as 30,240 or 30/240
Any suggestions gratefully received. Thank you

[wk9128]

F2 cell array formula

[erinsjoy]

Thank you for your help, that is a great solution. Unfortunately my version of Excel does not have ConcatY so I am still struggling to get it to work.

[Limor_OP]

Hi,
In F2 and down, (for up to 3 numbers in a line >0):

Please Login or Register  to view this content.

How many figures to the maximum you may have in one line?

[erinsjoy]

I have just tried it on my work system where it does have Concat, and am thinking I need to remove the two {}, and also to remove the Y after Concat? Is that correct. By doing that, I have got the correct numbers, but they are not separated by a comma as on yours, do you have any idea what I am doing wrong? If I keep the Y after Concat, it results in #NAME? If I use the {}, it just copies the formula. I am fairly new to Excel and on a steep learning curve, so apologise if I ask stupid or obvious things. It is a real challenge, but I love learning these things.

[wk9128]

I have just tried it on my work system where it does have Concat, and am thinking I need to remove the two {}, and also to remove the Y after Concat? Is that correct. By doing that, I have got the correct numbers, but they are not separated by a comma as on yours, do you have any idea what I am doing wrong? If I keep the Y after Concat, it results in #NAME? If I use the {}, it just copies the formula. I am fairly new to Excel and on a steep learning curve, so apologise if I ask stupid or obvious things. It is a real challenge, but I love learning these things.

Textjoin and Concat only available in MS 365 and Excel 2019

That is a custom function
First Press Alt+F11 to open the Visual Basic Editor
Then you will see the Code,You can copy the code to your worksheet

Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT,
and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {}
around the formula (though do not attempt to manually insert these yourself).

[erinsjoy]

Thank you so much for your help.

[erinsjoy]

Thank you for your help, I have learned much from you :-)

[wk9128]

Response #8

If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved