Concatenate thousands of cells with a comma between each one?
I have over 6,000 cells in a column that I need to join together with a comma (no spaces) between each one. Any easy ways of doing this?
I tried following these instructions, but it tells me that I am over the character limit once I press F9.
Select cell B2.
Type =A1:A6282&", " in formula bar.
Press F9.
Delete curly brackets in formula bar.
Delete last delimiting character.
Type =Concatenate( in front of all characters in formula bar.
Type ) after last character in formula bar.
Press Enter
Cell character limit is 32K so (not knowing the length of each cell) I guess you are over that.
MetroBOS,
I've used the method outlined here several times. http://chandoo.org/wp/2014/01/13/com...ues-quick-tip/
I think it should help you as well (it even gives you an alternate to using F9.
MetroBOS,
Do you have a solution...? If so, please mark this as Solved. If not, can you better explain your problem?
Do you mean that you are trying to take data from each cell in Column A1 (xlDown), then 'transpose' that entire column of data such that each value is joined with each 'other' value (the next cell down) with a comma?
Something like this;
A1 | A2 | A3
Val1
Val2
Val3
Val4
....changed to this;
Val1,Val2,Val3,Val4........Valn
If so, you can simply use Concatenate in a rigid formula such as this;
In say Cell A2, type
=A1&","&A2&","&A3&","&A4&"," etc. etc. to &An&
Similarly, you can record a macro to achieve the same thing if you really have 6000 items in a coulmn to do.
H.
--If You Can't Measure It, You Can't Manage It--
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks