How to transpose and concatenate long list in one column?

  • Thread starter Thread starter CSchwass
  • Start date Start date
C

CSchwass

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---
 
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
 
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
 
Gord,

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

Thanks,
Chris
 
With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-click>Insert>Module

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord
 
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
 
Works perfectly, thanks!

Gord Dibben said:
With your workbook currently open.................

Alt + F11 to open VBE

CTRL + r to open Project Explorer

Select your workbook/project and Right-click>Insert>Module

Paste the UDF into that module.

Alt + q to return to the Excel window.

Enter the formula into a sheet cell.


Gord
 
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.
 
Trying to avoid macros if possible.

Jacob Skaria said:
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
 
Back
Top