Concatenate with gap suppression!

B

Bretter99

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
 
P

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
 
B

Bretter99

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top