concantenate cells in multiple row

shilabrow · #1 03-24-2015, 09:13 AM

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 · #2 03-24-2015, 10:49 AM

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

shilabrow · #3 03-24-2015, 01:06 PM

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

Snakehips · #4 03-24-2015, 01:51 PM

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 · #5 03-24-2015, 05:48 PM

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 · #6 03-25-2015, 12:54 AM

Quote:

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 · #7 03-25-2015, 10:22 AM

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.

shilabrow · #8 03-26-2015, 06:42 PM

Thanks Snakehips, works. Appreciate all the help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Get multiple cells in separate worksheet	kerkstrt	Excel	1	10-26-2014 11:20 PM
Link multiple cells in drop-down lists	Trial4life	Excel	0	09-08-2014 05:08 AM
Counting cells with multiple complex criteria	TishyMouse	Excel	12	12-06-2012 05:05 AM
How to merge multiple records into one and update the cells	mag	Excel	1	10-30-2012 01:11 AM
Multiple VLOOKUP's checking multiple Cells	OTPM	Excel	11	05-23-2011 11:18 AM