Excel only displays a portion of a text entry


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.

Who is Participating?
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)
EirmanChief Operations ManagerCommented:
The limit for excel 2007 is 32,767 characters.

Have a look here

Are you running in compatability mode
Steve_BradyAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.