One for the Excel experts

[Spacecadet]

That won't work unless the only difference between the IP adresses is in the 4th part. If that's the case then it's good enough, but you might need to sort on more than 10 digits.

Edit: looks it won't work at all

If you only need to sort those addresses once, then the cvs file solution is probably the easiest. But if you want to make it automatic, you will most likely have to write some VBA code.

it would work if each of the parts was padded to 3 digits. eg:
192.168.0.1 becomes 192.168.000.001 becomes 192168000001

easily done with:
=RIGHT("000" & LEFT(A1,SEARCH(".", A1)-1), 3) & RIGHT("000" & MID(A1, SEARCH(".", A1)+1, SEARCH(".", A1, SEARCH(".", A1)+1)- SEARCH(".", A1)-1), 3)&RIGHT("000" & MID(A1, SEARCH(".", A1, SEARCH(".", A1)+1)+1, SEARCH(".", A1, SEARCH(".", A1, SEARCH(".", A1)+1)+1) - SEARCH(".", A1, SEARCH(".", A1)+1)-1), 3) & RIGHT("000" & MID(A1, SEARCH(".", A1, SEARCH(".", A1, SEARCH(".", A1)+1)+1)+1,3), 3)

[Pondlife]

you want them in date order?

,... pedantically yours.....

<pedant>
Surely you mean numerical order as opposed to lexicographic order?
</pedant>

[richard-af]

you want them in date order?

,... pedantically yours.....

One has to second-guess the challenged User!

[robnjc]

Any ideas how to order it differently so it lists the addresses in chronological order? I've tried changing the format of the number and there's no option for IP addresses..

Thanks in advance

you want them in date order?

,... pedantically yours.....

[richard-af]

I'd use a left join.

You are very naughty.

[bored]

You need to remove the dots and pad the numeric value to 10 digits
and convert to a number. stick this formula below in another column
where A1 = start of IP addresses. then sort by this column.

=VALUE(LEFT((SUBSTITUTE(A1,".",""))&REPT("0",10),1 0))

That won't work unless the only difference between the IP adresses is in the 4th part. If that's the case then it's good enough, but you might need to sort on more than 10 digits.

Edit: looks it won't work at all

If you only need to sort those addresses once, then the cvs file solution is probably the easiest. But if you want to make it automatic, you will most likely have to write some VBA code.

[DimPrawn]

I'd use a left join.

[Bright Spark]

You need to remove the dots and pad the numeric value to 10 digits
and convert to a number. stick this formula below in another column
where A1 = start of IP addresses. then sort by this column.

=VALUE(LEFT((SUBSTITUTE(A1,".",""))&REPT("0",10),1 0))

[Jog On]

legend

[richard-af]

Copy-n-paste IPs into Notepad. Use Ctrl-H to change all dots to commas.
Save as xxx.csv
Open xxx.csv in Excel
Create new column, and set it to ColA & "." & ColB & "." & ColC. Drag-n-copy down the page. Copy and Paste Special as Values.
Delete original Cols A, B & C.
You now have new Col A, which is a list of IPs without the last octet. That's in Col B.
Select entire page, sort by ColA/ColB.
In an empty Col, set it to be ColA & "." & ColB. Drag-n-copy down the page. Copy and Paste Special as Values.

Et, voila!

[Jog On]

Got a spreadsheet full of IP addresses that someone before me ordered by ascending numbers. Unfortunately instead of looking like:

192.168.0.1
192.168.0.2
192.168.0.3
192.168.0.4
192.168.0.5
etc etc

It's ordered it like this:

172.17.90.1
172.17.90.10
172.17.90.100
172.17.90.101
172.17.90.102
172.17.90.103
172.17.90.104
172.17.90.105
172.17.90.106
172.17.90.107
172.17.90.108
172.17.90.109
172.17.90.11
172.17.90.110
172.17.90.111
172.17.90.112

Any ideas how to order it differently so it lists the addresses in chronological order? I've tried changing the format of the number and there's no option for IP addresses..

Thanks in advance