Conditional Concatenate?


Dan B.

I am trying to pull survey information from multiple sheets to one summary
sheet. With the survey, there is an option to make a comment, so each sheet
may or many not have a comment. I used the CONCATENATE function to combine
multiple cells, but is there a way to merge different numbers of cells?
Sheet1 - Info in A1, B1, C1, D1
Sheet2 - Info in A1, B1, C1
Sheet3 - Info in A1
Sheet4 - Info in A1, B1

I can set up the summary page for this, but the next survey might have
Sheet1 - Info in A1, B1
Sheet2 - Info in A1, B1, C1
Sheet3 - Info in A1, B1, C1, D1
Sheet4 - Info in A1

Is there a way to merge a string of data, but depending on how many cells
are in a given range, the merging will not add extra characters, but will
only merge the information that is there?

Hope this makes sense. Thanks.



Gord Dibben

This UDF will ignore blank cells.

Gives you comma and space separated strings in each cell.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ", "
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

In Summary sheet A1 enter this formula


Copy down to A4

Gord Dibben MS Excel MVP



T. Valko

If there aren't too many cells involved *and* the data in those cells
doesn't contain any space characters you use something like this.

All on one line:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question