Thanks for this Brian - feeling suitably admonished here for not being specific enough – particularly as I spent my working life designing computer business systems!
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. 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. 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 2. Is there a cell format I can use that would work as required for the above? Nervously about to hit 'send’! .... David > On 28 Jun 2021, at 13:49, Brian Barker <b.m.bar...@btinternet.com.INVALID> > wrote: > > 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 >
