At 15:35 28/06/2021 +0100, David Deeks wrote:
Thanks for this Brian - feeling suitably
admonished here for not being specific enough
particularly as I spent my working life designing computer business systems!
Oh, I was not trying to be critical.
I don't require them to be numbers per se as
explained in my first email I need to be able
to sort them, but should have made it clear that
I do not need them to be actual numerics for the purposes of calculations.
Good. But "2-3" doesn't really sort anywhere
between numbers. And if you sort text, "3" comes
*after* "20". So which do you want?
So having followed your original advice with a
successful conclusion in those cases where there
are just single values in the cells, I should
have explained my follow-up query as ...
In both cases they show the required result
e.g. 2-3 or 2/3 etc in the Text to Columns
overlay screen, but now give these results, for example, in the spreadsheet?
2-3 or 2/3 now display in cells as:
44257
3-4 or 3/4 now display in cells as:
44289
etc.
Whether the format is text or number, I seem to get the same result.
Apologies that this statement was rather mangled
in transit in your previous message, so I
completely misunderstood it. But it's easy to
explain. The Text to Columns facility causes
OpenOffice to reinterpret the values in the
cells: that is how the *text* "23" becomes the
number 23. And the same thing happens to your
examples with hyphens or slashes. If the numbers
around those characters are appropriate, such
text strings typed into a cell would be
interpreted as dates and displayed as such. What
has happened here is that your values are dates
but, because your cells are formatted as number,
what is displayed is the number actually stored
in a cell for a date value - the number of days
and fractions of a day since the origin date. If
you format these cells as Date, you will see that
44257 (for 2-3 or 2/3) is 2 March 2021 and 44289
(for 3-4 or 3/4) is 3 April 2021. (This follows
the UK interpretation of shorthand dates, where
it appears you may be, which is different from the US one, of course.)
My queries I suppose have therefore become twofold ..
1 Why my required results show in the Text to
Columns overlay screen, but then appear otherwise on the spreadsheet
See above.
2. Is there a cell format I can use that would work as required for the above?
It's not a question of formatting but of the cell
*values* you have. And Text to Columns, though it
will sort your real text numbers to numeric
values, indeed messes up values that look like
shorthand dates. You may have to handle some of
this manually. But I still don't know why you
want your text values converted to numbers, as
you cannot sensibly sort "2-3" within numbers.
Where exactly do you expect it to appear?
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
