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?