This should do it. The 6 refers to column F which is one column to the left
of the 1st column. So, if your data starts in col B and you want the new
string in col a, use 1 instead of 6. Correct if you have word-wrap.

Sub joinwithoutblanks()
mc = 6
For c = 3 To Cells(Rows.Count, mc + 1).End(xlUp).row
Cells(c, mc) = ""
For i = mc + 1 To Cells(c, Columns.Count).End(xlToLeft).Column
If Cells(c, i) < "" Then
Cells(c, mc) = Cells(c, mc) & "," & Cells(c, i)
End If
Next i
Cells(c, mc) = "'" & Right(Cells(c, mc), Len(Cells(c, mc)) - 1)
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LM" wrote in message
...
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.