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)," ",", ")

--
Biff
Microsoft Excel MVP


"Dan B." wrote in message
...
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?
Example:
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.