I'm keep track of static IP address assignments using a Numbers spreadsheet. It would be easier to use if I could have the columns sorted by ip address in the correct order, but it sorts it alphabetically.
Ideas?
MacBook Air, OS X Mavericks (10.9.3), 8GB RAM 256MB SSD Hard Drive
This is what I have seen done in Excel, but I wanted to avoid it. It's definitely easier to separate the address into octets and sort, but it makes data entry more difficult.
What ended up doing fits my needs since all of the hosts are on the same subnet, was to make the complete address one field, and the last octet another field which I could use for sorting. This would not hold up where there are multiple subnets, but it works for what I need it to. Thx for the replies.
I would use an extra column that take the 4 values and creates a single number like this:
B1=FIND(".", A1, 1)
C1=FIND(".", $A1, B1+1)
D1=FIND(".", $A1, C1+1)
E1=MID($A1, 1, B1−1)
F1=MID($A1, B1+1, C1−(B1+1))
G1=MID($A1, C1+1, D1−(C1+1))
H1=MID($A1, D1+1, E1−(D1+1))
I1=256×(256×(256×E1+F1)+G1)+H1
select B1 thru I1, copy
select B1 thru the end of column I, paste
sort on column I
you may hide, if you want, columns B thru H
How about something equally mad without the extra columns…
Export to CSV, run 'sort' in Terminal on that file, open new CSV.
Command adapted from http://larsmichelsen.com/open-source/quickie-sort-ip-addresses-on-linux-command- line-bash/
temp.csv is a file in your home directory (or current working dir)
temp2.csv is created (overwritten each time)
sort -t . -k 1,1n -k 2,2n -k 3,3n -k 4,4n temp.csv > temp2.csvInput - temp.csv
192.168.7.1,whr,,,
192.168.0.03,ysfas,,,
172.1.128.200,asdasd,,,
10.01.22.03,ytythth,,,
10.1.22.4,ddjy,,,
192.168.7.4,feffse,,,
192.168.8.3,xcvdv,,,
192.168.07.02,whr,,,
172.5.128.200,ewrvd,,,
192.168.10.4,qw,,,
Output - temp.csv
10.01.22.03,ytythth,,,
10.1.22.4,ddjy,,,
172.1.128.200,asdasd,,,
172.5.128.200,ewrvd,,,
192.168.0.03,ysfas,,,
192.168.7.1,whr,,,
192.168.07.02,whr,,,
192.168.7.4,feffse,,,
192.168.8.3,xcvdv,,,
192.168.10.4,qw,,,
I suppose it could be turned into an Automator action or shell script.
Data entry should be no harder than before. The extra columns can be at the very end after whatever actual data exists. IF you copy and paste then you would place the extra columns after the last real data.
Glad you found something that works
Hi AxeBox,
Could you give an example of how you would like addresses to sort?
quinn
How can I correctly sort a spreadsheet by ip address?
