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.

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.

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.

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