Page 1 of 1
How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 5:29 pm
by solid1
I want to sort a column which has numbers with alphabethic prefixes, of various length, by numerical content.
Like
- C1
C3
C4
C6
C7
C8
C9
C10
C11
C12
C13
C14
C15
C16
C17
C18
C19
C20
C21
C22
C23
C26
C27
and not like
- C1
C10
C11
C12
C13
C14
C15
C16
C17
C18
C19
C2
C20
C200
C201
C202
C203
C204
C205
C206
C207
C208
C209
C21
C210
C211
C212
C213
C214
C215
C216
C217
C218
C22
C23
C24
C25
Re: How do I sort numerically with alpaha prefix?
Posted: Mon Feb 02, 2015 7:13 pm
by RusselB
I don't think this can be done with the entries as you have them.
You can, use a couple of helper columns to achieve this.
If your data is in column A, then enter =left(A1;1) in B1 and =mid(A1;2;len(A1)) in C1
Then copy & paste those formulas for each entry in column A
Next go to Data -> Sort and select column B as your primary key and column C as your secondary key.
Ensure that column A is part of the sorting range, but not selected as a sort key.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 8:30 pm
by solid1
The prefix can be several characters long.
I too, think this cannot be done unless you split the data, taking off the prefixes, which is a chore.
The correct sorting in the example was furnished by a file with an .xlsx extension, so there is at least one similar program
that can do this sorting.
This seems like a simple thing, and would perhaps be a new feature in the program. "Sort by number ignoring alphabetic characters"
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 8:33 pm
by Lupp
I cannot tell for AOO 4.1 because it's not yet present on my computer. My OpenOffice 3.2 does not offer an appropriate option.
LibreOffice, at least from V3.6.5 upward can do that for you. There is an option 'Enable natural sort' under the 'Options' tab of the 'Data' > 'Sort' dialogue.
solid1 wrote:The prefix can be several characters long.
I too, think ...
Please consider posting an example more clearly presenting what you expect.
Sorting data of type 'Text' will constitute lexicographical order (case senstivity aside) regularly. The 'natural sort' is a violation of the rule and should not be stressed without an urgent need. The one (or the team) making the design decisions should consider changing the encoding of any information leading to the "C2 > C10 problem". It's long enough wll known.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 8:40 pm
by solid1
I will try LibreOffice.
I stopped using it, as it had some unforgivable bugs. (like not being able to; Edit > Fill > Series > Endvalue ... on a column, adding something after the last value, filling the remainder of 1^6 column with junk.) OpenOffice dos not do this.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 9:12 pm
by solid1
The prefix can be a single character or several, so the sorting could (should?) be first by the first letter, ignoring the letters following, and then by number.
BAR1
BAR2
BAR3
BAR20
BAR30
BRA1
BRA2
BRA3
BRA20
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
D2
D3
D12
D22
D31
DA1
DAG1
...
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 9:23 pm
by Lupp
solid1 wrote:{LibreOffice} had some unforgivable bugs ...
I noticed "unforgivable bugs" in any software I used up to now and starting in the 1960es (with some compilers for FORTRAN or ALGOL) when there was next to nothing like a readymade software at all. Programs I had designed and coded myself contained bugs, of course, too.
What you mentioned as an example of a top score bug is negligeable for me. I didn't notice it as I never use that tool in Calc. (Longing for full control I nearly always create my series by formulae.)
I came about some other bugs (one of them related to sorting and meanwhile fixed) in LibreOffice Calc, that are really annoying to me - and were under some circumstances endangering the reliability of my results. (I found a workaround.) Millions of users won't have noticed them.
"{Fact} ... is that "basic and annoying" is in the eye of the beholder. ..." as 'LouS39', a contributor to another forum on LibrteOffice stated.
By the way: When a nephew of mine studied computer science in the mid 1980es he was told that the OS/360 for mainframe systems by IBM, still in use at the time, on whose working relied truely a lot (maybe even the survival of mankind) had collected a list of about 100 000 bugs reported, confirmed - and not fixed. The main advice was: Don't use features you cannot trust in.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 9:29 pm
by solid1
"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 9:43 pm
by karolus
solid1 wrote:"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.
But it don't ignore the [optional] second and third letters!!?
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 9:50 pm
by Lupp
solid1 wrote:... so the sorting could (should?) be first by the first letter, ignoring the letters following, and then by number.
solid1 wrote:"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.
With regard to your example
Code: Select all
BAR1
BAR2
BAR3
BAR20
BAR30
BRA1
BRA2
BRA3
BRA20
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
D2
D3
D12
D22
D31
DA1
DAG1
this is in contradiction. Simply sorting it by > 'Data' > 'Sort...', the option 'natural sort' chosen' it will result in
Code: Select all
BAR1
BAR2
BAR3
BAR20
BAR30
BRA1
BRA2
BRA3
BRA20
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
D2
D3
D12
D22
D31
DA1
DAG1
whilst your quoted "specification" should aim at
Code: Select all
BAR1
BRA1
BAR2
BRA2
BAR3
BRA3
BAR20
BRA20
BAR30
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
DA1
DAG1
D2
D3
D12
D22
D31
How to understand?
(This post, again, crossed another one. This time by @karolus.)
Maybe I will add another statement later.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 10:04 pm
by Villeroy
Lupp wrote:How to understand?
Another half-heartedly implemented new feature in LO.
The "semi-natural" sort by first letter and trailing number goes like this:
X1: =SEARCH("[:digit:]";A1)
Y1: =LEFT(A1;1)
Z1: =VALUE(MID(A1;X1;LEN(A1)))
Sort by Y and Z
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 10:16 pm
by RusselB
The attached spreadsheet, to the best of my ability to tell, does the job requested.
Note: Formulas use regex, thus the checkbox to allow regex in formulas (located at Tools -> Options -> OpenOffice Calc -> Calculate) must be checked.
The sorting order is column B, column C, column A
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 10:35 pm
by karolus
Villeroy wrote:
Another half-heartedly implemented new feature in LO.
Any suggestion to implement it "full-heartly" in
general ?? -- No? - so don't grumble about!!
Karolus
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 10:39 pm
by Lupp
@Villeroy Did you read my post? "How to understand?" was about the contradiction in @solid1 'es statements.
What is "half-hearted" in the implementation of the 'natural sort' so far? It does exactly what one would expect having understood the concept of a lexicographical sort and its feasible modification with respect to numerics of a not fixed number of digits. Just an opportunity to bash against out of a mood? There are issues with LibO (Calc) but here we haven't a valid example.
I was actually interested in clearing the concept and in finding good advice for 'solid1', thinking he should try hard to get better codes instead of looking for a workaround every other day. I will leave this thread now.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 11:13 pm
by Villeroy
Sorry, I got it wrong. LO implemented natural sorting properly.
Re: How do I sort numerically with alpha prefix?
Posted: Mon Feb 02, 2015 11:13 pm
by solid1
Lupp wrote:solid1 wrote:... so the sorting could (should?) be first by the first letter, ignoring the letters following, and then by number.
solid1 wrote:"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.
With regard to your example
Code: Select all
BAR1
BAR2
BAR3
BAR20
BAR30
BRA1
BRA2
BRA3
BRA20
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
D2
D3
D12
D22
D31
DA1
DAG1
this is in contradiction. Simply sorting it by > 'Data' > 'Sort...', the option 'natural sort' chosen' it will result in
Code: Select all
BAR1
BAR2
BAR3
BAR20
BAR30
BRA1
BRA2
BRA3
BRA20
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
D2
D3
D12
D22
D31
DA1
DAG1
whilst your quoted "specification" should aim at
Code: Select all
BAR1
BRA1
BAR2
BRA2
BAR3
BRA3
BAR20
BRA20
BAR30
BRA30
C1
C2
C3
C10
C11
C12
C20
C30
D1
DA1
DAG1
D2
D3
D12
D22
D31
How to understand?
(This post, again, crossed another one. This time by @karolus.)
Maybe I will add another statement later.
Yes, I was fuzzy. My example is correct. The letters needs proper sorting too. Each one is a different animal with its own sub-number.
Re: How do I sort numerically with alpha prefix?
Posted: Tue Feb 03, 2015 11:21 am
by Villeroy
You can install LibreOffice and use the "natural sort" option or use something like this:
X1: =SEARCH("[:digit:]";A1)
Y1: =LEFT(A1;X1-1)
Z1: =VALUE(MID(A1;X1;LEN(A1)))
It would be more adequate to store the animal names and the numeric subcategory in a separate columns.
Re: How do I sort numerically with alpha prefix?
Posted: Tue Feb 03, 2015 3:52 pm
by solid1
Thanks all for your input.
The numbers are from a schematic diagram, and the numbers are component designators from a schematic in a bill of materials, splitting them up is not an option.
LibreOffice as you say has this feature working perfectly, but I prefer OpenOffice as it does not have some very severe flaws of LO, which makes me avoid it, but in this case, I will have to use a second program to sort naturally.
It would appear to be a "no-brainer" that Natural Sorting feature should also be incorporated in OpenOffice.
Re: How do I sort numerically with alpha prefix?
Posted: Tue Feb 03, 2015 4:01 pm
by Villeroy
solid1 wrote:It would appear to be a "no-brainer" that Natural Sorting feature should also be incorporated in OpenOffice.
This will not happen until somebody actually does it. There are too many complainers and not enough coders.
Re: How do I sort numerically with alpha prefix?
Posted: Tue Feb 03, 2015 4:43 pm
by RusselB
The numbers are from a schematic diagram, and the numbers are component designators from a schematic in a bill of materials, splitting them up is not an option.
The spreadsheet I posted only splits the information in order to generate a couple of helper columns.
The original data (column A in my spreadsheet) ends up being sorted, but other than that is unmodified.
If you don't want the extra columns to be seen, you can hide them with no additional effect.