Concatenate Question (formatting for blank spaces)

gregz12345

New Member
Joined
Sep 19, 2018
Messages
7
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,920
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2013
Messages
521
I came up with the following:

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

Watch MrExcel Video

Forum statistics

Threads
1,099,121
Messages
5,466,812
Members
406,499
Latest member
ToyoMike

This Week's Hot Topics

Top