Excel 2000 Concatenate cells but excluding any empty cells

[shaleen7]

Current Excel Spreadsheet

App No Group1 Group2 Group3
1569A90 US FR
1569A91 FR US UK
1569A92 UK
1569A93 JP IT US
1569A94 IT US




Desired Results

App No Group1 Group2 Group3 Concatenate column
1569A90 US FR US,FR
1569A91 FR US UK FR,US,UK
1569A92 UK UK
1569A93 JP IT US JP,IT,US
1569A94 IT US IT,US


Is the desired possible because I want to skip any empty cells?
Any suggestions?
Thanks

 

[SkipVought]

Hi,

[tt]
=B2&if(isblank(c2),"",",")&c2&if(isblank(d2),"",",")&d2....
[/tt]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[rdeleon]

I'm sure there a million ways to skin this cat!

=IF(ISBLANK(A1),IF(ISBLANK(B1),C1,IF(ISBLANK(C1),B1,B1&","&C1)),IF(ISBLANK(B1),A1,IF(ISBLANK(C1),A1&","&B1,A1&","&B1&","&C1)))

 

[byundt]

Here is a shorter formula for the concatenation:
=SUBSTITUTE(TRIM(B5 & " " & C5 & " " & D5)," ",",")

It concatenates with blanks, then uses TRIM to remove leading, trailing and doubled blanks. Finally, it uses SUBSTITUTE to change the remaining blanks to commas.

 

[byundt]

Here is a shorter formula for the concatenation:
=SUBSTITUTE(TRIM(B1 & " " & C1 & " " & D1)," ",",")

It concatenates columns B with blanks, then uses TRIM to remove leading, trailing and doubled blanks. Finally, it uses SUBSTITUTE to change the remaining blanks to commas.