• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Excel only displays a portion of a text entry

Hello,

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.

Thanks
0
Steve_Brady
Asked:
Steve_Brady
2 Solutions
 
rspahitzCommented:
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)
0
 
EirmanCommented:
The limit for excel 2007 is 32,767 characters.

Have a look here
http://excelribbon.tips.net/T010231_Character_Limits_for_Cells.html

Are you running in compatability mode
0
 
Steve_BradyAuthor Commented:
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now