|
Concatenate multiple rows and columns into 1 cell
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. |
Concatenate multiple rows and columns into 1 cell
You are aware that a cell can take in limited number of characters. Any more
than that number, the result will be truncated please. "mj44" wrote in message ... 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. |
Concatenate multiple rows and columns into 1 cell
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 |
Concatenate multiple rows and columns into 1 cell
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 |
Concatenate multiple rows and columns into 1 cell
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,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 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 |
Concatenate multiple rows and columns into 1 cell
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 |
Concatenate multiple rows and columns into 1 cell
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. PY & Associates Wrote:[color=blue] 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 -- 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 |
Concatenate multiple rows and columns into 1 cell
We knew about 1,024 but didn't border to dig up 32,767.
Thank you for your advice. "Bryan Hessey" wrote in message news:Bryan.Hessey.1ydq7z_1131791401.8066@excelforu m-nospam.com... 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. PY & Associates Wrote: 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[color=blue] 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 -- 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 |
Concatenate multiple rows and columns into 1 cell
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. PY & Associates Wrote:[color=blue] We knew about 1,024 but didn't border to dig up 32,767. Thank you for your advice. "Bryan Hessey" wrote in message news:Bryan.Hessey.1ydq7z_1131791401.8066@excelforu m-nospam.com... 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. PY & Associates Wrote: 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 -- 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 |
Concatenate multiple rows and columns into 1 cell
On Fri, 11 Nov 2005 11:56:07 -0800, "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. 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 |
Concatenate multiple rows and columns into 1 cell
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" wrote in message news:Bryan.Hessey.1ydsja_1131794401.6728@excelforu m-nospam.com... 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. PY & Associates Wrote: We knew about 1,024 but didn't border to dig up 32,767. Thank you for your advice. "Bryan Hessey" wrote in message news:Bryan.Hessey.1ydq7z_1131791401.8066@excelforu m-nospam.com... 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. PY & Associates Wrote: 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[color=blue] 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 -- 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 |
Concatenate multiple rows and columns into 1 cell
Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get this as a result? -- Thank you, mj44 "Ron Rosenfeld" wrote: On Fri, 11 Nov 2005 11:56:07 -0800, "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. 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 |
Concatenate multiple rows and columns into 1 cell
On Mon, 14 Nov 2005 05:37:08 -0800, "mj44"
wrote: Ron, When I try using =mconcat(a1:c500), my result is #NAME? Why would I get this as a result? Only if you did not download and install morefunc.xll. --ron |
Concatenate multiple rows and columns into 1 cell
I have not done that. Do you know where and how I can find and install
morefunc.xll? -- Thank you, mj44 "Ron Rosenfeld" wrote: On Mon, 14 Nov 2005 05:37:08 -0800, "mj44" wrote: Ron, When I try using =mconcat(a1:c500), my result is #NAME? Why would I get this as a result? Only if you did not download and install morefunc.xll. --ron |
Concatenate multiple rows and columns into 1 cell
Per Ron's original post, download the add-in he Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=484382 |
Concatenate multiple rows and columns into 1 cell
From Ron's post:
Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ mj44 wrote: I have not done that. Do you know where and how I can find and install morefunc.xll? -- Thank you, mj44 "Ron Rosenfeld" wrote: On Mon, 14 Nov 2005 05:37:08 -0800, "mj44" wrote: Ron, When I try using =mconcat(a1:c500), my result is #NAME? Why would I get this as a result? Only if you did not download and install morefunc.xll. --ron -- Dave Peterson |
Concatenate multiple rows and columns into 1 cell
On Mon, 14 Nov 2005 05:51:02 -0800, "mj44"
wrote: I have not done that. Do you know where and how I can find and install morefunc.xll? What happened when you tried the method I outlined in my first response to your request? --ron |
Concatenate multiple rows and columns into 1 cell
Thank you all for your responses. I added the morefunc.xll and Ron, your
suggestion for using =mconcat(A1:C500) worked great. -- Thank you, mj44 "Ron Rosenfeld" wrote: On Mon, 14 Nov 2005 05:51:02 -0800, "mj44" wrote: I have not done that. Do you know where and how I can find and install morefunc.xll? What happened when you tried the method I outlined in my first response to your request? --ron |
Concatenate multiple rows and columns into 1 cell
On Mon, 14 Nov 2005 10:30:10 -0800, "mj44"
wrote: Thank you all for your responses. I added the morefunc.xll and Ron, your suggestion for using =mconcat(A1:C500) worked great. 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 |
Concatenate multiple rows and columns into 1 cell
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 "Ron Rosenfeld" wrote: On Fri, 11 Nov 2005 11:56:07 -0800, "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. 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 |
Concatenate multiple rows and columns into 1 cell
Use this UDF which will do the same thing.
Will leave a space between data from each cell. Ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =ConCatRange(A1:C100) Or a macro which leaves no formulas to deal with. Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells..Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Also ignores blank cells. Gord Dibben MS Excel MVP On Wed, 18 Jul 2007 08:18:02 -0700, Bonobo wrote: 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 "Ron Rosenfeld" wrote: On Fri, 11 Nov 2005 11:56:07 -0800, "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. 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 |
Concatenate multiple rows and columns into 1 cell
The function works perfectly.
Thanks! "Gord Dibben" wrote: Use this UDF which will do the same thing. Will leave a space between data from each cell. Ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " " Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function =ConCatRange(A1:C100) Or a macro which leaves no formulas to deal with. Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells..Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Also ignores blank cells. Gord Dibben MS Excel MVP On Wed, 18 Jul 2007 08:18:02 -0700, Bonobo wrote: 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 "Ron Rosenfeld" wrote: On Fri, 11 Nov 2005 11:56:07 -0800, "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. 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 |
| All times are GMT +1. The time now is 10:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com