 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Merge multiple row data in one cell with delimieters
I have a single row of data, which will grow, which I need to merge into a long list of text in one cell, so I can paste into another program. I have started with the concantenate function, but I could do with a function to automatically go down the list until there is no more data. I think I have seen this done, but I can't remember how. TIA One way. Just make sure that there is an empty column between last data and the column to fill. If putting data in col G then col F should be blank Sub mergecolumndata() mc = "a" lr = Cells(Rows.Count, mc).End(xlUp).Row For Each c In Range(Cells(2, mc), Cells(lr, mc)) lc = Cells(c.Row, mc).End(xlToRight).Column + 1 mystr = "" For i = 1 To lc mystr = mystr & Cells(c.Row, i) & "," Next i cells(c.Row, "g") = Left(mystr, Len(mystr) - 2) Next c End Sub  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I have a single row of data, which will grow, which I need to merge into a > long list of text in one cell, so I can paste into another program. I [quoted text clipped - 4 lines] > seen this done, but I can't remember how. > TIA Thanks for the quick reply. I have added this macro to a shape, but it comes up with an error 1004 and highlights the mystr = mystr & Cells(c.Row, i) & "," line. Was I supposed to add in some information? At the moment all my data is in column a from row 1 to 29 if this helps. > One way. Just make sure that there is an empty column between last data and > the column to fill. If putting data in col G then col F should be blank [quoted text clipped - 20 lines] > > seen this done, but I can't remember how. > > TIA Since you didn't fully explain and you said "a single row of data", I assumed you had info in columns a,b,c or a,b,c,d etc. and wanted to string together into column G. A B C D E F G a b c BLANK a,b,c a b c d BLANK a,b,c,d a b c BLANK a,b,c a b c d ff BLANK a,b,c,d,ff SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thanks for the quick reply. > [quoted text clipped - 32 lines] >> > seen this done, but I can't remember how. >> > TIA Sorry Don, I knew what I wanted to say but it came across wrong. If I understand what you have written, can I substitute the the word column for row in certain lines, if not all. I haven't had a chance to try it yet, but will give it a go this evening I still do NOT understand your problem. Send you file to my address below if you like. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Sorry Don, > [quoted text clipped - 4 lines] > a > go this evening Don, I have emailed you the file Thanks > I still do NOT understand your problem. Send you file to my address below if > you like. [quoted text clipped - 7 lines] > > a > > go this evening Makes all of col A into one cell. Sub mergecolumndata() mc = "a" lr = Cells(Rows.Count, mc).End(xlUp).Row For Each c In Range(Cells(1, mc), Cells(lr, mc)) mystr = mystr & c & "," Next c 'MsgBox mystr Cells(1, mc).Offset(, 1).Value = Left(mystr, Len(mystr) - 1) End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Don, > [quoted text clipped - 16 lines] >> > a >> > go this evening Many Thanks Don, That worked a treat > Makes all of col A into one cell. > [quoted text clipped - 28 lines] > >> > a > >> > go this evening Glad to help SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Many Thanks Don, > [quoted text clipped - 35 lines] >> >> > a >> >> > go this evening |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.