View Single Post
 
Old 03-25-2015, 10:22 AM
Snakehips Snakehips is offline Windows 8 Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

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.

Code:
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.
Reply With Quote