I am using a pivot table as an efficient way to aggregate text responses from
a large data set. However, the pivot table cuts off the text after the first
255 characters (similar to when you copy a worksheet by using the move/copy
option).

How can I overcome this? I have tried putting the pivot table on the same
sheet as the dataset, but that does not work.

I should also note that these pivot tables are then fed into an automated
report through a complicated set of VLOOKUPs, etc. The pivot table
aggregates several questions and responses from many areas of the datset into
one discrete, defined area.

The character limit in a pivot table cell is 255, and as far as I know,
this setting can't be changed manually or programmatically.

Perhaps the automated report could pull additional data from the pivot
table source.

tanyae_823 wrote:
I am using a pivot table as an efficient way to aggregate text responses from
a large data set. However, the pivot table cuts off the text after the first
255 characters (similar to when you copy a worksheet by using the move/copy
option).

How can I overcome this? I have tried putting the pivot table on the same
sheet as the dataset, but that does not work.

I should also note that these pivot tables are then fed into an automated
report through a complicated set of VLOOKUPs, etc. The pivot table
aggregates several questions and responses from many areas of the datset into
one discrete, defined area.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I don't think you can change this behavior.

Maybe you could add a code that represents that longgggggg response. Then
create the pivottable based on that.

Then after the pivottable is created, you could use =vlookup() or
=index(match()) to retrieve the real long text.


tanyae_823 wrote:

I am using a pivot table as an efficient way to aggregate text responses from
a large data set. However, the pivot table cuts off the text after the first
255 characters (similar to when you copy a worksheet by using the move/copy
option).

How can I overcome this? I have tried putting the pivot table on the same
sheet as the dataset, but that does not work.

I should also note that these pivot tables are then fed into an automated
report through a complicated set of VLOOKUPs, etc. The pivot table
aggregates several questions and responses from many areas of the datset into
one discrete, defined area.

--

Dave Peterson

Thanks for the responses. I will try your suggestions. It is nice to know
that I had not done something wrong.

Tanyae_823, can you show me how to use the pivot table to aggregate text
responses? That could really help me. If you could just provide a sample
that would be great.

"tanyae_823" wrote:

I am using a pivot table as an efficient way to aggregate text responses from
a large data set. However, the pivot table cuts off the text after the first
255 characters (similar to when you copy a worksheet by using the move/copy
option).

How can I overcome this? I have tried putting the pivot table on the same
sheet as the dataset, but that does not work.

I should also note that these pivot tables are then fed into an automated
report through a complicated set of VLOOKUPs, etc. The pivot table
aggregates several questions and responses from many areas of the datset into
one discrete, defined area.