Sorting Numbers

[Donbo]

I use exel to keep track of IP addresses. How do I sort on a column so the
numbers will be decending I use text or general and it will put
1,11,12,13...2,20,21 and so on. Would like to have it sort 1,2,3,4, and so on.

 

[Gord Dibben]

IP addresses are not numbers.

They are text and sort like text which is not the same as number sorting.

You must extract part of the IP into another column and sort on that column.


Gord Dibben MS Excel MVP

 

[David Biddulph]

Data/ Text to Columns ... may be a handy way of splitting up the IP text
into separate columns which can be treated as numbers.

 

[Ron Rosenfeld]

I use exel to keep track of IP addresses. How do I sort on a column so the
numbers will be decending I use text or general and it will put
1,11,12,13...2,20,21 and so on. Would like to have it sort 1,2,3,4, and so on.

You have to convert the IP addresses into numbers, and then sort on that. You
can do this with a formula in a "helper column" adjacent to your table. Then
you sort on the helper column. You can hide (or delete) the helper column.

A worksheet formula to convert your IP address in C1 into a number is:

=TEXT(LEFT(C1,FIND(".",C1)),"000")&TEXT(MID(C1,FIND(".",C1)+1,FIND(CHAR(1),
SUBSTITUTE(C1,".",CHAR(1),2))-FIND(".",C1)),"000")&TEXT(MID(C1,FIND(CHAR(1),
SUBSTITUTE(C1,".",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(C1,".",CHAR(1),3))-
FIND(CHAR(1),SUBSTITUTE(C1,".",CHAR(1),2))),"000")&TEXT(RIGHT(C1,LEN(C1)-
FIND(CHAR(1),SUBSTITUTE(C1,".",CHAR(1),3))),"000")

OR, you can download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/morefunc/english and use this shorter **ARRAY**
formula:

=MCONCAT(TEXT(REGEX.MID(C1,"\d+",ROW($1:$4)),"000"))

To enter and **ARRAY** formula you must hold down <ctrl><shift> while hitting
<enter>. Excel will place braces {...} around the formula.
--ron