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.