Sorry, I looked at your post again and admit I have missed D1 as in
=D1&","&A2&","&B2&","&C2

My suggestion is therefore redundant.

For my curiosity, where is the 32,767 characters come from please?

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1ydc3m_1131773100.8989@excelforu m-nospam.com...

OK - I guess that beats me, as
E1 will then = A1,B1,C1
E2 will then = A1,B1,C1A1,B1,C1,A2,B2,C2
E3 will then = A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3
E4 will then =

A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3A1,B1,C1,A2,B2,C2,A3,B3,C
3,A4,B4,C4


and the progression gets increasingly further from what the OP
requested.
It is (almost) possible to visually inspect, but of 32,767 characters
only 1024 are displayed in the row, the full set is displayed only in
the formula bar.

A better suggestion for checking might be that E1 be

=Right(D1,40)

and formula copy that to the end of data rows, at the point of
overflow the E column ceases changing and displays the same characters
32,728 to 32,767



PY & Associates Wrote:
Using this hint, we suggest copy D1 to E1
put E2=E1 & D2
copy all the way down
we can now visually inspect where E# starts to truncate data and
adjust
accordingly.

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1yd7gy_1131767101.0139@excelforu m-nospam.com...

Presuming that you need comma seperators, in D1 put

=A1&","&B1&","&C1

and in D2 put

=D1&","&A2&","&B2&","&C2

and formula-drag that to the end of your data.

in the next cell I suggest you put

=mid(D9999,32700,1024)

to display the last 68 characters of the permitted 32767 limit, if
you
have data here you have probably exceeded the limit, backtrack up
the
data and decide where to re-start the formula by removing the
Dnn&","
from the start.
You may need multiple sets if you have more than 32,000 characters.
note, the D9999 reference means the previous cell, ie the last one
filled with data.

After you have the data collated, you might want to Copy and Paste
Special = Values for the final cell(s) to preserve the data.



mj44 Wrote:
I have a spreadsheet using 3 columns and n number of rows worth of
data.
I
am trying to create one long string of this data by concatenating
in
the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually
click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=484382



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382