Re: [libreoffice-users] Re: sorting in calc

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.barker@btinternet.com> wrote:

 Subject: Re: [libreoffice-users] Re: sorting in calc
 To: users@global.libreoffice.org
 Cc: "Gary Collins" <gcatlast@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+unsubscribe@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+unsubscribe@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