Sorting Numbers
 

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.

Sorting Numbers
 

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

On Tue, 26 Feb 2008 13:13:02 -0800, Donbo
wrote:

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.

Sorting Numbers
 

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 26 Feb 2008 13:13:02 -0800, Donbo

wrote:

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.

Sorting Numbers
 

On Tue, 26 Feb 2008 13:13:02 -0800, Donbo
wrote:

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,FIN D(".",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),SUBST ITUTE(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