Combine and display values in range in 1 cell but skip blank cells
 

I have a range of data in collumns that I wish to combine into one cell at
location E3 (with comas so it should look like 9,5,10b,4). The range is
AE3:BG3 but some cells are blank. The values in the cells are just numbers
but I dont want to add them, just display them. Any tips are appreciated.

the data looks like this:
AG AH AI AJ -
11 9 23 24 18 21 3 4 8 10b 15 16 20 17

11 18 21 4 10b 15 16 20 17

Combine and display values in range in 1 cell but skip blankcells
 

Well, this is a bit clumsy given that you have so many cells:

=SUBSTITUTE(AE3&", "&AF3&", "&AG3&", "&AH3&", "&AI3&", "&...
.... &BE3&", "&BF3&", "&IF(BG3="",",",BG3&","),", ,",",")

You will need to continue with the terms:

.... &cell&", " ...

in the middle of the formula for each of your cells. You will have a
comma at the end of the last non-blank term - you could delete this
using LEFT.

Hope this helps.

Pete

On Sep 4, 1:31*pm, Swiss wrote:
I have a range of data in collumns that I wish to combine into one cell at
location E3 (with comas so it should look like 9,5,10b,4). *The range is
AE3:BG3 but some cells are blank. *The values in the cells are just numbers
but I dont want to add them, just display them. *Any tips are appreciated.

the data looks like this:
AG * * * * * AH * * * * * * AI * * * * * * AJ -
11 * * *9 * * * 23 * * *24 * * *18 * * *21 * * *3 * * * 4 * * * 8 * * * 10b * * 15 * * *16 * * *20 * * *17

11 * * * * * * * * * * * * * * *18 * * *21 * * * * * * *4 * * * * * * * 10b * * 15 * * *16 * * *20 * * *17

Combine and display values in range in 1 cell but skip blank cells
 

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html

then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(AE3:BG3<"",AE3:BG3,"" )&" "))," ",", ")

ctrl+shift+enter, not just enter



"Swiss" wrote:

I have a range of data in collumns that I wish to combine into one cell at
location E3 (with comas so it should look like 9,5,10b,4). The range is
AE3:BG3 but some cells are blank. The values in the cells are just numbers
but I dont want to add them, just display them. Any tips are appreciated.

the data looks like this:
AG AH AI AJ -
11 9 23 24 18 21 3 4 8 10b 15 16 20 17

11 18 21 4 10b 15 16 20 17

Combine and display values in range in 1 cell but skip blank c
 

Thank you so much - that solution worked well.

Cheers -

"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html

then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(AE3:BG3<"",AE3:BG3,"" )&" "))," ",", ")

ctrl+shift+enter, not just enter



"Swiss" wrote:

I have a range of data in collumns that I wish to combine into one cell at
location E3 (with comas so it should look like 9,5,10b,4). The range is
AE3:BG3 but some cells are blank. The values in the cells are just numbers
but I dont want to add them, just display them. Any tips are appreciated.

the data looks like this:
AG AH AI AJ -
11 9 23 24 18 21 3 4 8 10b 15 16 20 17

11 18 21 4 10b 15 16 20 17