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,

We are using Office 2013 - can you point me in the direction of what you mean when you say " recent versions of Excel should be able to do this...without requiring helper formulas"...

Sorry for not clarifying the version - I found a similar question in the posts, and did not pay attention to the version referenced.

Thanks,

Tammy

Answer 4

I have Excel 2010 myself. If I let Excel sort the Survey column, it does so the way you want: 1.2.1.1 after 1.1.1.1 but before 1.10.1.1, so I don't need a helper column with formulas.

I assume that Excel 2013, being newer, should act the same.

Answer 5

Thanks very much for responding, Hans. There must be something hidden in the file that I'm not seeing because it is not sorting as you've indicated.

Thanks again, for taking the time to respond!

Tammy