I am trying to concatenate a large number of cells and finding excel is running into some sort of limit where it will only concatenate a limited number of the cells.
As an example...
=A1&A2&A3&A4.........&A97&A98.....
but I am finding it will only handle about the first 30 of them
Im just guessing as to the number, but you get the idea..
Does anyone know of a way to effect this concatenation in full?
many thanks
One way I can think of off top of my head, in B2: =A1&A2, then in b3: =B2&A3, drag/copy B3 down
Not sure if that will help you, but all I can think of atm
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
You could use a user-defined function (UDF) which can be given a range of cells to concatenate, rather than having to specify every individual cell...
So with this in a VB module in your workbook you could use:
Please Login or Register to view this content.
=RangeCon(A1:A98)
Andrew offered a nice solution here...
http://www.excelforum.com/excel-gene...-one-cell.html
Edit: Also did this, in this thread!
Last edited by Fotis1991; 01-25-2013 at 04:59 AM. Reason: Edit
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
The one in this thread is nicer, as you can specify delimiters and whether to skip/include blank cells
I should probably create a book of handy answers and save that one, rather than rewriting it from scratch each time, shouldn't I?
YES. You should do this.... should probably create a book of handy answers and save that one, rather than rewriting it from scratch each time, shouldn't I?
Ok...well..first..thank you all for your input, but unfortunately, the suggestions are not working. I attached an example of the issue.. the file only contains one line of data with the concatenation by module highlighted in green background in cell AU1.
But note that when you copy and paste this cost, it is only includes a small subset from the first cells in the defined range.
Any other thoughts??
One way I thought to do this was to break it up into 4 pieces of concatenation and then do a copy and paste special for each of those and then concatenate those, which will work... but its not very elegant.
I copied that cell and pasted it into Notepad and got this:
Which looks like a concatenation of the whole range.Please Login or Register to view this content.
You guys are awesome .. you are correct, it works perfectly...the issue was i was trying to paste into wordpad.... why wordpad?? when i paste into wordpad, i dont get the quotes surrounding my content, which i do in notepad. the good news?? I can paste into word and solve both issues... and now can move on with my project...thanks so much... [SOLVED]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks