I have a list like this in column A:

2345
1023
1492
2985
2902
etc

....but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

This requires code.

Function merge(r As Range) As String
merge = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
merge = merge & "," & rr.Value
End If
k = 2
Next
End Function

Call the function like this:
=merge(A1:A50) < -- in cell B1, for instance.

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"CSchwass" wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Thanks, didn't know how to create a UDF yet so I used another answer.

"ryguy7272" wrote:

This requires code.

Function merge(r As Range) As String
merge = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
merge = merge & "," & rr.Value
End If
k = 2
Next
End Function

Call the function like this:
=merge(A1:A50) < -- in cell B1, for instance.

HTH,
Ryan---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"CSchwass" wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

You dont need to loop. If you are looking for a UDF you can try the below.

=conct(A1:A20,",")

Function CONCT(varRange As Range, strDel As String) As String
CONCT = Join(WorksheetFunction.Transpose(varRange), strDel)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Gord Dibben" wrote:

With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

This won't work because it separates blank cells with a comma, so I would
have to update the range for the formula every time I add new values to the
column in question. Thanks tho.

"Jacob Skaria" wrote:

You dont need to loop. If you are looking for a UDF you can try the below.

=conct(A1:A20,",")

Function CONCT(varRange As Range, strDel As String) As String
CONCT = Join(WorksheetFunction.Transpose(varRange), strDel)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Gord Dibben" wrote:

With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Works perfectly, thanks!

"Gord Dibben" wrote:

With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Good to hear.

Thanks for the feedback.


Gord

On Thu, 10 Sep 2009 15:31:01 -0700, CSchwass
wrote:

Works perfectly, thanks!

"Gord Dibben" wrote:

With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-clickInsertModule

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord

On Wed, 9 Sep 2009 15:19:01 -0700, CSchwass
wrote:

Gord,

I am familiar with macros but not UDFs. How do I paste this to a "general
module"?

Thanks,
Chris

"Gord Dibben" wrote:

Copy/paste this UDF to a general module in your workbook.

Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
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

This UDF ignores blank cells so you can make the range quite large to
accomodate future entries in column A

=concatrange(A1:A100)

Please note: Excel will truncate the contents after about 1024 characters


Gord Dibben MS Excel MVP

On Wed, 9 Sep 2009 09:20:03 -0700, CSchwass
wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

You can try out the below macro.

Sub Macro()
Dim rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("B1") = Join(WorksheetFunction.Transpose(rngTemp), ",")
End Sub

If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


If this post helps click Yes
---------------
Jacob Skaria


"CSchwass" wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Trying to avoid macros if possible.

"Jacob Skaria" wrote:

You can try out the below macro.

Sub Macro()
Dim rngTemp As Range
Set rngTemp = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("B1") = Join(WorksheetFunction.Transpose(rngTemp), ",")
End Sub

If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()


If this post helps click Yes
---------------
Jacob Skaria


"CSchwass" wrote:

I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris

Hi,

Download and install the following addin
http://www.download.com/Morefunc/300...-10423159.html and then you may
use the mconcat(range,",") function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CSchwass" wrote in message
...
I have a list like this in column A:

2345
1023
1492
2985
2902
etc

...but much longer. I want this result in one cell:

2345,1023,1492,2985,2902,etc

I will continually add to the list in Column A and I don't want to update
the formula every time for the CSV cell.

How can I do this?

Thanks,
Chris