Concatenate Question (formatting for blank spaces)

[gregz12345]

Hello All-

I have a report with four columns listing differing FTA names.
I need to combine these columns into one column (concatenate) separated by commas, but some of the fields may be blank and when this happens I can’t have a space or double commas.
Below is an example of what I am looking for.

NAFTA	AUSFTA	KORUS	UCFTA	I want this:
NAFTA	AUSFTA	KORUS	UCFTA	NAFTA,AUSFTA,KORUS,UCFTA
NAFTA		KORUS		NAFTA,KORUS
NAFTA			UCFTA	NAFTA,UCFTA
	AUSFTA	KORUS		AUSFTA,KORUS
NAFTA				NAFTA

<tbody>
</tbody>

Thanks!

 

[Rick Rothstein]

Assuming you concatenated text will not be longer than 99 characters (increase the 99 if it could be), then give this formula a try...

=MID(IF(A2="","",","&A2)&IF(B2="","",","&B2)&IF(C2="","",","&C2)&IF(D2="","",","&D2),2,99)

 

[cooper645]

I came up with the following:

=CONCATENATE(IF(ISBLANK($A1),"",$A1&","),IF(ISBLANK($B1),"",$B1&","),IF(ISBLANK($C1),"",$C1&","),IF(ISBLANK($D1),"",$D1&","))