One for the Excel experts

[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

[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]

legend

[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))

[DimPrawn]

I'd use a left join.

[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.

[richard-af]

I'd use a left join.

You are very naughty.

[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]

you want them in date order?

,... pedantically yours.....

One has to second-guess the challenged User!

[Pondlife]

you want them in date order?

,... pedantically yours.....

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