Concatenate multiple rows and columns into 1 cell

M

mj44

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.
 
P

PY & Associates

You are aware that a cell can take in limited number of characters. Any more
than that number, the result will be truncated please.
 
B

Bryan Hessey

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.
 
P

PY & Associates

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" <[email protected]>
wrote in message
 
B

Bryan Hessey

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,C3
E4 will then =
A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C3A1,B1,C1,A2,B2,C2,A3,B3,C3,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
 
P

PY & Associates

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" <[email protected]>
wrote in message
 
B

Bryan Hessey

In the help, search for Limit and look up
Excel specifications and limits
Worksheet and workbook specifications
which includes:

Length of cell contents (text) 32,767 characters. Only 1,024 displa
in a cell; all 32,767 display in the formula bar.

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
<[email protected]>
wrote in message
Click to expand...
 
P

PY & Associates

We knew about 1,024 but didn't border to dig up 32,767.
Thank you for your advice.

"Bryan Hessey" <[email protected]>
wrote in message
In the help, search for Limit and look up
Excel specifications and limits
Worksheet and workbook specifications
which includes:

Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar.
Click to expand...
 
B

Bryan Hessey

Also, the 1024 is limited by 255 character column width, and 409 point
of row height (about 550 pixels).
The only way I know to view all text in a 'full' cell is via 3
following helper rows which use =mid(a1,1024,1024) where the mid figur
increments by 1024 =mid(a1,2048,1024) etc until 31744 or no furthe
data, but I don't think this is a wise use of Excel and the text woul
be more appropriately located in MS Word.

Screen shots of the formula bar (press the PrtSc or PrintScreen button
pasted via Ctrl/V into MS Word, trimmed via the Crop tool, and adjuste
to readable size are time comsuming to say the least, but how else doe
one get a hard copy.

Cheers.

We knew about 1,024 but didn't border to dig up 32,767.
Thank you for your advice.

"Bryan Hessey
<[email protected]>
wrote in message
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron
 
P

PY & Associates

After you clear advice, we suggest to enlist assistance of len function,
inserting breaks once it exceed a set number of characters. Certainly need
adjustment to len of the cell immediately following the blank line please.

"Bryan Hessey" <[email protected]>
wrote in message
Also, the 1024 is limited by 255 character column width, and 409 points
of row height (about 550 pixels).
The only way I know to view all text in a 'full' cell is via 31
following helper rows which use =mid(a1,1024,1024) where the mid figure
increments by 1024 =mid(a1,2048,1024) etc until 31744 or no further
data, but I don't think this is a wise use of Excel and the text would
be more appropriately located in MS Word.

Screen shots of the formula bar (press the PrtSc or PrintScreen button)
pasted via Ctrl/V into MS Word, trimmed via the Crop tool, and adjusted
to readable size are time comsuming to say the least, but how else does
one get a hard copy.

Cheers.
Click to expand...
 
M

mj44

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?
 
R

Ron Rosenfeld

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?
Click to expand...

Only if you did not download and install morefunc.xll.


--ron
 
S

swatsp0p

Per Ron's original post, download the add-in here:

Download and install Longre's free morefunc.xll add-in from
 
R

Ron Rosenfeld

I have not done that. Do you know where and how I can find and install
morefunc.xll?
Click to expand...


What happened when you tried the method I outlined in my first response to your
request?


--ron
 
M

mj44

Thank you all for your responses. I added the morefunc.xll and Ron, your
suggestion for using =mconcat(A1:C500) worked great.
 
R

Ron Rosenfeld

Thank you all for your responses. I added the morefunc.xll and Ron, your
suggestion for using =mconcat(A1:C500) worked great.
Click to expand...

Well, I'm glad it worked out for you. It sure has been nice the Longre wrote
all those functions -- saves the rest of us a lot of aggravation.


--ron
 
B

Bonobo

I was also interested in using the MCONCAT formula your giving below but have
the following error message when accessing the web site:
"You don't have permission to access / on this server."

Would you know why?
Do you know if it is available from another web site?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to have data in one column 1
Development combinations with exclusion of columns 0
Looking to added up based on a character from cell range 1
VBA Coding Help for Beginner 0
Concatenate when pasting. 0
Protecting user from changing previous data 5
Macro to concatenate columns 3
Development combinations with exclusion of columns 0

Top