At 11:30 28/06/2021 +0100, David Deeks wrote:
Thanks very much Brian - almost completely sorted!
I have a few exceptions where the contents of the field are not
simple numbers. They indicate a small range thus e.g. 2-3, 3-4, or
an either/or thus e.g. 2/3, 3/4.
In both cases they show the correct result 2-3 or 2/3 in the text to
columns overlay screen, but now give these results in the spreadsheet?
2-3 or 2/3:
442573-4 or 3/4:
44289
Have you missed out the exposition you were intending to give here?
Is there a cell format I can use that would work correctly for these?
There is a sense that you are expecting magic here: you want a format
that will "work correctly" without explaining what you mean by that.
Those rogue values that you are describing as "not simple numbers"
are indeed not numbers at all. Remember also that formatting affects
the display of values in cells - *not* the values themselves. If you
think "2-3" is a number, please tell me its square root. Or, in a
numerically sorted list, where does it come relative to "2.1" or to
"2/3" or to "1-2.5"?
If you want to sort values that are not numbers, you can sort them
all as text. That means that "2.1" sorts between "2-3" and "2/3" -
oh, and "3" sorts after "20" and "200", because "3" sorts after "2".
Is that what you want?
What do you consider "correct"?
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
