Skip blanks when concatenating

Question

I'm not sure if I am trying to go about this the right way, but...  I want to concatenate the text from a number of columns separated by ", " but I need to skip blank cells.  For example:

                    A                    B                    C                    D                     E                    F

1              Apples           Oranges           BLANK             Pears               BLANK          Bananas

When I concatenate Row 1 I get: "Apples, Oranges, , Pears, , Bananas"

What I would like to get is: "Apples, Oranges, Pears, Banans"

Is there a way to skip the blank cells so that I don't get extra commas in the result?

Thanks.

Answer 1

Hi,

Try this,

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1)," ",", ")

Answer 2

you could try: 

assuming the data is columns A-E: 

Column F : =CONCATENATE(A1,",",B1,",",C1,",",D1,",",E1) 

Column G : =SUBSTITUTE(F1, ",,", ",") 

Column H: =SUBSTITUTE(G1, ",,", ",") 

Column I : =IF(FIND(",",H1)=1,MID(H1,2,1000),H1) 

Column J : ==IF(ISERR(FIND(",",I1,LEN(I1))),I1,LEFT(I1,LEN(I1)-1)) 

it doesn't "skip" the blanks, but 

G: replaces doubled commas with single commas 

H handles remaining double commas due to multiple blanks 

I handles if the first column is blank producing a leading , 

J handles if the last column is blanks producing a trailing ,

Answer 3

Hi,

Download and install the Morefunc add-in from this link - http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/ (Read point 4 for instructions on installing this add-in for Excel 2010 and higher versions)

Once downloaded and installed, you may use this formula

=MCONCAT(A1:F1,",")

Hope this helps.

Answer 4

Hi,

Take a look at the answers here which suggest using a UDF: http://answers.microsoft.com/en-us/office/forum/office_2003-excel/lookup/1274e035-1efc-4033-a7ba-d976e0ffe570

Cheers

Rich

Answer 5

I'm not sure if I am trying to go about this the right way, but...  I want to concatenate the text from a number of columns separated by ", " but I need to skip blank cells.  For example:

 

                    A                    B                    C                    D                     E                    F

1              Apples           Oranges           BLANK             Pears               BLANK          Bananas

 

When I concatenate Row 1 I get: "Apples, Oranges, , Pears, , Bananas"

What I would like to get is: "Apples, Oranges, Pears, Banans"

 

Is there a way to skip the blank cells so that I don't get extra commas in the result?

 

Thanks.

You could use a UDF which will ignore blank cells.

Function ConCatRange(CellBlock As Range) As String

'for non-contiguous cells  =ccr((a1:a10,c4,c6,e1:e5))

'note double parens

Dim cell As Range

Dim sbuf As String

    For Each cell In CellBlock

    On Error GoTo fred

    If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ", "

        Next

    ConCatRange = Left(sbuf, Len(sbuf) - 2)

fred:

End Function

Gord