Text in pivot table limited to 255 characters

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.
0
2/6/2005 1:43:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
637 Views

Similar Articles

[PageSpeed] 44

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

0
dsd1 (5911)
2/6/2005 1:55:02 AM
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
0
ec357201 (5290)
2/6/2005 2:04:11 AM
Thanks for the responses.  I will try your suggestions.  It is nice to know 
that I had not done something wrong.


0
tanyae823 (1)
2/6/2005 11:21:02 PM
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.
0
9/30/2005 10:58:03 PM
Reply:

Similar Artilces:

Pivot Tables + Color + Dynamic Content generation and display #2
I have a dynamically generated pivot table (generated from actions in another spreadsheet - number of rows and location keeps changing - cant use conditional formatting). The data I currently have displayed on the pivot table is: Resource Project Allocation Jan Allocation Feb -------- ------- -------------- -------------- Resource1 Project1 0.5 0.5 Resource1 Project1 0.25 0.5 Resource1 Total 0.75 1.0 Resource2 Project1... .. I need to (1) Highlight, in red, items where Resource totals are less than 1.00. e.g. Resource 1 Total. Just...

Trying to put a pivot table report on an existing sheet causes err #3
Thanks to Frank Kabel, I can now make great pivot tables. The problem I am having is that I want to write a macro to generate pivot tables, and I want the pivot table report to be saved on a worksheet called "Sum of Open", which is overwritten each time the macro runs. I have created the sheet named "Sum of Open" which is in the same workbook, but when i try to save it to there ('Sum of Open!'), I get an error: "Your formula contains and invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, ...

Dates shown as text
When I use the trim function on some dates that have been input as text, the leading spaces are gone. But Excel still treats the dates as though they were text However, if I hit the F2 key to enter the edit mode and then press Enter, it converts the information to dates. How do I convert thess quasi-text cells without manually editing each one? Is there a worksheet function that will do this? Thanks Try this: - copy any empty cell - select the problem cells - do Edit>Paste Special, click Add, then OK - do Format>Cells>Date HTH Anders Silv=E9n "Colleyville Alan" <...

text on document is really small even though it is set at 12 why?
When I am in a word 03 document the text displayed is really small, even though the font size is set at 12. Either that or the page is really big Have you tried changing the zoom rate? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "suzannah" <suzannah@discussions.microsoft.com> wrote in message news:894012CB-69D6-48BC-92DC-54DBAF71CB7C@microsoft.com... > When I am in a word 03 document the text displayed is really small, even > though the font size is set at 12. Either that or the page is...

Pivot Table/Multiple Workbooks
Is there anyway to create a pivot table using information from multiple workbooks (not worksheets?) I have 3 files: count1.xls count2.xls count3.xls All files have same headers but each file represents a different month. How can I create a pivot table using these 3 excel files? I also want the pivot table to get updated everytime I make a change to one of the excel files. If this is doable, what is maximum number of files that I can link to that pivot table? Thanks. You can use a pivot table across mulitple workbooks by using the "Multiple Consolidation Ranges" in the ...

Creating a botton to update data from a Form to Table
Greetings People I need to know how to create a botton on a Form (Microsoft Access) that may allow me to update the data in a Table. I know that I have to use VBA or VB ..NET CODE to create it. Where could I find a sample code that may help me with that? Reboot Kid On Tue, 11 May 2010 16:19:01 -0700, Reboot the Kid <ReboottheKid@discussions.microsoft.com> wrote: >Greetings People > >I need to know how to create a botton on a Form (Microsoft Access) that may >allow me to update the data in a Table. I know that I have to use VBA or VB >.NET CODE to c...

Pivot forms don't work
I've a strange problem on my company PC. All PCs are equal and the installation of OS and programs has been made using the same disc image. On some PCs, like mine, is not possible to open pivot tables and pivot graphs. Double clicking one of them, the hourglass appears for a couple of seconds and that's all. All Access components are installed. Have you already heard about a similar problem? Is there any setting to check? Thanks in advance ...

Formatting text within a cell
I'm pretty new to Excel ... I want to emulate my formatting in Word of a title with several bulleted subpoints indented and under it. Every time I hit "Enter" for a new line, I get skipped to a new cell. Any help will be appreciated. When editing a cell, if you hold down the [Alt] key when you press [Enter], that will put the subsequent text on a new line in the same cell. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "AV Guy" wrote: > I'm pretty new to Excel ... I want to emulate my formatting in Word of a > title with several bullete...

pasting a block of text into one cell
Hi everybody, I'm trying to paste a few passages from a book, surrounded by a trim() command, into a single cell of a worksheet. Since this has multiple lines, and tabs at the beginning of each line, the paste occurs in multiple rows and the trim() command doesn't work. Does anyone know how to paste something like this into one cell? For clarity, the block is like this: =trim( " blah blahhhhhhh zzzzz ") (with tabs before each line) Is there any way to do this? Any help would be greatly appreciated. Thanks, Jezzica85 Hi, Try the =CLEAN(A1) function. -- Tha...

Pivot Tables #36
I have a table of data that consists of well numbers, well names, volumes, and values. For some wells the volumes and values are zero - I want to pivot table this data, however, I want the pivot table to exclude any wells that have zero volumes and values. Is there a criteria option in pivot tables? -- thanks, mc I would add a helper column of formulas to the raw data. I'd put a formula that did something like: =if(or(volume=0,value=0),"hide","show") (You'll do the real formula) Then use that column in the page field. Then just use that page field to cha...

Pivot Table not sorting correctly
In column A I have several of the same names listed more than once spelled exactly the same but when these line are pulled into a pivot table each is displayed seperately. I followed the same exact procedure for this table as I have for the others that I displayed correctly. What causes those lines to be displayed on separate lines? Perhaps some of the entries have space characters at the end, and others don't. Marian wrote: > In column A I have several of the same names listed more than once spelled > exactly the same but when these line are pulled into a pivot table each ...

Adding to VLOOKUP table
I have a table to which I have added 2 further columns I have a formula which looks up colums 2 and 3 when I key data in column 1 =if(trim(a23)="","",vlookup(a23,scanner_table,2,0)) The new data I want to select is in column 5 so =if(trim(a24)="","",vlookup(a24,scanner_table,5,0)) to which I get the raspberry #REF I am sure this must be very basic but sometimes I can't see the wood for trees Any comments would be much appreciated. Hi Billy Have you extended the range definition for scanner_table to include the additional 2 columns? Regards R...

remove the text preview in a slide text box
My slide has a text box and the bullits are grayed out until I select them in my presentation. How can I remove the grayed out preview? That is just a placeholder until you put text into it. It does not show up until your in the slide presentation mode. If you want a blank slide without a place holder you need to select new slide and select the blank option. -- Michael Koerner MS MVP - PowerPoint "Ken V" <Ken V@discussions.microsoft.com> wrote in message news:F33DE7C8-4082-44A4-8499-91094BBA7FCC@microsoft.com... My slide has a text box and the bullits ar...

I was told I could not activate the code since I pased the limit .
I purchased Publisher about 6 months ago since I had an older version and needed to upgrade and use it for flyer in my real estate and mortgage business. I had one computer that got messed up and we had to restore and when we did that all the softward had to be reloated. I also just went from one laptop to a newer one and now on this newer one I'm being told that I can't activate the software due to I've used the limit for this software. I the only user of this software along with my wife who is my partner and find this very restrictive since I did purchase the software...

Finding cells with leading characters
My spreadsheet looks like this: A 1 000000 2 182068 3 198506 4 200081 5 368010 6 580905 7 680001 8 686850 9 690060 10 700012 11 701068 12 802054 13 920211 14 968902 Using EDIT/FIND, when I enter '68' in the "Find What:" box, Excel finds A2, A5, A7, A8, A11 and A14 because their values contain '68'. What do I enter in the "Find What:" box to find the cells whose values BEGIN with '68'? (In this example, ...

Pivot tables #41
Hi, I have an .xls and have used a pivot table to analyse 1000s of rows of data. An eg of the data that the Pivot table returns is as follows: Count of Business type Researcher Business type Total Alan Pedersen Film distribution 11 Film production 20 Film sales 10 Alan Pedersen Total 41 The pivot table is counting the number of times "Film distribution" & "Film production" etc re-occurs per researcher. What I need the pivot table to count is the number of business types only. eg what I am looking for is: Count of Business type Researcher Bu...

how do justify text in linked textboxes without hyphernation
I am trying to do a newsletter with linked text boxes. When I paste an article into the boxes I can not get them to fit properly. What I get is words hyphernating next to the edge. I would like to justify and get the words fitting in. How can I do this Do you have the paragraphs set for justified? If you want to control the hyphenation manually, Tools - Language - Hyphenation - Clear "Automatically hyphenate this story" -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "Naida" <Naida@discussions.microsoft...

Table data update
I have a table that contains several fields but I am only concerned with two, OldPrice and NewPrice. On my form for entering new data both of these fields show. The custermer is charged using the average of these two fields. My problem is that when I enter a new NewPrice I need to have the OldPrice field update to the NewPrice before the change. EXAMPLE OldPrice $8.00 NewPrice $10.00 Price goes up to $12.00 and I enter it into the form Table should update to show OldPrice $10.00 NewPrice $12.00 Try putting the following code in the AfterUpdate event of ...

Delete table values automatically
Hi All, I want to create a routine that will clean all records from a specific table automatically once a week (if possible) or each time that the MDB opens up. Any thoughts? Thanks a lot in advance Hi, the code to empty a table is simple Public Sub EmptyTable() Dim strSQL as String Dim db as DAO.Database Set db = dbengine(0)(0) strSQL = "DELETE FROM TableName" db.Execute strSQL, dbFailOnError Set db = Nothing Exit Sub the above code goes in a standard module when your database opens, you call the routine EmptyTable Jeanette Cunningham "Lp12" <Lp12@discussion...

Disappearing column totals in cross-reference table query
I have added column totals in a cross-reference table query, by clicking Registers/Totals. I saved the query and closed it. (I'm using a Portuguese version of MS Office and apologise if my translation of the commands is not perfect.) Whenever I open the query again, the totals have disappeared. I have to activate them again by clicking on Registers/Totals. This doesn't happen in a simple query. The column totals I add remain activated, and are visible when the query is opened the next time. I would be grateful for any help. Matias ...

Text size #3
Is there a way to make the text size preference "sticky"? I mean for it to be remembered across sessions until I change it. Thanks. ...

Access 2000 Table
I created a table roughly a year and a half ago. The table has been modified numerous times and now holds over 10,000 records. I am concerned that I may have to start a new table at some point if Access will no longer accept new records. Is there a limit as to how much information or how many records can be held? Unless we had a restriction on number of rows in Access 2000, based on specifications for newer version of Access (2003), you aren't restricted by rows but by file size.http://office.microsoft.com/en-us/access/HP051868081033.aspxAs long as your file hasn't reached the 2GB m...

Highlighting Found Text in Excel
How can one find key words in an Excel document and programmatically highlight the found key words? Best regards In xl2002+, you can use Edit|Replace and specify a format for the whole cell after the change. (Change the string to itself and specify the formatting you like under the Options button. But that affects the whole cell--may not be what you want if there are other words in those cells.) Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? Option Explicit Option Compare Text Sub testme...

Excel Tables
I am copying excel table into word and doing a simple paste. When paste the table, the empty space in the cell is filled with dots an the number wraps. Any suggestions -- Jewel Howar ----------------------------------------------------------------------- Jewel Howard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2924 View this thread: http://www.excelforum.com/showthread.php?threadid=48963 I am unable to produce that result. If you paste into Notepad does the result show the same dots? To paste, are you highlighting the required number of cells in an MSW...

- Excel 2007: Pivot Table to SSAS why menu item "Options"->"Formulas" is disabled?
I am using Excel 2007 with SQL Server Analysis Services 2005 SP2. I want to create calculated measure based on other measures, but I cannot do this for Pivot table. If I go to menu "Options"->"Formulas" I can see that it is disabled. Same menu is enabled if I create Pivot table based on cells in sheet. How can I create caclulated measure for Pivot Table based on SSAS? Thank you ...