Re: [libreoffice-users] sorting in calc

Hello all,
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'. On sheet 1 column A has 
strings to be sorted, columns B-D contain number to represent char 1,2,3 of the string. I obtain 
these numbers as text, using a formula of the form =IFERROR(TEXT(VLOOKUP(MID 
(Ax,y,1),xlit,2,0),"General"")," ")
where x is the row containing the string and y is 1,2 or 3 for the appropriate char of the string, 
returning the number as a string or " "if the character doesnt exist (string is of shorter length). 
I can now order my strings by sorting on columns B,C,D in that order. Perhaps there might have been 
an easier way to do it, but it works and thats what counts
Gary
----------------------------------------
On Thu, 9/3/17, Gary Collins <gcatlast@yahoo.co.uk> wrote:

 Subject: Re: [libreoffice-users] sorting in calc
 To: "Gary Collins" <gcatlast@yahoo.co.uk>, "Dries Feys" <dries.feys@tvh.com>
 Cc: "users@global.libreoffice.org" <users@global.libreoffice.org>
 Date: Thursday, 9 March, 2017, 9:12
 
 Hello Dries
 Thank you for your reply. Yes i am aware of the ascii code
 but it doesnt seem to apply when some cells contain numbers
 rather than text. For example # comes before "1"  in
 the table but it sorts after numbers. I want something i can
 put in a cell which will sort before any number. I suspect
 there isnt anything. 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 doesnt do it,
 at least not in 5.2.2.2 under windows7. The format *appears*
 to change in that the numbers become left justified like
 strings but istext (cell) still returns false....
 /G.
 --------------------------------------------
 On Thu, 9/3/17, Dries Feys <dries.feys@tvh.com>
 wrote:
 
  Subject: Re: [libreoffice-users] sorting in calc
  To: "Gary Collins" <gcatlast@yahoo.co.uk>
  Cc: "users@global.libreoffice.org"
 <users@global.libreoffice.org>
  Date: Thursday, 9 March, 2017, 8:32
  
  Gary,
  
  You could check http://www.asciitable.com/ to see the
  order.
  
  Met vriendelijke
  groeten, Salutations distinguées, Kind Regards,
  
  DRIES FEYS
  CORPORATE SERVICES • Specialist Software
  Developer
  
  TVH GROUP NV
  Brabantstraat 15 • BE-8790 WAREGEM
  T +32 56 43 42 11 • F +32 56 43 44 88 •
  www.tvh.com
  View our company movies via
  downloads on our website.
  
  
  On 8 March 2017 at 19:59, Gary Collins <gcatlast@yahoo.co.uk>
  wrote:
  > Hello,
  > 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
  >
  > Thanks
  > Gary
  >
  > --
  > 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
  
  -- 
  
  
  **** DISCLAIMER ****
  
  http://www.tvh.com/glob/en/email-disclaimer
  
  "This message is
  delivered to all addressees subject to the conditions
  set forth in the attached disclaimer, which is
  an integral part of this
  message."
  
  -- 
  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