Excel 2010 Concatenate If statement

Hello guys,

I have been puzzled by the following formula for far too long.

I have about 10 columns of possible data all of which have at least 2 words. I need to concatenate these rows into a comma separate list.

I tried: =SUBSTITUTE(TRIM(G2 & " " & H2 & " " & I2 & " " & J2 & " " & K2 & " " & L2 & " " & M2 & " " & N2 & " " & O2 & " " & P2& " " & Q2), " ", ", ")

This works well if we only have a space between cell values and you only look to SUBSTITUTE for a single space. I realize now it is because of TRIM()... TRIM is removing the double space to just a single space between each cell value therefore SUBSTITUTE is not finding a double space to replace. I know there has to be a way around this using a formula. What are your thoughts?

Type 1	Type 2	Type 3	Type 4	Type 5
Type 1		Type 3
Type 1				Type 5

Current Answer for Row1: Type 1 Type 2 Type 3 Type 4 Type 5
Answer desired for Row1: Type 1, Type 2, Type 3, Type 4, Type 5

Current Answer for Row2: Type 1 Type 3
Answer desired for Row2: Type 1, Type 3

Current Answer for Row3: Type 1 Type 5
Answer desired for Row3: Type 1, Type 5

Thanks for the help!

Are you open to using a VBA function to do this?

Hi

This is messy, but it avoids VBA. Copy this down and across as many columns as you have "Types". You can hide all but he last culumn if you want...

=IF(A1="",F1,F1&A1&IF(COUNTA(B$1:$E1)>0,", ",""))

Adjust ranges as needed

Kind of ugly but it works (as long as the data does not contain commas)...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE
(SUBSTITUTE(G2&","&H2&","&I2&","&J2&","&K2&","&L2
&","&M2&","&N2&","&O2&","&P2&","&Q2,
" ","~"),","," "))," ",", "),"~"," ")

oops sorry, use this...

=IF(A1="",F1,F1&A1&IF(COUNTA(A1:$E1)>1,", ",""))

You're welcome. Thanks for the feedback!