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
<Colin@chayes.demon.co.uk> 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 <Toppers@discussions.microsoft.com>
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 <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, 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 <Toppers@discussions.microsoft.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
>>>
>>>
>
Your choice.
Have fun.
Gord
On Fri, 14 Jul 2006 03:47:40 +0100, Colin Hayes <Colin@chayes.demon.co.uk>
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 <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, 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 <Toppers@discussions.microsoft.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
In article <5o5eb29l7bki7a0778mbe6bnonca5rbd1f@4ax.com>, 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 <Colin@chayes.demon.co.uk>
>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 <eqidb2hjmtvp363a8ncpunpgt5gprh2kd6@4ax.com>, 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 <Toppers@discussions.microsof
>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 <Toppers@discussions.microsoft.com>
> 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-3FFA1F84CE7B@microsoft.com>, Toppers
<Toppers@discussions.microsoft.com> 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
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks