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
+, 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 +, 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 +, 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
