Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

On Thu, 13 Jul 2006 20:51:02 +0100, Colin Hayes
wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

With your numbers in say A1:A10, enter in
B1 =A1, and in
B2 =B1&","&A2

Now copy B2 down ro B10.

B10 now contains your concatenaated numbers. To convert them to a long
text string do Edit F9 and Enter.

Now copy B10 to B1 and delete B2:B10

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Try this macro:

Put this required cell;

=onelist(A1:A100) .

... set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

HI All

I'm pleased you were able to sort that out between yourselves...!

I'll do it manually ....

^_^

Best Wishes


Colin




In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
m
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

Your choice.

Have fun.

Gord

On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
wrote:


HI All

I'm pleased you were able to sort that out between yourselves...!

I'll do it manually ....

^_^

Best Wishes


Colin




In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
m
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes




Gord Dibben MS Excel MVP

In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Your choice.

Have fun.

Gord

HI Gord

No , not my choice at all. Quite the opposite in fact. I spent some time
trying to implement the code , and wasn't able to.

I'm grateful for any advice given of course , but we're not all experts.
MVPs have a role as instructor , don't they?


Best Wishes


Colin



On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes
wrote:


HI All

I'm pleased you were able to sort that out between yourselves...!

I'll do it manually ....

^_^

Best Wishes


Colin




In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
t.
co
m
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes




Gord Dibben MS Excel MVP

Thanks Gord ... I appreciate the feedback!!!

"Gord Dibben" wrote:

Toppers

One problem with this.

If a cell in the range is blank it returns a 0

So you get 1,2,3,0,0,0,7,8,9 if A4:A6 are blank.

This function ignores blanks.

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


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 14:01:02 -0700, Toppers
wrote:



Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function

"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes

In article , Toppers
writes


Try this macro:

Put this required cell;

=onelist(A1:A100) .

.. set range as required


Function onelist(ByRef rng As range) as string
bStr = ""
For Each cell In rng
bStr = bStr & Trim(Str(cell.Value)) & ","
Next
onelist = Left(bStr, Len(bStr) - 1)
End Function



Hi

OK Thanks for that.

I wasn't able to implement it , I'm afraid. I can run macros OK , but I
think you're expecting more programming skill than I possess!

(For example , when you say

Put this required cell;

=onelist(A1:A100) .

.. set range as required

What do you mean? How do I do this?)



Best Wishes





"Colin Hayes" wrote:


Hi All

Hope someone can help.

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

I've been playing around for ages to do this - can someone put me out of
my misery?



Best Wishes