Concatenating columns ignoring blanks

[rapscalli]

Hi all,

i have a number of columns of data that I wish to concatenate together, separated by commas for pasting into another application. Easy enough to do using the concatenate function, but since a lot of cells are blank, I am getting output form the concatenate function with long strings of commas.

My concatenate function
=CONCATENATE(AP2,", ",AQ2,", ",AR2,", ",AS2,", ",AT2,", ",AU2,", ",AV2,", ",AW2,", ",AX2,", ",AY2,", ",AZ2,", ",BA2,", ",BB2,", ",BC2,", ",BD2,", ",BE2,", ",BF2,", ",BG2,", ",BH2,", ",BI2,", ",BJ2,", ",BK2,", ",BL2,", ",BM2,", ",BN2)

Example output
, , , , , , , , , , , , , , , , , , , , , , , XXX,
, , , , , , , , , , , , , , , , , , , , , , , XXX, YYY



What is the easiest way to get the output I need, which in the first example above would be XXX, and in the second example would be XXX, YYY

[Pete_UK]

You could change your formula to this:

=SUBSTITUTE(SUBSTITUTE(CONCATENATE(AP2,", ",AQ2,", ",AR2,", ",AS2,", ",AT2,", ",AU2,", ",AV2,", ",AW2,", ",AX2,", ",AY2,", ",AZ2,", ",BA2,", ",BB2,", ",BC2,", ",BD2,", ",BE2,", ",BF2,", ",BG2,", ",BH2,", ",BI2,", ",BJ2,", ",BK2,", ",BL2,", ",BM2,", ",BN2),", ,",","),",,","")

Hope this helps.

Pete

[rapscalli]

Hi Pete, that is closer but not exactly what I need.

Previous output
, , , , , , , , WWW, , , , , , , , , , , , XXX, YYY, ZZZ, ,

Output using your formula:-
, , , , WWW, , , , , , XXX, YYY, ZZZ,

PS I have up to 24 consecutive commas in some cells. I do not mind having helper columns as this is purely a calculation sheet for an output that I paste elsewhere.

[Pete_UK]

Okay, you can do it like this:

=SUBSTITUTE(SUBSTITUTE(TRIM(IF(AP2="","",AP2&", ")&IF(AQ2="","",AQ2&", ")&IF(AR2="","",AR2&", ")&IF(AS2="","",AS2&", ")&IF(AT2="","",AT2&", ")&IF(AU2="","",AU2&", ")&IF(AV2="","",AV2&", ")&BN2&"/"),", /",""),"/","")

You can see in the section coloured blue that the comma-space only gets added in if the cell is not empty. You will need to complete this for the cells from AV2 up to the BN2 term (coloured red). Notice that I am adding a slash character at the end, so that I can then remove the trailing ", /" if BN2 is empty or the final slash if it is not empty.

Hope this helps.

Pete

[Jonmo1]

Are there any spaces pre existing in any of the cells?

If not, you could do

=SUBSTITUTE(TRIM(CONCATENATE(AP2," ",AQ2," ",AR2," ",AS2,etc))," ",", ")

[Jonmo1]

If there are pre existing spaces in the cells...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(AP2,"|",AQ2,"|",AR2,"|",AS2,etc)," ","^"),"|"," "))," ",", "),"^"," ")

[Tony Valko]

Are you able/open to using a VBA function for this?

If so, the code is at this post:

https://www.excelforum.com/showthread.php?p=3096647

Then, after installing the code, to use it enter this array formula**:

=concatall(IF(AP2:BN2<>"",AP2:BN2,""),", ")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[rapscalli]

Hi all, many thanks for there response. In the end I used a number of helper columns as I was in a rush to supply the output.

My solution that I used (nowhere near as elegant as the above..)
The columns containing data I wished to concatenate were in AR to BP, some cells containing blanks. Row 1 contained headers, rows 2 and below contained the data.

The first concatenating column in BQ
=IF(AR2="","",AR2)

The second concatenating column in BQ, dragged across to CO
=IF(AS2="",BQ2,CONCATENATE(BQ2,",",AS2))

This second concatenation column ensured that i didn't add a blank column, avoiding the need to edit the output which previously contained a string of commas.
However, the output could begin with a comma if BQ was blank, so I used the following function to trim the last concatenation column (CO)
=IF(LEFT(CO2,1)=",",RIGHT(CO2,LEN(CO2)-1), CO2)

As I said, not elegant...

I would be interested to see the most elegant solution to concatenate a number of columns, containing a mix of data and empty cells, to a comma separated output.

Many thanks all. I will keep this bookmarked for when I come across a similar problem.

[Tony Valko]

I would be interested to see the most elegant solution to concatenate a number of columns, containing a mix of data and empty cells, to a comma separated output.

Here's a small sample file created in Excel 2010 that demonstrates the VBA function. The data range is B1:F1.