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 <gcatl...@yahoo.co.uk> wrote:
Subject: Re: [libreoffice-users] sorting in calc To: "Gary Collins" <gcatl...@yahoo.co.uk>, "Dries Feys" <dries.f...@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.f...@tvh.com> wrote: Subject: Re: [libreoffice-users] sorting in calc To: "Gary Collins" <gcatl...@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 <gcatl...@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+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 -- **** 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+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