Thanks, yes, theres some really helpful info in this reply. I had thought of using a lower value but it hadnt occurred to me to hide it using formatting (and of course i didnt want it to show). The methods of converting to text are very useful to know. In the end i did indeed use the TEXT function in a formula, and conditionally substituted a blank where the string was shorter. Again not exactly elegant but it works and thats the main thing. It would be helpful to be able to define a nonstandard sort order but i dont think thats easily possible in a case like this (?: but im very willing to be proved wrong on that đ) Again i think i'd be into the realm of macros!
Thanks G. -------------------------------------------- On Thu, 9/3/17, Brian Barker <b.m.bar...@btinternet.com> wrote: Subject: Re: [libreoffice-users] Re: sorting in calc To: users@global.libreoffice.org Cc: "Gary Collins" <gcatl...@yahoo.co.uk> Date: Thursday, 9 March, 2017, 23:17 At 18:59 08/03/2017 +0000, Gary Collins wrote: >Is there a character i can enter into a cell >that will come *before* numbers in the sort >order? I mean, before other cells that actually >contain numbers rather than numerical strings What about some value less than any of your real data are (or can be)? What is the range of your numerical values? In practice, this is unlikely to span the entire range that Calc can handle. If your values are all positive and non-zero, for example, then zero will do. Otherwise a sentinel value such as -999 might suffice. If you don't want this number to appear in your list, you can hide it using Conditional Formatting with a cell style having its font colour set to white (or whatever is your cell background colour). Alternatively, you can set the cell format of your range itself to something like [WHITE][<=0]0;0 . But the number would still be there, of course, so you'd have to take care if you did any calculations on the range such as AVERAGE(). At 09:12 09/03/2017 +0000, Gary Collins wrote: >Maybe it might work if i can convert all the >relevant numbers i have entered to text but im >still looking for a way to do that. >Format>cells>numbers and selecting text doesn't do it, ... No: changing format of cells already containing values never changes the stored values themselves. But there are two alternative easy ways to do this: o In another column, enter =TEXT(Xn;"0") (or whatever is a suitable format string). o Fill down the column. o Select the new values and cut. o Paste them over the originals, but using Edit | Paste Special... (or Ctrl+Shift+V), selecting Numbers but not Formulae in the Paste Special dialogue. o Select the range of values. o Go to Data | Text to Columns... . o In the Text to Columns dialogue, under Fields, click on the column header (probably currently Standard). o In the "Column type" drop-down, select Text. o OK. Voilà ! At 10:15 09/03/2017 +0000, Gary Collins wrote: >Ive managed to solve my problem in a roundabout >way. To give the context, i have a column >containing strings of length 1 to 3 which may >contain nonstandard characters and which i must >sort in a specific order (not the usual >alphabetic order). To achieve this i have a >table assigning a number (col B) to each char >(col A) in sheet 2, defined as a range 'xlit'. So this table must indeed have a small, finite range of values (presumably integers) in column B. Surely your lookup formula just needs to ascribe some numeric value outside and below this range instead of the blank character or empty cell that you may have at present? I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted