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.

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.

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

....then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter


"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.

On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula


Unfortunately, that link has been down for a number of weeks for me. Are you
able to access it?
--ron

Thanks to both of you. I will try your suggestions tomorrow.

In the meantime, I tried SUBSTITUTE and that seemed to substitute commas for
my spaces and gave me the result I wanted. I am sure I had tried this before
and it didn't work but obviously I did something wrong the first time.

Thanks again.



"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter


"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.

No, I can't access it either. I receive an error message - and it's
certainly not in English!!

"Ron Rosenfeld" wrote:

On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula


Unfortunately, that link has been down for a number of weeks for me. Are you
able to access it?
--ron

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.

On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter

Oh, if he can get morefunc, I think this would work as well, and be shorter:

=SUBSTITUTE(TRIM(MCONCAT(C2:Z2," "))," ",",")
--ron

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.

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.

dear lyn

try this
=SUBSTITUTE(A2&","&B2&","&C2&","&D2&","&E2&","&F2& ","&G2 upto 25th column
,",,",",")




"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.

Perhaps this would help?

http://www.download.com/Morefunc/300...-10423159.html