How to truly disable text overflow

Discuss the spreadsheet application
Post Reply
johnnybaloney
Posts: 5
Joined: Tue Jan 21, 2014 2:14 pm

How to truly disable text overflow

Post by johnnybaloney »

Hello,

Menu: Tools -> Options -> LibreOffice Calc -> View -> Display -> Text overflow

The manual says:
Text overflow - If a cell contains text that is wider than the width of the cell, the text is displayed over empty neighbouring cells in the same row. If there is no empty neighbouring cell, a small triangle at the cell border indicates that the text continues.
The text spills to the empty neighbouring cells regardless whether this option is selected or not. The only difference that I can see is the presence or absence of the red arrow. Is this all it does?

How to actually stop the cell's content from spilling to the neighbouring empty cells?
Linux / LibreOffice 4.1.3.2
johnnybaloney
Posts: 5
Joined: Tue Jan 21, 2014 2:14 pm

Re: How to truly disable text overflow

Post by johnnybaloney »

The solution posted here is unfortunately unsatisfactory.

Setting horizontal alignment to Fill apart from repeating the cell's content (this is how it is supposed to work) also right aligns the content. Both are not something I want to happen.

Am I right to say that Text Overflow feature is broken? Clearly, disabling it does not stop cell's content from spilling over to the neighbouring cells.
Linux / LibreOffice 4.1.3.2
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to truly disable text overflow

Post by acknak »

As far as I can see, the "Text overflow" option only determines whether or not the red overflow indicator is displayed. The actual text is only truncated when there is content in the adjacent cell.

But the other suggestion there will still work: fill the adjacent cells with some invisible character--a space, say--to prevent the overflow. You can hide the extra column if you like.
AOO4/LO5 • Linux • Fedora 23
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to truly disable text overflow

Post by Villeroy »

Database reports and Writer tables are made for tabular text layout.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
johnnybaloney
Posts: 5
Joined: Tue Jan 21, 2014 2:14 pm

Re: How to truly disable text overflow

Post by johnnybaloney »

acknak wrote:As far as I can see, the "Text overflow" option only determines whether or not the red overflow indicator is displayed. The actual text is only truncated when there is content in the adjacent cell.
Hi acknak, The question is, is it all what the Text Overflow option is supposed to do and everything is fine or is the Text Overflow not quite working as expected? In other words, does this (mis)behaviour warrant filling a bug/enhancement report? The way the manual describes it implies that unticking this option should stop long text spilling over to the neighbouring cells and this is not happening.
acknak wrote:But the other suggestion there will still work: fill the adjacent cells with some invisible character--a space, say--to prevent the overflow. You can hide the extra column if you like.
Unfortunately not for my case. The data I work with has missing entries in various columns and filling such cells with whitespace by hand is a no-go. Unless there is some way to do it in a fairly automated manner?
Villeroy wrote:Database reports and Writer tables are made for tabular text layout.
I'm not sure about the meaning of your comment in relation to my problem. Could you elaborate?
Linux / LibreOffice 4.1.3.2
User avatar
lino
Volunteer
Posts: 312
Joined: Thu Mar 28, 2013 11:16 am
Location: http://goo.gl/maps/rsfPZ

Re: How to truly disable text overflow

Post by lino »

You do:
Format > Cells
Alignment tab
Text alignment - Horizontal - Select "Filled"
OK

Bum, shrink!
johnnybaloney
Posts: 5
Joined: Tue Jan 21, 2014 2:14 pm

Re: How to truly disable text overflow

Post by johnnybaloney »

@lino Please read this first.
Linux / LibreOffice 4.1.3.2
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to truly disable text overflow

Post by acknak »

johnnybaloney wrote:... is it all what the Text Overflow option is supposed to do and everything is fine ...
Yes; I believe so.
... The way the manual describes it implies that unticking this option should stop long text spilling over to the neighbouring cells and this is not happening.
The help text you quoted could be interpreted either way. I expect it's simply another instance where the option label and the help text are unclear.

You can certainly submit a bug report, just don't expect too much in response: there are many requests with higher priority.
[Tutorial] Reporting bugs or suggestions
acknak wrote:But the other suggestion there will still work: ...
... filling such cells with whitespace by hand is a no-go. Unless there is some way to do it in a fairly automated manner?
Copy/paste is too much trouble? Remember, you're filling all the cells to the right of the ones you want to block; it requires just one copy/paste, or if your table is well-formed, one double-click to fill out the column.
Villeroy wrote:Database reports and Writer tables are made for tabular text layout.
I'm not sure about the meaning of your comment in relation to my problem. Could you elaborate?
Detailed formatting in a spreadsheet can be a nasty time suck because spreadsheets are designed primarily for calculating & managing data, not so much for formatting. You have to constantly work against limitations and then the finished product still doesn't quite behave the way you want.

If polished output is a priority, use a tool designed for that purpose.

Of course, you're the only one who can make that decision.
AOO4/LO5 • Linux • Fedora 23
User avatar
lino
Volunteer
Posts: 312
Joined: Thu Mar 28, 2013 11:16 am
Location: http://goo.gl/maps/rsfPZ

Re: How to truly disable text overflow

Post by lino »

Setting horizontal alignment to Fill apart from repeating the cell's content (this is how it is supposed to work) also right aligns the content. Both are not something I want to happen.
1. As we can see on the screenshot, if longer than cell/2, the content is left aligned.

2. If you think that cell is in danger of repeating the content, use conditional formatting with condition:
Formula is LEN(C2)<10
where C2 is cell it self and it can hold 20 characters, and
formatting set to align the content left.

Bum, done!
Attachments
Screenshot-FilledCell.png
Screenshot-FilledCell.png (3.28 KiB) Viewed 25600 times
johnnybaloney
Posts: 5
Joined: Tue Jan 21, 2014 2:14 pm

Re: How to truly disable text overflow

Post by johnnybaloney »

lino wrote:
Setting horizontal alignment to Fill apart from repeating the cell's content (this is how it is supposed to work) also right aligns the content. Both are not something I want to happen.
Are you saying that setting the cell's horizontal alignment to Filled left aligns the cell's content for you? It doesn't for me. :?
acknak wrote:
johnnybaloney wrote:... is it all what the Text Overflow option is supposed to do and everything is fine ...
Yes; I believe so.
Believe so or see so? :D I'm aware that there are always higher priorities and usability is usually at the bottom of the list but for reference and perhaps clarification purposes I will file a report.
acknak wrote:Detailed formatting in a spreadsheet can be a nasty time suck because spreadsheets are designed primarily for calculating & managing data, not so much for formatting.
This was certainly the case in the times of Psion Vu-Cal. These days the longest menu item in my Calc is Format and the longest default toolbar is... Formatting, neither has much to do with managing data. I find Calc a nifty tool for quick data analysis and its formatting abilities a helpful feature. Not everyone is an accountant out there, you know.
acknak wrote:
acknak wrote:But the other suggestion there will still work: ...
... filling such cells with whitespace by hand is a no-go. Unless there is some way to do it in a fairly automated manner?
Copy/paste is too much trouble?
The part of the quote you cut out was important here. Yes, copy/paste is both trouble and dangerous, one wrong paste and you lose data from a cell.

I said that filling the gaps by hand would be a no-go so is here is my first macro experiment:

Code: Select all

Sub Main
	
	doc = ThisComponent
	sheet = doc.getSheets.getByIndex(0)

    usedRange = sheet.getCellRangeByPosition(0,0,0,0)
    ' creates a cell cursor including the whole spreadsheet
    cursor = sheet.createCursorByRange(usedRange)
    cursor.GotoEndOfUsedArea(false)
    
    lastRow = cursor.RangeAddress.EndRow
    lastColumn = cursor.RangeAddress.EndColumn + 1
    
    for row = 0 to lastRow
    	for col = 0 to lastColumn
    		cell = sheet.getCellByPosition(col,row)
    		if (cell.Type = 0) then
				cell.setFormula(" ") ' add space
    		endif
    	next col
    next row

	Print "Done!"

End Sub
Before and after below. Now I can see what's going on.
Attachments
text-overflow.jpg
Linux / LibreOffice 4.1.3.2
Benaround
Posts: 4
Joined: Thu Jun 12, 2008 3:03 pm
Location: Hampshire, UK

Re: How to truly disable text overflow

Post by Benaround »

As far as I can make out, you select wherever you want text wrapped, a cell, row, column, or the whole page or document, then click the "wrap" button on the toolbar. It's next to the three "align" buttons. You may now find at least two "sub issues".
The cell and all cells in that row have been extended vertically to hold all the text. The solution is to force the row height back to the same as all the others by Format>Rows>Height and select required height or the default. Now you may find that when you narrow the column width only the bottom row oftext is displayed, that is, the last word or two and a blank to the end of the line. You probably want to see the first few words, so go to the Align (Top) button on the toolbar. If you don't see what you want on the toolbar you have to go Select required>Format>Cells and in the window select the Align tab and work from there, where you can align and also select wrap in the properties section.
OOo 2.4.X on Ms Windows XP
Post Reply