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
<(E-Mail Removed)> 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 <(E-Mail Removed)>
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 <3107B997-6540-47BF-81D1-(E-Mail Removed)>, Toppers
<(E-Mail Removed)> 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
>>

HI All

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

I'll do it manually ....

^_^

Best Wishes


Colin




In article <(E-Mail Removed)>, 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 <(E-Mail Removed)
>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 <(E-Mail Removed)>
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 <(E-Mail Removed)>, 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 <(E-Mail Removed)
>>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 <(E-Mail Removed)>
> 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 <(E-Mail Removed)>, 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 <(E-Mail Removed)>
>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 <(E-Mail Removed)>, 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 <(E-Mail Removed)
>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

Colin

Apologies for the misunderstanding.

I saw a post from you asking Toppers for instructions on how to implement the
Function and my assumer got stuck.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the ConCatRange function code in there.

Save the workbook and hit ALT + Q to return to your workbook.

Enter the formula =ConCatRange(A1:A20) in B1


Gord

On Fri, 14 Jul 2006 14:24:40 +0100, Colin Hayes <(E-Mail Removed)>
wrote:

>In article <(E-Mail Removed)>, 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 <(E-Mail Removed)>
>>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 <(E-Mail Removed)>, 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 <(E-Mail Removed)
>>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

Gord Dibben MS Excel MVP