sorting numbers as 1.1,1.9, 1.10 - instead of 1.1,1.10, 1.9

[eusgfo]

How can I format the cells, or write a function to sort
numbers as follows:
1.1
1.2
....
1.9
1.10
1.11
1.12

when I sort this now, I get: 1.1, 1.10, 1.11, 1.12, 1.2,
etc.

Thanks!

 

[Jerry W. Lewis]

If these are text values rather than numeric values, you could create an
additional column containing then length of each cell contents, using
the LEN() function. Then sort by two conditions, length and value.

Jerry

 

[Fred Smith]

One option is to number your points properly, eg 1.01, 1.02, etc.

Assuming this is out of the questions for whatever reason, you could try
creating a helper column, then sorting on it. For example:

=if(len(a1)>3,a1,left(a1,2)&"0"&right(a1,1))

 

[Harlan Grove]

If these are text values rather than numeric values, you could create an
additional column containing then length of each cell contents, using
the LEN() function. Then sort by two conditions, length and value.

....

LEN() won't help with, e.g., 10.9 and 9.10. The only sensible way to sort
this sort of pseudonumeric text is as multiple fields separated by periods.
If there's only one period, then it's possible to dummy up numeric values
that reflect the intended ordering. Something like

=LEFT(X,FIND(".",X)-1)+TEXT(MID(X,FIND(".",X)+1,4),"\.0000")

 

[GB]

How can I format the cells, or write a function to sort
numbers as follows:
1.1
1.2
...
1.9
1.10
1.11
1.12

when I sort this now, I get: 1.1, 1.10, 1.11, 1.12, 1.2,
etc.

Out of interest, Eusgfo, how do you enter these numbers in the first place?
I would have thought that Excel would not distinguish between 1.1 and 1.10.
Are you entering a ' before you enter the number?

Geoff

 

[Lee Garrett]

-----Original Message-----
How can I format the cells, or write a function to sort
numbers as follows:
1.1
1.2
....
1.9
1.10
1.11
1.12

when I sort this now, I get: 1.1, 1.10, 1.11, 1.12, 1.2,
etc.

Thanks!
.
Isn't that what you're supposed to be getting with your

sort? Seems to me you are sorting properly and getting
the correct response. (Or, what am I missing?)

 

[Harlan Grove]

....
Isn't that what you're supposed to be getting with your
sort? Seems to me you are sorting properly and getting
the correct response. (Or, what am I missing?)

That's what the OP should be getting if these were *numbers*, but since
there's both a 1.1 and 1.10, and they're presumably distinct, it's a simple
inference that these are text, perhaps chapter.section labels in a document,
so 1.1 should be considered 1.01, 1.2 -> 1.02, and 1.10 -> 1.10.

 

[Bernd Plumhoff]

1. Enter User-defined functions (see below) into Visual Basic Editor.
2. Insert additional column into your spreadsheet.
3. Enter =stufnr2zahl(A1) into new column (A1 refers to your column with
1.1, 1.10, you may even have 1.1.1.1, 1.1.1.2 etc.)
4. Sort spreadsheet due to new column.That's it.
5. If code is a miracle, learn German (to understand comments) or ask me.

Despite the fact that I tested my code, I cannot and I will not give any
warranty or any guarantee for its function, for its quality or for absence
of malfunction or absence of viruses. Remember: It's free

Kind regards,
Bernd Plumhoff

PS: Yes, this is a worksheet function newsgroup. I know - but: I could not
see an easy approach for the general solution without macros. And: You use
them as functions

------------------- Code below -------------------------------

Private Const delim As String = "."
Private Const basis As Double = 10000
Private Const logbasis As Long = 4



Public Function stufnr2zahl(stufnr As String) As Double

' stufnr2zahl wandelt den String stufnr in eine Double Zahl um.
' Beispiel: Bei gegebenem Trennzeichen "." und Basis 100 ist
stufnr2zahl("4.33.12.1") = 4,331201
' Mit dieser Hilfe kann nach den gegebenen Strings hierarchisch sortiert
werden.

Dim strtmp As String ' Hilfsvariable zur Untersuchung von stufnr
Dim delimlen As Integer ' Zur Ermittlung der Zahl rechts hinter dem jeweils
letzten Trennzeichen

stufnr2zahl = 0
strtmp = Trim(stufnr) ' Leerzeichen links und rechts weg

loop1:

' Wir gehen von rechts nach links durch stufnr durch und schieben die
erkannten Teilzahlen versetzt ins Ergebnis

delimlen = InStr(1, StrReverse(strtmp), delim, vbTextCompare) ' Position des
Trennzeichens von rechts gesehen?

If delimlen > 0 Then ' Trennzeichen gefunden

stufnr2zahl = stufnr2zahl / basis + Right(strtmp, delimlen - 1) ' Rechte
Teilzahl ins Ergebnis nehmen
strtmp = Left(strtmp, Len(strtmp) - delimlen) ' Rechte Teilzahl
herausnehmen
GoTo loop1 ' Weiter untersuchen

End If

stufnr2zahl = stufnr2zahl / basis + strtmp ' Restzahl ins Ergebnis nehmen

End Function

Public Function stufnr2normstr(stufnr As String) As String

' stufnr2normstr wandelt den String stufnr in einen String mit normierten
Hierarchiestufen um.
' Beispiel: Bei gegebenem Trennzeichen "." und logbasis 2 ist
stufnr2normstr("4.33.12.1") = "04331201"
' Mit dieser Hilfe kann nach den gegebenen Strings hierarchisch sortiert
werden.

Dim strtmp As String ' Hilfsvariable zur Untersuchung von stufnr
Dim delimlen As Integer ' Zur Ermittlung der Zahl rechts hinter dem jeweils
letzten Trennzeichen

stufnr2normstr = ""
strtmp = Trim(stufnr) ' Leerzeichen links und rechts weg



' Wir gehen von rechts nach links durch stufnr durch und schieben die
erkannten Teilzahlen versetzt ins Ergebnis

delimlen = InStr(1, StrReverse(strtmp), delim, vbTextCompare) ' Position des
Trennzeichens von rechts gesehen?

Do While delimlen > 0 ' Trennzeichen gefunden

stufnr2normstr = Format(Right(strtmp, delimlen - 1), String(logbasis,
"0")) & stufnr2normstr ' Rechte Teilzahl ins Ergebnis nehmen
strtmp = Left(strtmp, Len(strtmp) - delimlen) ' Rechte Teilzahl
herausnehmen
delimlen = InStr(1, StrReverse(strtmp), delim, vbTextCompare) ' Position
des Trennzeichens von rechts gesehen?

Loop

stufnr2normstr = Format(strtmp, String(logbasis, "0")) & stufnr2normstr '
Restzahl ins Ergebnis nehmen

End Function

 

[eusgfo]

I renumbered the points - I guess it was a case of not
being able to see the forest for the trees.

Thanks!