Excel only displays a portion of a text entry

Posted on 2011-05-03
Last Modified: 2012-05-11

In Excel (2007), I have pasted a large text document (a book with several hundred pages) in column B.  It was pasted so that each cell includes one paragraph.

Because I need to have certain paragraphs formatted in different ways, I have hidden col B and have chosen col E to actually display the text.  That is easily accomplished by formatting col E to Wrap Text and by entering =B# formulas in col E all the way down.  

(FYI, a simple example of the formatting I'm after is being able to indent the first line of a large paragraph in row 8.  The actual paragraph, which is hidden, is located in B8.  It is displayed [with the first line indented] in E8 by using the formula:  ="      "&B8.)

The problem I'm having is that for some reason, cells in col E will display no more than 1018 characters from col B.  In other words, for any paragraph in col B with more than 1018 characters, col E will display only the first 1018 characters and no more.

For example, =LEN(B87) gives a value of 1809 but E87 displays only the first 1018 characters.  Interestingly however, =LEN(E87) also gives a value of 1809.

Any idea what would be causing that?  

BTW, I pasted the content into a new spreadsheet and did not have the same problem so it's something unique to this one.  I'm loathe to take the time to move everything to a new file though and I would really like to know the cause if possible.

Question by:Steve_Brady
    LVL 22

    Accepted Solution

    Not really sure of the problem but a few thoughts
    (1) If you put that formula in E elsewhere (like Sheet2 with formula ="     "&Sheet1!B8) do you get the same issue?
    (2) Did you try to move the contents to a different sheet WITHIN the same workbook (or just a new workbook)?
    (3) Try to see what happens if you show the right 1000 characters (=Right(B8))
    (4) Is the text truncating in the formula bar, or in the cell?  
    (5) I'm wondering if there's a stray character in the cell that is causing Excel to stop showing the rest of the text so maybe removing that character will help show the rest: =Left(B8, 1018) & Mid(B8, 1020, 9999)
    (6) If you copy the contents to a different cell on the same sheet, do you still have the problem? How about if you paste into Notepad, copy from Notepad, and paste back? (sometimes stray formatting is removed)
    LVL 23

    Assisted Solution

    The limit for excel 2007 is 32,767 characters.

    Have a look here

    Are you running in compatability mode

    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now