Too Big for My Cell (in Excel) 1

[HelpDeskMan]

I have a user who is having trouble with an Excel Spreadsheet (Office 2003). Basically, some of the cells have inordinate amounts of text. The text runs over and then doesn't all show up when she goes to print out the spreadsheet or view in print preview. She can resize the cell but the text is still 'cut off' as it were. Does anyone have any insight into this one? I've exausted my resources. :\

 

[KenWright]

Excel help will tell you that there's a maximum of 1024 characters per cell to be displayed. You can though try adding some manual line breaks with ALT + ENTER and you will get more to display.
..

A clarification posted by Harlan Grove in the MS groups though:-

As long as the text contains appropriately spaced newline chars and the cell and worksheet are formatted to allow display of more than 1024 chars in the cell, Excel can *display* up to 32762 chars. Now the result is completely unreadable, but the 1024 char limit only applies if the user does absolutely nothing to expand the number of characters displayed in the cell.


This is one point on which Excel's written specs differ from what Excel is actually capable of doing.

Regards
Ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

limit for character display in a cell is 1,024. Up to about 32,000 can display in the formula bar but only 1,024 in the cell

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[byundt]

Here's a sub that adds the needed line feed characters, as described by Ken. It requires Excel 2000 or later and should be installed in a regular module sheet. Select the cells to be "modified", then run the sub.

Sub DisplayLongText()
'Adds line feed characters as required on cells in selection that are longer than 1024 characters
Dim cel As Range
Dim col As Long
For Each cel In Selection
    AddLineFeeds cel, col
Next
col = 0     'Force line length dialog to display the next time sub runs
End Sub

Sub AddLineFeeds(cel As Range, col As Long)
'Adds line feed characters at end of each line of text. Some experimentation may be required to set number
    'of characters at the wrapping point.
'Code requires Excel 2000 or later because of Replace and InStrRev functions
Static lineIncr As Long
Dim i As Long, j As Long, pos As Long
Dim sLeft As String, str As String, sRight As String, sLineFeed As String

With cel
    If Len(.Value) <= 1024 Then Exit Sub
    
    'Remove line feed characters which may have been added previously. These always follow an ASCII 160 space.
    sLineFeed = Chr(160) & Chr(10)      'Code puts an ASCII 160 space before every added line feed character
    str = Replace(.Value, sLineFeed, " ")
    
        'The maximum permitted number of characters on a line. User-specified up to a limit of 256 characters/line
    If .Column <> col Then  'Use same value as last time if still working in same column
        lineIncr = Application.Min(InputBox(Prompt:="Please specify the desired column width (in characters)", _
            Title:="Long Text In Cell Utility", Default:=.ColumnWidth - 1), 256)
        col = .Column
    End If
    
    sLeft = Left(str, 1022) 'Excel has no problem wrapping the first 1024 characters
    pos = InStrRev(sLeft, " ")      'Find right-most space in first 1022 characters
    If pos = 0 Then     'No space found, so force a break after 1022 characters
        sLeft = sLeft & sLineFeed
        sRight = Mid(str, 1023)
    Else    'Put ASCII 160 plus line feed characters in place of this right-most space
        sLeft = Left(str, pos - 1) & sLineFeed
        sRight = Mid(str, pos + 1)
    End If
    
    pos = 1     'Loop through remainder of text, looking for places to put ASCII 160 plus line feed characters
    Do
        j = InStr(pos, sRight, Chr(10))
        If j > 0 And j - pos <= lineIncr Then
            pos = j + 1
        Else
            i = InStrRev(sRight, " ", pos + lineIncr)   'Find right-most space in next lineIncr characters
            If i > pos Then     'Put ASCII 160 plus line feed characters in place of this right-most space
                sRight = Left(sRight, i - 1) & sLineFeed & Mid(sRight, i + 1)
                pos = i + 2
            Else    'Didn't find a good place to break the line, so force the break in middle of a word
                sRight = Left(sRight, pos + lineIncr) & sLineFeed & Mid(sRight, pos + lineIncr + 1)
                pos = pos + lineIncr + 3
            End If
        End If
        If Len(sRight) - pos < lineIncr Then Exit Do    'Not enough text left for a full line
    Loop
    
    .Value = sLeft & sRight     'Put the rebuilt string in place of the original
End With
End Sub

Brad

 

[HelpDeskMan]

Thanks, Ken and Brad. But you say "run the sub". How do you do that?

 

[byundt]

To install a sub or function in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) Select the cells containing long text that you want to modify
6) ALT + F8 to open the macro window
7) Select the macro
8) Click the "Run" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

 

[KenWright]

How to use a macro FAQ:-
faq707-5758

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------