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
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;"";1)"> <text:p/> </table:table-cell> </table:table-row> </table:table>
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.
Or a simple `=A1` would change from empty display string to 0, or `ISBLANK(A1)` change from FALSE to TRUE.
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.
=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)
Adjusted summary to keep this for the non-searchable aspect.
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.
The documentation for Calc (the respective help article) may only mention the "$-case" to be simple and top avoid misunderstandings.
(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.
(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.
(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.
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.