Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-24-2015, 09:13 AM
shilabrow shilabrow is offline concantenate cells in multiple row Windows Vista concantenate cells in multiple row Office 2007
Advanced Beginner
concantenate cells in multiple row
 
Join Date: Apr 2014
Posts: 45
shilabrow is on a distinguished road
Default concantenate cells in multiple row


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
Reply With Quote
  #2  
Old 03-24-2015, 10:49 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline concantenate cells in multiple row Windows 7 64bit concantenate cells in multiple row Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

The number of characters per cell is limited to 256 I think
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 03-24-2015, 01:06 PM
shilabrow shilabrow is offline concantenate cells in multiple row Windows Vista concantenate cells in multiple row Office 2007
Advanced Beginner
concantenate cells in multiple row
 
Join Date: Apr 2014
Posts: 45
shilabrow is on a distinguished road
Default

So are you saying its impossible to concatenate using VBA even if it outputs to Word document? All help appreciated on this. Thanks
Reply With Quote
  #4  
Old 03-24-2015, 01:51 PM
Snakehips Snakehips is offline concantenate cells in multiple row Windows 8 concantenate cells in multiple row Office 2013
Advanced Beginner
 
Join Date: Mar 2015
Posts: 36
Snakehips is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 03-24-2015, 05:48 PM
shilabrow shilabrow is offline concantenate cells in multiple row Windows Vista concantenate cells in multiple row Office 2007
Advanced Beginner
concantenate cells in multiple row
 
Join Date: Apr 2014
Posts: 45
shilabrow is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 03-25-2015, 12:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline concantenate cells in multiple row Windows 7 64bit concantenate cells in multiple row Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by shilabrow View Post
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.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 03-25-2015, 10:22 AM
Snakehips Snakehips is offline concantenate cells in multiple row Windows 8 concantenate cells in multiple row 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
  #8  
Old 03-26-2015, 06:42 PM
shilabrow shilabrow is offline concantenate cells in multiple row Windows Vista concantenate cells in multiple row Office 2007
Advanced Beginner
concantenate cells in multiple row
 
Join Date: Apr 2014
Posts: 45
shilabrow is on a distinguished road
Default

Thanks Snakehips, works. Appreciate all the help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
concantenate cells in multiple row 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
concantenate cells in multiple row How to merge multiple records into one and update the cells mag Excel 1 10-30-2012 01:11 AM
concantenate cells in multiple row Multiple VLOOKUP's checking multiple Cells OTPM Excel 11 05-23-2011 11:18 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:27 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft