How do I sort numerically with alpha prefix?

[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

[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.

[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"

[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.

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.

[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.

[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
...

[Lupp]

{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.

[solid1]

"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.

[karolus]

"Enable natural sort" in LibreOffice 4.4.0.3 does work perfectly.

But it don't ignore the [optional] second and third letters!!?

[Lupp]

... so the sorting could (should?) be first by the first letter, ignoring the letters following, and then by number.

"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.

[Villeroy]

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

[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

[karolus]

Another half-heartedly implemented new feature in LO.

Any suggestion to implement it "full-heartly" in general ?? -- No? - so don't grumble about!!

Karolus

[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.

[Villeroy]

Sorry, I got it wrong. LO implemented natural sorting properly.

[solid1]

... so the sorting could (should?) be first by the first letter, ignoring the letters following, and then by number.

"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.

[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.

[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.

[Villeroy]

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.

[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.