Thanks for the update.

And the extra comma fix up.

I had not noticed that defect.

Another version which allows user to decide on de-limiter.

Function ConCatRange22(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange22(a1:a10,"|") desired delimiter between quotes
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text < "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell
ConCatRange22 = Left(sbuf, Len(sbuf) - Len(Delim))
End Function


Gord

On Fri, 13 Nov 2009 14:19:02 -0800, CD-UIO
wrote:

Hi Gord,

Thanks again, yes it works, I think I was copying the function where it
shouldn't have gone.

I modified it slightly like this to include a space after the coma, I had to
think why I was getting the coma af ther the last word, but adjusting the
last row to - 2 seems to have worked.

Many thanks!


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 & ", "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function


"Gord Dibben" wrote:

The UDF I posted will work.

In BC2 enter =concatrange(D2:BA2)

Then copy down.


Gord

On Fri, 13 Nov 2009 12:14:19 -0800, CD-UIO
wrote:

Thanks to both Gord and Don for these quick replies,

I should have specified that I want to combine these into one cell for each
row eg My data is from D2:BA2 and I want to combine this into BC2, and then
with D3:BA3 into BC3, D4:BA4 into BC4, etc, etc.

No problem with using a function, but didn't get either of these two to work.

Cheers
Chris

"Gord Dibben" wrote:

Into one column or one cell?

If you are willing to use a User Defined Function.

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 & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(A1:AX1)

Ignores blank cells.

Note: Excel has a limitation of 32767 characters in a cell with only 1024
of those visible in the cell.

50 columns by many rows could limit out.


Gord Dibben MS Excel MVP

On Thu, 12 Nov 2009 14:34:03 -0800, CD-UIO
wrote:

I have 50 Columns of text data, not all of which contain values, I want to
combine these into one column, with each value separated by a coma, but
omitting those cells with no values. I tried with IF functions and ISNULL,
but it gets a bit long. Any neater solutions?

Thanks a lot

.


.