Created attachment 126488 [details]
Small spreadsheet

Seems I can only attach one file. I can upload the two png images when appropriate

Created attachment 126489 [details]
Image of function wizard selecting function VALUE()

Created attachment 126490 [details]
Image of cell selection and expected interim results

Initially, the bug report document implies only one file may be attached. It may be appropriate to indicate more than one document may be attached to obviate comments of the ilk "I only seem to be able to upload one document" :)

cells are formatted as text. Select cells, right click, clear direct formatting. Then it works.

In which case the error is probably in the formatting/format interpretation functionality. I had previously selected the entire column and set it to Number - general as you can see from that uploaded spreadsheet.

I have now tried selecting the two cells which could be sequentially copied to provide the Numeric - general format on the table therein and still the function remains as a text string not a formula.

I then tried selecting just the two cells as above and simply selected reset. Still no change.

I can confirm that the reformatting attempts indeed report that the two cells (the entire column) are formatted as number - general but that still doesn't prevent it from treating the contents as a text string.

Would you like me to provide a further copy of the spreadsheet upon which I have exercised the above two reformatting options or will you try it yourself on the version you already have?  Hint: to make sure we were both working with the same spread I actually downloaded that one attached to the bug report and tested your suggestion on that.

Are you merely convinced the formatting, as you have described it, works as it should or, have you actually attempted to reformat the cells directly yourself and confirmed that the reformatting produces a function rather than leaving it as a text string?

If it works for you then is it conceivable that the update of a couple of days ago simply resulted in a corrupted version for me?

Created attachment 126491 [details]
Modified original spreadsheet

The text accompanying that last upload got "dropped" by the system.
The new spreadsheet is simply the original downloaded and modified to ensure we are all on the same sheet :)
the document was created from my original large multi page spreadsheet where the error originally occurred.
I erased all but a few sample cells to demonstrate.
The erasure would undoubtedly have left formatting in the "undo/redo" buffer and the column widths remained the same. All the recommended re-formatting options suggested had already been performed and I even saved and restarted the document to ensure all buffers were cleared.
As can be seen from this later submission:-
retyping the formulae cell by cell along line one produced the erroneous results even after almost the entire sheet was formatted as Number--General.
Selecting and dragging the formulae from line one to line two produced "identical" results.
Selecting the entire line two and dragging to replicate line three produced the anticipated copy of line two.
selecting the first "text formula" on line three and dragging to the right produced the entire line of "text formulae".
Perhaps you would care to satisfy yourself as to what happens if "valid" formulae are copied to line three as appropriate.
I selected the appropriate cells and even changed the decimal values in the hope that might "force" the recognition of a change. Not much luck there!
I accept it's probably not a VALUE() function error but it is a major flaw in the formatting of repurposed cells where all user attempts are generally ignored in favour of something that may once have been.

The original sheet was four pages of 300+ rows 100+ columns with web links, typed text, copied text, calculated substring text, typed values, calculated values, accumulations and inter page references. The document is regularly updated, sorted, resorted with special sort orders and five graphic charts produced. I imagine the buffering to retain all the possible undo and redo states is potentially overwhelming the ability to accurately reset all cells when something is repurposed.

Should a new bug report be raised?

Colin, I suggest reading what Villeroy wrote here: https://forum.openoffice.org/en/forum/viewtopic.php?t=16494#p76378

(In reply to Aron Budea from comment #9)
> Colin, I suggest reading what Villeroy wrote here:
> https://forum.openoffice.org/en/forum/viewtopic.php?t=16494#p76378

Aron, thanks for the link. Not sure I fully understand the underlying logic but certainly the explanation and the examples of how to circumvent the issue makes it as clear as daylight. I also experimented and found that it is simple enough to just fully erase and retype any one cell then use ny of the normal metthods to replicate it throughout the desired range.

Thanks for your hint and also thanks to everybody else who helped.

I have marked the item as resolved