Concatenate with gap suppression!

[Guest]

Hi,

I have a list of data in a column that are lookup answers from across
several sheets in excel 2000. the data looks something like this:

black
0
blue
0
0
0
green
0
red

(the zeros are returned from blank cells)

I want to be able to concatenate all the words with a line break between
each on but suppress the lines with a zero so my end cell looks like this:

black
blue
red
green

i can manage =concatenate(a1,char(10),a2,char(10),etc....)
but i cant think how to suppress the zeros and still get a line break after
each word.
Any help would be great thanks.
Brett

 

[Pete_UK]

This will (almost*) do it for your 9 values in A1 to A9:

=IF(A1=0,"",A1)&IF(A2=0,"",CHAR(10)&A2)&IF(A3=0,"",CHAR(10)&A3)&IF(A4=0,"",CHAR(10)&A4)&IF(A5=0,"",CHAR(10)&A5)&IF(A6=0,"",CHAR(10)&A6)&IF(A7=0,"",CHAR(10)&A7)&IF(A8=0,"",CHAR(10)&A8)&IF(A9=0,"",CHAR(10)&A9)

though I'm sure there must be a simpler way. You need to format the
cell to wrap text and you will get:

black
blue
red
green

as required.

*If instead of "black" you had a zero for the first entry, then you
will have one blank line above the rest of your values.

Hope this helps.

Pete

 

[Guest]

try:

=SUBSTITUTE(CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3),"0" & CHAR(10),"")

 

[Guest]

Thanks Toppers, awesome mate, I been trying to do this for years, lol
Thanks Pete_UK same result, just more typing ,
Brett

 

[Guest]

Guys,

Is there any way that i can also get this to skip duplicates?

Thanks