Bug 144986 - Formula to Value for a formula with an empty string result creates a non-empty but 0-length text cell. How to search such cells should be documented in the help.
Summary: Formula to Value for a formula with an empty string result creates a non-empt...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
6.4.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:25.2.0
Keywords:
Depends on:
Blocks: Find-Search
  Show dependency treegraph
 
Reported: 2021-10-07 21:04 UTC by LeroyG
Modified: 2024-07-22 12:21 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description LeroyG 2021-10-07 21:04:44 UTC
Description:
When a formula result is "", Formula to Value renders the cell content as an empty paragraph (<text:p/>), not as an empty cell.

Steps to Reproduce:
1. In A2 type =""
2. Select A2, choose menu Data - Calculate - Formula to Value
3. Press Ctrl+End (Moves the cursor to the last cell on the sheet that contains data.)

Actual Results:
Last cell is A2.

Expected Results:
Last cell is A1.


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.0.6.2 (x86)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win
Locale: es-MX (es_MX); UI: en-US
Calc: threaded
Comment 1 m_a_riosv 2021-10-08 00:08:43 UTC
I don't agree, to change the value, for me it's fine, it is not an empty cell, with the same value before the operation.

 <table:table table:name="Hoja1" table:style-name="ta1">
    <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
    <table:table-row table:style-name="ro1">
     <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p/>
     </table:table-cell>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
      <text:p>1</text:p>
     </table:table-cell>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float">
      <text:p>2</text:p>
     </table:table-cell>
    </table:table-row>
    <table:table-row table:style-name="ro1">
     <table:table-cell table:formula="of:=IF([.B5]=0;&quot;&quot;;1)">
      <text:p/>
     </table:table-cell>
    </table:table-row>
   </table:table>
Comment 2 Eike Rathke 2021-10-08 11:31:57 UTC
Strictly spoken the behaviour is correct, but the result of an empty text content should be searchable (it might not be because such can't be entered via UI). If an empty string was replaced with a blank cell, semantics would change like

 A1: `=""`
 A2: `=A1+2`
 => `#VALUE!` (if strict text conversion is enabled)

With A1 being empty the result would change to 2.
Comment 3 Eike Rathke 2021-10-08 11:36:40 UTC
Or a simple `=A1` would change from empty display string to 0, or `ISBLANK(A1)` change from FALSE to TRUE.
Comment 4 LeroyG 2021-10-08 16:50:55 UTC
Thanks Miguel and Eike.

After reading comments 1 to 3, I agree that this type(?) of content "should be searchable" (comment 2).

So, what is next? Change the bug title to reflect that? Close this bug and post an enhancement request for the search issue?

After apply Formula to Value in A1: ="" 

=ISBLANK(A1) gives FALSE.
=ISFORMULA(A1) gives FALSE.
=ISLOGICAL(A1) gives FALSE.
=ISNONTEXT(A1) gives FALSE.
=ISNUMBER(A1) gives FALSE.

=ISTEXT(A1) gives TRUE.
Comment 5 LeroyG 2021-10-08 16:58:36 UTC
=CELL("CONTENTS";A1) gives nothing
=CELL("TYPE";A1) gives "l" (for label, text)
=CELL("FORMAT";A1) gives "F0" (number without thousands separator and 0 decimal places)
Comment 6 Eike Rathke 2021-10-09 18:24:54 UTC
Adjusted summary to keep this for the non-searchable aspect.
Comment 7 Wolfgang Jäger 2023-08-13 14:33:11 UTC
Searching for regular expressions and using
^
or
$
or
f{0}
or
(foo){0}
as SearchString where f may be replaced with any single literal character or foo with any syntactically acceptable subexpression (including empty), the empty-string-cells are found.
I change it to a Documentation bug.
Comment 8 Wolfgang Jäger 2023-08-13 14:35:56 UTC
The documentation for Calc (the respective help article) may only mention the "$-case" to be simple and top avoid misunderstandings.
Comment 9 LeroyG 2023-08-13 15:15:38 UTC
(In reply to Wolfgang Jäger from comment #7)
> Searching for regular expressions and using
> ^
> or
> $
> or
> f{0}
> or
> (foo){0}
> as SearchString where f may be replaced with any single literal character or
> foo with any syntactically acceptable subexpression (including empty), the
> empty-string-cells are found.
> I change it to a Documentation bug.

^ and (){0} or .{0} works fine 
before {0}, also works / ~ ] @ # = - _ , ; : < > 
before {0}, avoid * ? \ ) ( [ | +

$ also find cells with numbers, text or formula; so, is not a good option.
Comment 10 Wolfgang Jäger 2023-08-13 15:29:44 UTC
(In reply to LeroyG from comment #9)

> 
> $ ... is not a good option.

That's correct.
^
must do. Little problem. It may not be entered on the simple keypress.
Comment 11 Olivier Hallot 2024-07-21 14:51:50 UTC
(1) https://help.libreoffice.org/master/en-US/text/scalc/01/04060110.html?DbPAR=CALC

(2) https://help.libreoffice.org/master/en-US/text/shared/01/02100001.html?&DbPAR=CALC

Proposed text in (1)

Empty string and blank cells
----------------------------
Cells with the empty string ("") are not equivalent to blank cells. When searching or calculating with text, the empty string "" is the text with length zero.

For example, when the formula in A1 returns the empty string "", then the following applies:

=ISBLANK(A1) returns FALSE. The cell is not blank.
=ISFORMULA(A1) returns TRUE. The cell has a formula.
=ISLOGICAL(A1) returns FALSE. Not a logical value
=ISNONTEXT(A1) returns FALSE. The cell has the empty string
=ISNUMBER(A1) returns FALSE. Not a number.
=ISTEXT(A1) returns TRUE. The cell has the empty string
=LEN(A1) returns 0. The length of the empty string is zero.

Proposed text in (2) (for Calc only)

Note: Cells with the empty string ("") are not equivalent to blank cells. When searching or calculating with text, the empty string "" is the text with length zero.
Comment 12 Commit Notification 2024-07-21 20:59:42 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/805241fc011cf160c349548bcb532f487aa18e38

tdf#144986 Blank cells vs. empty string.