sorting numbers as 1.1,1.9, 1.10 - instead of 1.1,1.10, 1.9
eusgfo
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
additional column containing then length of each cell contents, using
the LEN() function. Then sort by two conditions, length and value.
Jerry
Fred Smith
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))
--
Regards,
Fred
Please reply to newsgroup, not e-mail
"eusgfo" <wool...@hotmail.com> wrote in message
news:096f01c3854d$3c6788a0$a101...@phx.gbl...
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
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
>>when I sort this now, I get: 1.1, 1.10, 1.11, 1.12, 1.2,
>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
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