Download it now!
Bug 96822 - Cell With Numeric Values Formatted As "TEXT" Should Align Left
Summary: Cell With Numeric Values Formatted As "TEXT" Should Align Left
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Yogesh Desai
URL:
Whiteboard: target:5.2.0
Keywords:
Depends on:
Blocks:
 
Reported: 2015-12-30 02:44 UTC by Kevin Suo
Modified: 2016-11-30 08:52 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
test file: values in column A should align left as they are formatted as "TEXT" (4.70 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-12-31 00:59 UTC, Kevin Suo
Details
Excel and Calc compare (193.91 KB, image/png)
2015-12-31 01:02 UTC, Kevin Suo
Details
I have submitted the patch which solves this issue. (534.11 KB, patch)
2016-01-05 18:43 UTC, Yogesh Desai
Details
Type the numbers in cell & then change format as Text (539.73 KB, image/png)
2016-01-05 18:57 UTC, Yogesh Desai
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2015-12-30 02:44:29 UTC
In Calc, when you set the a cell format to "text", the cell value alignment does not change to left accordingly. 
However, when you type in new values in cells pre-formatted as "text", the values are align left as expected.

Steps to Reproduce:

1.1. Type "123" in A1, without quotes.
1.2. Set cell format of A1 to "Text". 
(Observe that the value is right-aligned. The expected behaviour is left-aligned as the cell value is text).

2.1. Set cell format of A2 to "Text".
2.2. Type "123" in A2, without quotes.
(Oberve that the value is left-aligned. This is expected.)

Version: 5.0.4.2
Build ID: 2b9802c1994aa0b7dc6079e128979269cf95bc78
Locale: zh-CN (zh_CN)
Win10 x64.
Comment 1 A (Andy) 2015-12-30 08:57:18 UTC
Reproducible with LO 5.1.0.1, Win 8.1
Comment 2 GerardF 2015-12-30 09:52:51 UTC
(In reply to Kevin Suo from comment #0)
 
> 1.1. Type "123" in A1, without quotes.
> 1.2. Set cell format of A1 to "Text". 
> (Observe that the value is right-aligned. The expected behaviour is
> left-aligned as the cell value is text).

1. You type 123 in A1 : This is a numeric value.
2. You are formatting the cell as "Text".

Changing format of a cell with data do not affect data, the cell content is still a numeric value.
 
> 2.1. Set cell format of A2 to "Text".
> 2.2. Type "123" in A2, without quotes.
> (Oberve that the value is left-aligned. This is expected.)

2.3 Change format A2 to "Standard" or any numeric format.
A2 is still left aligned and "123" is still text.

No bug here. Changing cell format never affect cell content.
Comment 3 A (Andy) 2015-12-30 10:06:54 UTC
(In reply to GerardF from comment #2)
> (In reply to Kevin Suo from comment #0)
>  
> > 1.1. Type "123" in A1, without quotes.
> > 1.2. Set cell format of A1 to "Text". 
> > (Observe that the value is right-aligned. The expected behaviour is
> > left-aligned as the cell value is text).
> 
> 1. You type 123 in A1 : This is a numeric value.
> 2. You are formatting the cell as "Text".
> 
> Changing format of a cell with data do not affect data, the cell content is
> still a numeric value.
>  
> > 2.1. Set cell format of A2 to "Text".
> > 2.2. Type "123" in A2, without quotes.
> > (Oberve that the value is left-aligned. This is expected.)
> 
> 2.3 Change format A2 to "Standard" or any numeric format.
> A2 is still left aligned and "123" is still text.
> 
> No bug here. Changing cell format never affect cell content.

Why should the cell format never affect the cell content?  If I decide after typing some characters to mark them as text then I would expect that it is text and that I don't have to type it again to get the same behaviour as it would be if I would have marked the cell as text before typing?
Comment 4 Kevin Suo 2015-12-30 14:49:26 UTC
(In reply to GerardF from comment #2)
Well, please let some other excel/Calc expert or daily spreadsheet users comment before close this bug as NOTABUG.

I Microsoft Excel this works perfect, but does not work in Calc. I will do some investigation to see if this is a regression - It should have worked in old LibreOffice versions.

Mark as ENHANCEMENT UNCONFIRMED for now
Comment 5 GerardF 2015-12-30 16:21:50 UTC
(In reply to Kevin Suo from comment #4)

> In Microsoft Excel this works perfect.

Changing format have never convert numeric to text (or reverse), neither in Excel nor in other spreadsheet software.

But I let open as asked.
Comment 6 Kevin Suo 2015-12-31 00:59:18 UTC
Created attachment 121654 [details]
test file: values in column A should align left as they are formatted as "TEXT"

(In reply to GerardF from comment #5)
I am not saying that format a cell as "text" should change the value of the cell. I am saying "Cell With Numeric Values Formatted As "TEXT" Should Align Left".

Yes, the values are numeric, but they are formatted as TEXT, so they should align left by default.
Excel is doing the correct behaviour.
Comment 7 Kevin Suo 2015-12-31 01:02:58 UTC
Created attachment 121655 [details]
Excel and Calc compare
Comment 8 MM 2015-12-31 12:11:19 UTC
Well 'text' formatted cells should take the input as-is, no matter what. So calc shouldn't filter them for 'numbers'.
Comment 9 Yogesh Desai 2016-01-05 18:43:58 UTC
Created attachment 121738 [details]
I have submitted the patch which solves this issue.

I have submitted the patch which solves this issue.
Comment 10 Yogesh Desai 2016-01-05 18:57:16 UTC
Created attachment 121739 [details]
Type the numbers in cell & then change format as Text

1. Type 123 in A1 while format is General.
2. Type 123 in B1 and then make format as Text (Check side Bar which shows the same for B1)

I have submitted the patch which accomplish expected behaviour of Left-alignment for "Text" Format.
Comment 11 Kevin Suo 2016-01-08 01:50:23 UTC
Just FYI, the patch submitted by Yogesh Desai is:
https://gerrit.libreoffice.org/#/c/21134/

It is currently under code review.
Comment 12 Commit Notification 2016-01-20 17:12:30 UTC
Yogesh Desai committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=812ebe795219966a05baca5719b528c186a0baf6

tdf#96822 : Make cell contents left align when number formatting is text

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2016-01-20 17:20:18 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e172d2e300e600d53975a39161ceed0c7158e1e0

nitpicks, tdf#96822 follow-up

It will be available in 5.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 14 Kevin Suo 2016-02-05 02:40:04 UTC
Any plan to apply this to 5.1 branch?