Concatenate question on limitations
I'm trying to concatenate a list of "N" number of names. I'm aware of the 30
item limitation, but because of the N number of names, I thought I'd try something, Here's what I have: A B name 1 name 2 =concatenate(b1,b2,", ") name 3 =concatenate(b2,b3,", ") name 4 =concatenate(b3,b4,", ") etc, etc =concatenate(b(n-2),b(n-1),", ") name n =concatenate(b(n-1),bn,", ") and this works just fine. I've been able to concatenate up to 86 names successfully. What I ran into today baffles me. Today, I had 107 names. in the middle of the 87th name, it cut off and would not add any additional text. Example of the last several charachters... ~~~ Fred Smith, Joe Jones, Tom Sm The line above was repeated for all additional names in the list Where the last name displayed was Tom Smith, and other names followed. Fred Smith Joe Jones Tom Smith Fred Furrier etc etc My first thought was that I've run into some sort of text limitation. So I pasted the value of the last concatenation and got the length of it using the LEN function. The length was 1387. An awfully strange number to be a text limitation. Please don;t criticize the "inelegance" of my solution. I've had to adapt an existing computed list for a non-excel user to be able to copy and paste into a text document the final list of names and this seemed to be the simplest way to do it until I ran into this limitation. Any ideas what I've stumbled into? |
Concatenate question on limitations
What you have run up against is not a limitation of what you can enter in a
cell..............32767 characters. The limit is what is visible in the cell..................1024 characters. Since you are copy/pasting to a *.txt file you don't care about visibility in Excel. Use this UDF to concatenate a great number of cells...............no 30 arg limit. Function ConCatRange(CellBlock As Range) As String 'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5)) 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 Then copy and Paste SpecialValues in Excel. Copy to *.txt file. I just tested with a range of cells that returned 6000 characters. =concatrange(A1:F140) Gord Dibben MS Excel MVP On Wed, 4 Nov 2009 12:37:02 -0800, emcentee wrote: I'm trying to concatenate a list of "N" number of names. I'm aware of the 30 item limitation, but because of the N number of names, I thought I'd try something, Here's what I have: A B name 1 name 2 =concatenate(b1,b2,", ") name 3 =concatenate(b2,b3,", ") name 4 =concatenate(b3,b4,", ") etc, etc =concatenate(b(n-2),b(n-1),", ") name n =concatenate(b(n-1),bn,", ") and this works just fine. I've been able to concatenate up to 86 names successfully. What I ran into today baffles me. Today, I had 107 names. in the middle of the 87th name, it cut off and would not add any additional text. Example of the last several charachters... ~~~ Fred Smith, Joe Jones, Tom Sm The line above was repeated for all additional names in the list Where the last name displayed was Tom Smith, and other names followed. Fred Smith Joe Jones Tom Smith Fred Furrier etc etc My first thought was that I've run into some sort of text limitation. So I pasted the value of the last concatenation and got the length of it using the LEN function. The length was 1387. An awfully strange number to be a text limitation. Please don;t criticize the "inelegance" of my solution. I've had to adapt an existing computed list for a non-excel user to be able to copy and paste into a text document the final list of names and this seemed to be the simplest way to do it until I ran into this limitation. Any ideas what I've stumbled into? |
Concatenate question on limitations
I'll agree, that is a weird limit. Unfortunately, I am unable to duplicate
your results. (I was able to concatenated 235 cells successfully). Do note that while a cell may contain all the data, it might not be able to display all of it, depending on cell format. In XL help file, take a look at "Excel limits and specifications" for further info on text length limits. So, in a random shot at creating a solution, I would recommend forgetting you ever heard of the CONCATENATE function. Not only does it have a long name, it has a 30 word limit! Instead, you can simply string things together using the ampersand "&" symbol, with no "30 word limit". Now you're only limited by the text length of a formula. With B1 formula: =A1 B2 formula, copied down: =B1&","&A2 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "emcentee" wrote: I'm trying to concatenate a list of "N" number of names. I'm aware of the 30 item limitation, but because of the N number of names, I thought I'd try something, Here's what I have: A B name 1 name 2 =concatenate(b1,b2,", ") name 3 =concatenate(b2,b3,", ") name 4 =concatenate(b3,b4,", ") etc, etc =concatenate(b(n-2),b(n-1),", ") name n =concatenate(b(n-1),bn,", ") and this works just fine. I've been able to concatenate up to 86 names successfully. What I ran into today baffles me. Today, I had 107 names. in the middle of the 87th name, it cut off and would not add any additional text. Example of the last several charachters... ~~~ Fred Smith, Joe Jones, Tom Sm The line above was repeated for all additional names in the list Where the last name displayed was Tom Smith, and other names followed. Fred Smith Joe Jones Tom Smith Fred Furrier etc etc My first thought was that I've run into some sort of text limitation. So I pasted the value of the last concatenation and got the length of it using the LEN function. The length was 1387. An awfully strange number to be a text limitation. Please don;t criticize the "inelegance" of my solution. I've had to adapt an existing computed list for a non-excel user to be able to copy and paste into a text document the final list of names and this seemed to be the simplest way to do it until I ran into this limitation. Any ideas what I've stumbled into? |
Concatenate question on limitations
Hi,
Excel will only display the first 1024 characters in a cell, even if it contains more. Thus, your cell may have more than 1300 characters, but the display cuts off at 1024. "emcentee" wrote: I'm trying to concatenate a list of "N" number of names. I'm aware of the 30 item limitation, but because of the N number of names, I thought I'd try something, Here's what I have: A B name 1 name 2 =concatenate(b1,b2,", ") name 3 =concatenate(b2,b3,", ") name 4 =concatenate(b3,b4,", ") etc, etc =concatenate(b(n-2),b(n-1),", ") name n =concatenate(b(n-1),bn,", ") and this works just fine. I've been able to concatenate up to 86 names successfully. What I ran into today baffles me. Today, I had 107 names. in the middle of the 87th name, it cut off and would not add any additional text. Example of the last several charachters... ~~~ Fred Smith, Joe Jones, Tom Sm The line above was repeated for all additional names in the list Where the last name displayed was Tom Smith, and other names followed. Fred Smith Joe Jones Tom Smith Fred Furrier etc etc My first thought was that I've run into some sort of text limitation. So I pasted the value of the last concatenation and got the length of it using the LEN function. The length was 1387. An awfully strange number to be a text limitation. Please don;t criticize the "inelegance" of my solution. I've had to adapt an existing computed list for a non-excel user to be able to copy and paste into a text document the final list of names and this seemed to be the simplest way to do it until I ran into this limitation. Any ideas what I've stumbled into? |
Concatenate question on limitations
To clarify, the data is still all there, even if it is not displayed, and
your user will be able to copy the cell to a text document just fine, with all data being transferred. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: I'll agree, that is a weird limit. Unfortunately, I am unable to duplicate your results. (I was able to concatenated 235 cells successfully). Do note that while a cell may contain all the data, it might not be able to display all of it, depending on cell format. In XL help file, take a look at "Excel limits and specifications" for further info on text length limits. So, in a random shot at creating a solution, I would recommend forgetting you ever heard of the CONCATENATE function. Not only does it have a long name, it has a 30 word limit! Instead, you can simply string things together using the ampersand "&" symbol, with no "30 word limit". Now you're only limited by the text length of a formula. With B1 formula: =A1 B2 formula, copied down: =B1&","&A2 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "emcentee" wrote: I'm trying to concatenate a list of "N" number of names. I'm aware of the 30 item limitation, but because of the N number of names, I thought I'd try something, Here's what I have: A B name 1 name 2 =concatenate(b1,b2,", ") name 3 =concatenate(b2,b3,", ") name 4 =concatenate(b3,b4,", ") etc, etc =concatenate(b(n-2),b(n-1),", ") name n =concatenate(b(n-1),bn,", ") and this works just fine. I've been able to concatenate up to 86 names successfully. What I ran into today baffles me. Today, I had 107 names. in the middle of the 87th name, it cut off and would not add any additional text. Example of the last several charachters... ~~~ Fred Smith, Joe Jones, Tom Sm The line above was repeated for all additional names in the list Where the last name displayed was Tom Smith, and other names followed. Fred Smith Joe Jones Tom Smith Fred Furrier etc etc My first thought was that I've run into some sort of text limitation. So I pasted the value of the last concatenation and got the length of it using the LEN function. The length was 1387. An awfully strange number to be a text limitation. Please don;t criticize the "inelegance" of my solution. I've had to adapt an existing computed list for a non-excel user to be able to copy and paste into a text document the final list of names and this seemed to be the simplest way to do it until I ran into this limitation. Any ideas what I've stumbled into? |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com