How to sort alphanumeric fields such as 1.1.1, 1.1.2, 2.1.1,10.1.1

Question

I am trying to sort on a colum that is used to display paragraph numbers that have the format 1, 1.1, 1.1.1, 2, 2.1,2.1.1,etc

The problem is that the tenth paragraph then sorts before the second. I have seen lots of posts on the subject but none that I can make work. Any help would be appreciated. Thanks

Answer 1

Assuming that you have at most 3 levels (1.1.1) and that the numbers won't go above 99, you could do the following.

Let's say your outline numbers are in A1:A100.

In B1, enter the formula

 =10000*LEFT(A1,IF(ISERROR(FIND(".",A1)),2,FIND(".",A1)-1)) + 100*IF(ISERROR(FIND(".",A1)),0,MID(A1,FIND(".",A1)+1, IF(ISERROR(FIND(".",A1,FIND(".",A1)+1)),2,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1))) + 1*IF(ISERROR(FIND(".",A1,FIND(".",A1)+1)),0,MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,2))

Fill down to B100.

Sort on column B. (You can hide this column if you like.

If you're willing to use a custom VBA function, it could be done more efficiently.

Answer 2

On Sun, 2 Jan 2011 22:58:14 +0000, AkAlan wrote:

>

>

>I am trying to sort on a colum that is used to display paragraph numbers that have the format 1, 1.1, 1.1.1, 2, 2.1,2.1.1,etc

>

>The problem is that the tenth paragraph then sorts before the second. I have seen lots of posts on the subject but none that I can make work. Any help would be appreciated. Thanks

Will every field have three sections separated by decimals?

What is the maximum number in any field?

Assuming the answer to the first is Yes, and the answer to the 2nd is 999, you could sort on a "helper column"

If your paragraph numbers are in column A, set up a helper column with a formula like:

=LEFT(A2,FIND(".",A2)-1)*10^6+MID(A2,FIND(".",A2)+1,FIND(

".",A2,FIND(".",A2)+1)-FIND(".",A2))*10^3+TRIM(RIGHT(

SUBSTITUTE(A2,".",REPT(" ",9))))

Fill down; then sort on that column.

Answer 3

Hi Hans,

This post is close to what I would like to do, but I don't understand the "answers" at all. My user has a spreadsheet and would like to sort the values a specific way:

As you can see, the "ten" value in the middle number is not picked up as a "ten". 

Do you have any suggestions on how he can get his data sorted the way he wants?

Thanks for any feedback!

Tammy

Answer 4

Hi AkAlan & THnat, 

And also you can use the below formula for any sort what you looking for 

fill your column with data to A15

{=INDEX($A$2:$A$15,MATCH(SMALL(IF(ISBLANK($A$2:$A$15),"",IF(ISNUMBER($A$2:$A$15),COUNTIF($A$2:$A$15,"<"&$A$2:$A$15),COUNTIF($A$2:$A$15,"<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15),1,0))+1)),ROW(A1)),IF(ISBLANK($A$2:$A$15),"",IF(ISNUMBER($A$2:$A$15),COUNTIF($A$2:$A$15,"<"&$A$2:$A$15),COUNTIF($A$2:$A$15,"<"&$A$2:$A$15)+SUM(IF(ISNUMBER($A$2:$A$15),1,0))+1)),0))}

Gratefully,

Answer 5

Recent versions of Excel should be able to do this the way he wants without requiring helper formulas.

If that doesn't work, however:

Let's say your data begin in A2.

I'll assume that all entries are of the form a.b.c.d, and that a, b, c and d are either 1 or 2 digits (i.e. they are in the range 0 ... 99).

In B2, enter the formula

=10^6*LEFT(A2,FIND(".",A2)-1)+10^4*MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)+10^2*MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)+10^0*MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,2)

Fill down as far as the data in column A go.

Then sort the range in columns A and B on column B.