concantenate cells in multiple row

[shilabrow]

I have about 120,000 of rows with entries in A column . I want to combine all entries in Column A into one cell seperating them by semicolon but l get timed out or excel doesnt capture all in one cell. I need help in getting this done even if Vba. Any and all help will be appreciated.



Column A
1. 0088900
2. 0089334
3. 0008994
4. Engen and Sons
Until
120000. 0995789

Output = 0088900;0089334;0008994;Engen and Sons;.............until;0995789

All help appeciated

[Pecoflyer]

The number of characters per cell is limited to 256 I think

[shilabrow]

So are you saying its impossible to concatenate using VBA even if it outputs to Word document? All help appreciated on this. Thanks

[Snakehips]

shilabrow,

Does it help if you Save As a CSV file ?

In addition to Excel hat can then be opened in the likes of Notepad, WordPad, Word etc.


Hope that helps.

[shilabrow]

Thanks Snakehips, I thought of that as well but when I tried it - it didn't give me the result as CSV. Am I saving it wrong or what? I just get a list with no comma or semicolon.

Is there a way to set a comma delimited option to it. Thanks so much for your help, much appreciated.

[Pecoflyer]

Originally Posted by shilabrow

So are you saying its impossible to concatenate using VBA even if it outputs to Word document? All help appreciated on this. Thanks

Read my post and your question. There was never any indication of using Word.

[Snakehips]

If you are using Excel 2007 + then you can have 32,767 characters per cell although Excel cannot display that many characters.

Assuming your data starts A1 and column B is available then the following will concatenate column A data, separated by a semi-colon, in cells B1:B??. Each cell will hold approx 32000 characters.

Right click your sheet tab >> View Code and paste the below into the code pane.

Sub con()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("B1")

For r = 1 To lr
Rng.Value = Rng.Value & ";" & Range("A" & r).Value
If Len(Rng.Value) > 32000 Then Set Rng = Rng.Offset(1, 0)
Next r
End Sub

I processed 120000 rows in under 1 minute.

When done, select the range B1:B?? Copy and paste into a word document.

Hope that helps.

[shilabrow]

Thanks Snakehips, works. Appreciate all the help.