Thanks for the input Rick.

Have updated per your suggestion.


Gord

On Sat, 5 Jul 2008 14:11:02 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

You might want to consider this minor change to your code so that it returns
the empty string if there is nothing in any of the cells rather than having
the function "error out" if that is the case...

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & ","
Next
If Len(sbuf) 0 Then ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

For the more general case (this does not apply to to the OP), if the number
of cells in the CellBlock are very large, the repeated concatenations can
become inefficient as the execution nears the end of the loop. This code may
be more efficient for this situation (not sure as I haven't attempted to
time it) as it avoids concatenations altogether (although I would guess it
is not as efficient as your function for smaller number of cells due to the
ReDim Preserve and Join function calls)...

Function ConCatRange(CellBlock As Range) As String
Dim X As Long
Dim Z As Long
ReDim Content(1 To CellBlock.Count) As String
For X = 1 To UBound(Content)
If Len(CellBlock(X).Value) 0 Then
Z = Z + 1
Content(Z) = CellBlock(X).Value
End If
Next
If Z 0 Then
ReDim Preserve Content(1 To Z)
ConCatRange = Join(Content, ",")
End If
End Function

Rick


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Function ConCatRange(CellBlock As Range) As String
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(E1:Y1) and copy down.


Gord Dibben MS Excel MVP

On Sat, 5 Jul 2008 05:26:01 -0700, LM
wrote:

I have a list of 2000 rows by 25 columns. I want to concatenate a cells e
to
y in each row. Not all cells have data in them but the cells that do have
data in them have a seven digit number.

I need to concatenate all cells with data in them and have them end up in
one column, with each seven digit number divided by a comma, i.e.
1234567,8912345,6789123,4567890.

If I use &","& I end up with extra commas where I have had blank cells.

I tried using spaces when concatenating, i.e. &" "&, removing all trailing
spaces using TRIM and then replacing the remaining spaces with commas but,
no
matter how I try to format the column as text, as soon as I replace all
the
spaces with commas, Excel turns most of them into numbers that look like
this
- 2.02411E+62 and I can't get that back to appearing as a number or text
with
commas between each seven numbers.

I tried using the following formula:

=substitute(trim(C2&" "&D2&" "&E2&" "&F2)," ",", ")

which I found in relation to a similar question in this discussion group
but
I can't get that formula to work (obviously I have the equations going up
to
&Y2) and I can't work out why even that formula won't work.

Any help would be so gratefully appreciated as I have so far spent hours
on
this.

Lyn.