Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.