Concatenate a Large Range of Cells

Question

Level 1

0 points

Concatenate a Large Range of Cells

I have made a scaled down proof of concept for an end result of "Table 1."

"Table 2" presents no problems as it is the data entry table and I already have the data. It is just a copy and paste.

I can auto fill the formulas for "Table 3" in four bites.

It is the formula used in "Table 1" that concerns me. From searching through the forums I gather there is no way to concatenate an entire range, for example I cannot use:

=CONCATENATE(C2:C6)

in cell B2 of "Table 1".

The number of items in each concatenate string in each cell of "Table 1" is too great to be practical in full scale. Is there a more elegant formula for the cells in "Table 1" than each cell having a concatenate formula with 250 strings/items?

I hope I have explained the problem clearly. Thank you for your time.

Posted on Feb 4, 2012 3:07 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Feb 4, 2012 7:02 PM in response to rawpaw

Neither the CONCATENATE function nor the equivalent concatenation operator ( & ) will accept a range.

But you have another issue that's going to be a brick wall for your proposed Table 3.

Numbers is not designed for large tables. The maximum number of columns in a Numbers table is 255 (or 256).

Can that table be split into two smaller tables, or transposed to become a 250 column by 450 row table?

Regards,

Barry

Reply

Answer 2

rawpaw Author

Level 1

0 points

Feb 4, 2012 7:31 PM in response to Barry

Barry--

Thanks for the heads up on the column limit. Table 3 has reasonable division points at the half and quarter points. So, it could be divided into two or four tables.

I've built some other Numbers documents that had a large quantity of monster formulas like this and they brought Numbers to a crawl when working with them. I'd prefer not to take a long time building something that is going to also take a lot of my time to use.

I was hoping there was a creative work around for the inability to CONCATENATE a range that was more efficient that the monster formula.

Reply

Answer 3

Barry

Level 9

60,023 points

Feb 4, 2012 11:11 PM in response to rawpaw

"I was hoping there was a creative work around for the inability to CONCATENATE a range that was more efficient that the monster formula."

More efficient to write, perhaps, but I doubt the shorter XXXX(B2:IV2) is faster that the longer version.

Any large Numbers table is going to be slow. That seems to be the nature of the beast.

Regards,

Barry

Reply

Answer 4

rawpaw Author

Level 1

0 points

Feb 5, 2012 9:43 AM in response to Barry

With some creative cross application work I was able to largely auto generate the large CONCATENATE formulas and the full scale project is built. I had to use single character names for the referenced sheets and tables so as not to exceed the formula character limit.

Not as slow to work with as I had feared.

Thanks.

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Feb 6, 2012 1:03 AM in response to rawpaw

Is the tables contents changing often ?

If it's not, using a script to concatenate the datas may be a valid answer.

Yvan KOENIG (VALLAURIS, France) lundi 6 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Reply

Answer 6

rawpaw Author

Level 1

0 points

Feb 6, 2012 3:52 AM in response to KOENIG Yvan

Yes. the data does change often. The long concatenate formula seems to be working reasonably well in the full scale project. While the solution is not elegant, it is working.

Reply

Answer 7

Jul 9, 2012 5:08 PM in response to rawpaw

Here is an answer that worked great for me. I found it here:

Concatenating a Range in Excel

You'll have to adapt it for your spreadsheet:

A	B	(result)
1	Apple
2	Baker	=A1&A2	AppleBaker
3	Charlie	=B2&C1	AppleBakerCharlie
4	Denver	=B3&C2	AppleBakerCharlieDenver
5	Edward	(etc.)

You just concatenate two cells together (using the '&' concatenation operator), and then in the next cell you just add on to the end of the result. So that by the end, you have them all in one long string.

You might say "But I have to write out all those formulas". No. As you know, you can just copy the formula to the next cell and Numbers will change the references for you.

Reply

Answer 8

Feb 26, 2016 2:49 AM in response to Andrew R1

Thanks a lot! Simple and effective. Genius!

Reply

Answer 9

Jan 17, 2017 10:25 PM in response to Andrew R1

Just used this, it's brilliant. Thank you for the suggestion!

Reply