Join value of rows in ColumnA

[maramiro]

Hello
I have a problem with excel, I would really appreciate your help please.
I have 4000 rows and 36 columns used.
I would like to join 4000 rows of column A together, separating each value by a comma (,) , if I use the concatenate function,it would take me forever to do so (ex : =concatenate (A4,",",A5, ",",A6,....A4000)). Is there any other solution to join all rows of column A by using a faster way than the concatenate function.

Thank you

 

[Sal Paradise]

There is a text limit for a single cell, and 4000 cells will almost certainly pass well beyond it.

ETA: up to 1024 characters can be displayed in a cell, 32767 can be displayed in the formula bar.

ETA2: Over 32767 characters and you will get a #VALUE! error.

 

[maramiro]

I have basically 4000 rows in the excel worksheet, and I would like to read the values inside each cell of Column A, seperating each value by a comma.
The following is a small example of what I need.
orange is column A, I want to copy/ paset the values in a cell so that the result appears to me like that : 0,5,1,2

But what I actually have is 4000 rows and not only 5 rows like the example shown below. Should I write like a VBA code, or there is a function that could do so to me

<table style="border-collapse: collapse; width: 96pt;" border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Orange </td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Yellow</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" align="right" height="20">0</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" align="right" height="20">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">7</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" align="right" height="20">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" align="right" height="20">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

Sorry I m pretty new in these things
Thank you

 

[Legacy 68668]

try
=JoinRows(A1:A4000,",")

Function JoinRows(ByRef rng As Range, ByVal JoinStr As String) As String
    JoinRows = Join(Application.Transpose(rng.Value), JoinStr)
End function

 

[Sal Paradise]

Again, there is a limit to how long the text can be and how much text a cell can display. If your text is like the example (1 character in each cell) then you can fit it all in a single cell with commas with no errors. But if you have an average of 3-4 characters per cell, you will just get a #VALUE! error. Additionally, depending on how you want to use the concatenated cells, you may run into other problems down the line.

Give Seiya's VBA code a try, but realize that Excel does have cell length limitations.

 

[maramiro]

hey Seiya,
when i actually write in the function bar =joinrows(A1:A4000,","),
I get an error in the cell saying #NAME?
Thank you

 

[Legacy 68668]

hey Seiya,
when i actually write in the function bar =joinrows(A1:A4000,","),
I get an error in the cell saying #NAME?
Thank you

1) Hit Alt + F11
2) go to [Insert] - [Module] then paste code
3) hit Alt + F11 again to get back to excel

Now you can use that function in the cell.

 

[maramiro]

THANKS A LOT SEIYA
It's working now