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

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