[Solved] IP address sorting
[Solved] IP address sorting
I have searched for an answer to this question throughout this forum and on the web, and haven't been able to find an answer.
I am attempting to sort a sheet by an IP address column. Obviously it isn't working properly, for all the known reasons:
Given the following list:
10.10.10.1
10.10.10.2
10.10.10.3
10.10.10.10
10.10.10.30
10.10.10.100
Sorting by OOO Calc by default would be:
10.10.10.1
10.10.10.10
10.10.10.100
10.10.10.2
10.10.10.3
10.10.10.30
A simple solution for this would be to pad each octet with leading 0's:
010.010.010.001
010.010.010.002
010.010.010.003
010.010.010.010
010.010.010.030
010.010.010.100
But I cannot find a way to implement this. I have tried to use a custom format (000"."000"."000"."000), but that does not work as advertised.
What would the proper format for padding be?
Thanks!
I am attempting to sort a sheet by an IP address column. Obviously it isn't working properly, for all the known reasons:
Given the following list:
10.10.10.1
10.10.10.2
10.10.10.3
10.10.10.10
10.10.10.30
10.10.10.100
Sorting by OOO Calc by default would be:
10.10.10.1
10.10.10.10
10.10.10.100
10.10.10.2
10.10.10.3
10.10.10.30
A simple solution for this would be to pad each octet with leading 0's:
010.010.010.001
010.010.010.002
010.010.010.003
010.010.010.010
010.010.010.030
010.010.010.100
But I cannot find a way to implement this. I have tried to use a custom format (000"."000"."000"."000), but that does not work as advertised.
What would the proper format for padding be?
Thanks!
Last edited by Villeroy on Wed Jul 22, 2009 5:40 pm, edited 1 time in total.
Reason: tagged [Solved]
Reason: tagged [Solved]
OOo 3.1.X on Mac OSx Leopard
Re: IP address sorting
Number formats apply to numbers. Your data are text which is why they sort in alphabetical order.
Assuming your list in column A and some free column F:
Order by numeric values in M1:P1
Assuming your list in column A and some free column F:
Code: Select all
F1 =FIND(".";$A1)
G1 =FIND(".";$A1;F1+1)
H1 =FIND(".";$A1;G1+1)
I1 =LEFT($A1;F1-1)
J1 =MID($A1;F1+1;G1-F1-1)
K1 =MID($A1;G1+1;H1-G1-1)
L1 =MID($A1;H1+1;H1-1)
M1 =VALUE(I1)
N1 =VALUE(J1)
O1 =VALUE(K1)
P1 =VALUE(L1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: IP address sorting
Thanks for the quick reply Villeroy!
OK, that kind of makes sense, however, it seems that one could create custom formats for text as well....
I did get it to work this way, however, my sort function only allows up to 3 sort criteria, not 4. Not a big deal as all are in the 10.x.x.x network.
Thanks for the help!
OK, that kind of makes sense, however, it seems that one could create custom formats for text as well....
I did get it to work this way, however, my sort function only allows up to 3 sort criteria, not 4. Not a big deal as all are in the 10.x.x.x network.
Thanks for the help!
OOo 3.1.X on Mac OSx Leopard
Re: IP address sorting
Instead of manipulating the human-readable presentation as a string, how about storing the decimal dotted-quad input as the 32-bit integer it represents, then providing dotted-quad as a display format for the underlying value. Then you could do numeric and logical operations (e.g. sort, AND) on the integer value, and the textual representation would Just Work.
IPV6 addresses are longer: It's a 128 bit integer, represented for human legibility as eight four-digit hexadecimal numbers separated by colons. They could be handled by a generalization of this mechanism of separating the representation from the underlying storage.
IPV6 addresses are longer: It's a 128 bit integer, represented for human legibility as eight four-digit hexadecimal numbers separated by colons. They could be handled by a generalization of this mechanism of separating the representation from the underlying storage.
OOo 3.1.1 on Windows 7
Re: [Solved] IP address sorting
Indeed, storing 4 numbers is the way to go.bobsutt wrote:Instead of manipulating the human-readable presentation as a string, how about storing the decimal dotted-quad input as the 32-bit integer it represents, then providing dotted-quad as a display format for the underlying value. Then you could do numeric and logical operations (e.g. sort, AND) on the integer value, and the textual representation would Just Work.
IPV6 addresses are longer: It's a 128 bit integer, represented for human legibility as eight four-digit hexadecimal numbers separated by colons. They could be handled by a generalization of this mechanism of separating the representation from the underlying storage.
Easier than my formula approach, Data>Text To Columns... can split up dot-separated IPv4 into 4 numbers.
The latest versions support stable sorting, so you can order by more than 3 columns. Order by 1,2,3, then by column 4, which will keep the previous order within the last sort order.
Using a database, all this should be no problem as far as the database tool is able to split a string into 4 numbers. Base can connect to log files, Base queries can deynamically convert, filter and sort the required information on the fly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: IP address sorting
No, I'm suggesting storing the address as one number in one cell, not as four numbers in four cells, which is almost as bad as four strings in four cells. An IPV4 address, as seen in each packet that traverses the Internet, is a 32-bit integer. The conventional dotted-quad representation views that single integer as four bytes, each containing a value 0..255. It displays that value in decimal, and separates the four bytes with period characters.
For efficiency of manipulation in a spreadsheet, that address should be stored as an integer, and the dotted-quad should be just a display format. Think of the way a date is stored in a cell as a binary number. That binary number is easy for a spreadsheet to sort, do date math, etc. The user selects whether she wants to see it as "01/25/2010" or "25 enero 2010" or "20100125", but that display representation is unrelated to the underlying storage encoding.
This same abstraction of display representation from storage format will serve just as well for IPV6 addresses.
For efficiency of manipulation in a spreadsheet, that address should be stored as an integer, and the dotted-quad should be just a display format. Think of the way a date is stored in a cell as a binary number. That binary number is easy for a spreadsheet to sort, do date math, etc. The user selects whether she wants to see it as "01/25/2010" or "25 enero 2010" or "20100125", but that display representation is unrelated to the underlying storage encoding.
This same abstraction of display representation from storage format will serve just as well for IPV6 addresses.
OOo 3.1.1 on Windows 7
Re: [Solved] IP address sorting
I see, 255.255.255.255 = FFFFFFFF = 4294967295 = (2^32)-1
I guess that 99% of all IPs in spreadsheets come from plain text logs or HTML with human readable dot-notation.
I guess that 99% of all IPs in spreadsheets come from plain text logs or HTML with human readable dot-notation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: IP address sorting
Yes, exactly, and that's the problem I'm trying to solve. I get system activity log data as csv text, of which one column is a date in the format "yyyymmdd" and another column is an IPV4 address in the textual format "1.2.3.4". It would be tremendously helpful if Calc could import the textual dotted-quad representation, and store it as a single 32-bit integer. Calc already knows how to parse and import and store a date, or a number; now how about an IP address?
And that same method is just as helpful for an IPV6 address in the textual format "0123:4567:89ab:cdef", which I would like to store and manipulate as a single 128-bit integer.
Not just sorting, other manipulations too - like bitwise logical masking for non-byte-aligned subnet boundaries or CIDR blocks, range math, all sorts of stuff. I want to have the same flexibility with IP addresses we currently enjoy with date/time. I doubt this can be done conveniently with user macros; it needs to be an underlying feature of the software. Are these storage/display features already part of Calc, or perhaps an addon? (I can't find them.) Where can I make a feature request? I'm a network guy, not a spreadsheet coder
And that same method is just as helpful for an IPV6 address in the textual format "0123:4567:89ab:cdef", which I would like to store and manipulate as a single 128-bit integer.
Not just sorting, other manipulations too - like bitwise logical masking for non-byte-aligned subnet boundaries or CIDR blocks, range math, all sorts of stuff. I want to have the same flexibility with IP addresses we currently enjoy with date/time. I doubt this can be done conveniently with user macros; it needs to be an underlying feature of the software. Are these storage/display features already part of Calc, or perhaps an addon? (I can't find them.) Where can I make a feature request? I'm a network guy, not a spreadsheet coder

OOo 3.1.1 on Windows 7
Re: IP address sorting
same here. with ipv4 space running out, it is nice find any "holes" you might have in iP assignements...bobsutt wrote: Are these storage/display features already part of Calc, or perhaps an addon? (I can't find them.) Where can I make a feature request? I'm a network guy, not a spreadsheet coder
OpenOffice.Org v3.2.0 Windows 7
Re: IP address sorting
Why? There are several way to do this. I demonstrated how to split up the decimal notation by means of formulas. Others prefer the text-to-columns tool.bobsutt wrote:Should have un-"solved" the subject line.
I always recommend to do all csv stuff using the Base component. It should be possible to solve this in one simple SQL query (split, convert to integer and sort).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: IP address sorting
What is the process for making a feature request for new underlying storage types (one for 32-bit IPv4 and one for 128-bit IPv6), and for corresponding new display representations and textual input converters?
OOo 3.1.1 on Windows 7
Re: [Solved] IP address sorting
Changing the underlying storage types of the spreadsheet? This will never happen. It would break the ODF standard and all compatibility with other spreadsheets for the single purpose to handle IP addresses more conveniently.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] IP address sorting
You can already store as many bits as you want--as a string. You just need some functions for doing simple math on them.
I believe there is already an extended precision extension for Calc; it might be possible to add a few functions onto that.
I believe there is already an extended precision extension for Calc; it might be possible to add a few functions onto that.
AOO4/LO5 • Linux • Fedora 23
Re: [Solved] IP address sorting
I don't think anyone is suggesting a new storage type. It could simply be a special format for a 32 bit integer or possible some special handling of a text format. I'm not familiar with the ODF spec but I would think it would allow for attaching metadata to a cell (formatting etc.) that would allow from some sort of backward compatibility when a specific format specifier isn't recognized. I would think that compatibility with other formats could be achieved by degrading to strings (like what happens when you save formulas to CSV). I'm doing my best to never convert to xls anywayVilleroy wrote:Changing the underlying storage types of the spreadsheet? This will never happen. It would break the ODF standard and all compatibility with other spreadsheets for the single purpose to handle IP addresses more conveniently.

I'm trying to do some simple math on IP addresses and it's quite difficult. Having to split the addresses to several columns to maintain both readability and some sort of recognized number format is very cumbersome. I was hoping that someone had written some sort of plugin to handle IPs more gracefully (especially since OO users tend to be a bit more technical)... maybe if I get some free time I'll write one myself

OpenOffice 3.1.0 on Mac OS 10.6
Re: [Solved] IP address sorting
At least one request is on file for this: Issue 96201: ip2long() builtin ip address conversion function. There is a web address there for a helper function, although I can't say if it will be of any use.
You can register there and add your vote (up to two) or comment.
You can register there and add your vote (up to two) or comment.
AOO4/LO5 • Linux • Fedora 23
Re: [Solved] IP address sorting
A cow-orker pointed out that perhaps the best way to address (sorry) this in the short term is to use the MySQL INET_ATON/INET_NTOA functions. Not exactly optimal for a spreadsheet, and doesn't yet solve the IPv6 problem, but it works.
OOo 3.1.1 on Windows 7
Re: [Solved] IP address sorting
I've just combined all formulas from the 1st reply, enjoy:
=VALUE(LEFT($B1;FIND(".";$B1)-1))*1000000000+MID($B1;FIND(".";$B1)+1;FIND(".";$B1;FIND(".";$B1)+1)-FIND(".";$B1)-1)*1000000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-FIND(".";$B1;FIND(".";$B1)+1)-1))*1000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-1))
=VALUE(LEFT($B1;FIND(".";$B1)-1))*1000000000+MID($B1;FIND(".";$B1)+1;FIND(".";$B1;FIND(".";$B1)+1)-FIND(".";$B1)-1)*1000000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-FIND(".";$B1;FIND(".";$B1)+1)-1))*1000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-1))
LibreOffice 5.1.3.2 on MacOS 10.12.3
Re: [Solved] IP address sorting
This office suite supports MySQL via JDBC, ODBC and with its own SDBC driver and you can integrate database data and query results seamlessly with spreadsheets
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] IP address sorting
Hallo
@CTAG:
The original Post is quite old from 2009, its about sorting textual Representations of IP-addresses
It is NOT about high-level "Formula-obfuscation"
(meanwhile Libreoffice is able to do the expected Sort with Option: enable natural Sort)
@CTAG:
The original Post is quite old from 2009, its about sorting textual Representations of IP-addresses
It is NOT about high-level "Formula-obfuscation"
(meanwhile Libreoffice is able to do the expected Sort with Option: enable natural Sort)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 24.8… flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: [Solved] IP address sorting
with this formula I've just sort bunch of ip-addresses in human-readable format. Because this thread is shown in google in top 5 results for "libreoffice ip-address sort", I've decided to share my work (based on 1st answer, though) for future visitors - it can be easily cut-n-pasted in any (probably - temporary) worksheet without any difficulties.
And yes, it is sort of necroposting, definitely )
And yes, it is sort of necroposting, definitely )
LibreOffice 5.1.3.2 on MacOS 10.12.3