ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Concatenating and transposing a row of numbers (https://www.excelbanter.com/excel-worksheet-functions/99205-concatenating-transposing-row-numbers.html)

Colin Hayes
Concatenating and transposing a row of numbers
 

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

Richard Buttrey
Concatenating and transposing a row of numbers
 
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
__________________________

Toppers
Concatenating and transposing a row of numbers
 


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
Concatenating and transposing a row of numbers
 
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

Colin Hayes
Concatenating and transposing a row of numbers
 
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

Colin Hayes
Concatenating and transposing a row of numbers
 

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
Concatenating and transposing a row of numbers
 
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

Toppers
Concatenating and transposing a row of numbers
 
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

Colin Hayes
Concatenating and transposing a row of numbers
 
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

Gord Dibben
Concatenating and transposing a row of numbers
 
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 InsertModule. 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
wrote:

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

Gord Dibben MS Excel MVP

Colin Hayes
Concatenating and transposing a row of numbers
 


Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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 InsertModule. 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
wrote:

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
of
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

Colin Hayes
Concatenating and transposing a row of numbers
 

HI Gord


OK I've entered and saved this function into the code in my workbook :

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



and entered the formula

=ConCatRange(A1:A20) in B1


I have my numbers down the sheet in column A from A1 to A20.


I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?


The VBA editor does seem to be giving an error in this phrase :

If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","

It goes green after the word 'text'. If i change any part of this I get
red errors....


Thanks again.



Best Wishes


Colin








In article , Colin Hayes
writes


Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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 InsertModule. 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

wrote:

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

of
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

Gord Dibben
Concatenating and transposing a row of numbers
 
Colin

The green part in the line is caused by the two single quotes I put in telling
Excel to ignore that part.

Another poster did not want the commas so I remmed that part out and sent you
the same code by mistake.

You don't want that part ignored, so remove them to this.

If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","

That is not the cause of the #NAME? error however.

That error usually comes from Excel not recognizing the function name.

Did you Copy/paste to a General Module in the same workbook?

If you click on the Fx button and select User Defined from the list, does
ConCatRange show up?


Gord

On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes
wrote:


HI Gord


OK I've entered and saved this function into the code in my workbook :

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



and entered the formula

=ConCatRange(A1:A20) in B1


I have my numbers down the sheet in column A from A1 to A20.


I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?


The VBA editor does seem to be giving an error in this phrase :

If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","

It goes green after the word 'text'. If i change any part of this I get
red errors....


Thanks again.



Best Wishes


Colin








In article , Colin Hayes
writes


Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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 InsertModule. 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

wrote:

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

of
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

Colin Hayes
Concatenating and transposing a row of numbers
 

Hi Gord

Ok thanks for getting back.

I went through the process again , and this time it worked fine. i must
just have made an error when I first did it. Anyway all is well now and
working perfectly. Thanks!

BTW Once saved as a module in a project , does the coding that we've
added become embedded in the sheet itself? If it I ran it on another
machine , would it run independently? Or would i need to put the code in
again on another PC?



Best Wishes


Colin







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

The green part in the line is caused by the two single quotes I put in telling
Excel to ignore that part.

Another poster did not want the commas so I remmed that part out and sent you
the same code by mistake.

You don't want that part ignored, so remove them to this.

If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","

That is not the cause of the #NAME? error however.

That error usually comes from Excel not recognizing the function name.

Did you Copy/paste to a General Module in the same workbook?

If you click on the Fx button and select User Defined from the list, does
ConCatRange show up?


Gord

On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes
wrote:


HI Gord


OK I've entered and saved this function into the code in my workbook :

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



and entered the formula

=ConCatRange(A1:A20) in B1


I have my numbers down the sheet in column A from A1 to A20.


I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?


The VBA editor does seem to be giving an error in this phrase :

If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","

It goes green after the word 'text'. If i change any part of this I get
red errors....


Thanks again.



Best Wishes


Colin








In article , Colin Hayes
writes


Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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 InsertModule. 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

wrote:

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

of
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

Gord Dibben
Concatenating and transposing a row of numbers
 
Colin

The Function would be available to other workbooks only if the original workbook
was open.

That's why most users will place their macros and UDF's in their Personal.xls
workbook which opens hidden with each session of Excel.

I prefer to keep mine in an Add-in which I load through ToolsAdd-ins.

You may or may not have a Personal.xls depending upon whether you have recorded
macros in the past.

To create one...................

Have any workbook open.

ToolsMacroRecord New Macro.

Store in "Personal Macro Workbook"

Record yourself copying a cell and pasting it somewhere.

Stop Recording.

Hit ALT + F11 to go to VBE and you will see a Personal.xls project.

Expand and d-click on Module1

Copy the ConCatRange UDF into that module.

Delete the recorded copy a cell macro lines.

Close the module and hit ALT + Q to go to Excel.

Go to Window and Select Personal.xls and "Hide"

Close Excel and click Yes when asked to save changes to Personal.xls.

Re-open Excel and Personal.xls will open hidden with the UDF and any other
macros you store in there.

The only change you must make is to add to your formula

Personal.xls!ConCatRange(A1:A20)

If you store your macros and UDF's in an Add-in you don't have to preface with a
workbook name.


Gord

On Sat, 15 Jul 2006 20:27:48 +0100, Colin Hayes
wrote:


Hi Gord

Ok thanks for getting back.

I went through the process again , and this time it worked fine. i must
just have made an error when I first did it. Anyway all is well now and
working perfectly. Thanks!

BTW Once saved as a module in a project , does the coding that we've
added become embedded in the sheet itself? If it I ran it on another
machine , would it run independently? Or would i need to put the code in
again on another PC?



Best Wishes


Colin







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

The green part in the line is caused by the two single quotes I put in telling
Excel to ignore that part.

Another poster did not want the commas so I remmed that part out and sent you
the same code by mistake.

You don't want that part ignored, so remove them to this.

If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","

That is not the cause of the #NAME? error however.

That error usually comes from Excel not recognizing the function name.

Did you Copy/paste to a General Module in the same workbook?

If you click on the Fx button and select User Defined from the list, does
ConCatRange show up?


Gord

On Sat, 15 Jul 2006 17:45:24 +0100, Colin Hayes
wrote:


HI Gord


OK I've entered and saved this function into the code in my workbook :

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



and entered the formula

=ConCatRange(A1:A20) in B1


I have my numbers down the sheet in column A from A1 to A20.


I'm getting the error #NAME? In B1. I've checked all the coding and
formula and all does look well. Any ideas where it might be going wrong?


The VBA editor does seem to be giving an error in this phrase :

If Len(cell.text) 0 Then sbuf = sbuf & cell.text '' & ","

It goes green after the word 'text'. If i change any part of this I get
red errors....


Thanks again.



Best Wishes


Colin








In article , Colin Hayes
writes


Hi Gord

OK Thanks for that. Very helpful.



Best Wishes


Colin





In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes
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 InsertModule. 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

wrote:

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

of
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


All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com