An IP address, be it an IPv4 or IPv6 is an integer number, being a 32bit integer if IPv4 or a 128bit if IPv6. Since Calc is well suited to perform number operations, it would be nice to add a formatting option to represent an integer as an Human readable IP Address and to accept a valid IP address and stora in the cell as an integer, as it is done with the DATE and TIME formats. There is an excelent set of Libreoffice Basic functions from Thomas Rohmer-Kretz in http://trk.free.fr/ipcalc/ to perform this task among various other IP related functions, but it would be better to have a native conversion in place, since going the Basic functions way is slow and cumbersome. Alternatively, a means to create a personalized format representation with a module function would be also be a way to enhance the personalized format feature.
I believe this is not a function that most users (not even many) would need. So my take is to rather keep it a 3rd party macro solution. Plus, it's not just formatting the integer as IP or vice versa but some calculation. For example 2130706433 = 127.0.0.1.
I came here to read the motivation behind this feature request. I cannot see too many reasons why someone would store an IP address as an integer rather than a string. I had only one idea: creating subsequent IP addresses via autofill. To me, it looks like a very rare edge case and I would rather do it by this formula in A2 to convert the integer of A1: =TEXTJOIN(".", 0, BITRSHIFT(A1, 24), BITAND(BITRSHIFT(A1, 16), 255), BITAND(BITRSHIFT(A1, 8), 255), BITAND(A1, 255) ) I may be wrong and there can be more valid use cases. If this is the case, could you show some, please?
I believe the best thing to implement would be OP's alternative suggestion: > Alternatively, a means to create a personalized format representation with a > module function would be also be a way to enhance the personalized format > feature. That really scratches the itch IMHO. As for the main ask here - I weakly-support it, because: 1. IP addresses, while not a very common use cases, are not a tiny niche; and tables of data involving IP addresses are often the result of event logging on the Internet. 2. We already support a variety of number formatting types, in several categories, and do not lack anything in the UI for enabling this support. 3. "Third-party" support requires either keeping every element of the IP in a separate column (which many not even be possible with IPv6), or doing a lot of string processing. Both are cumbersome to work with and I'm worried about performance when using them. 4. Either MS Excel has it, in which case - competitor parity; or it doesn't have it, in which case: one-upping the competition :-) I realize that someone might argue "oh, but shall we now add another number format option for any number of niche formats?" - and the answer to that is, that AFAICT, there are extremely few, if any, other such requests, and I doubt the adoption of IP formatting will change this.
(In reply to csongor from comment #2) > I cannot see too many reasons why someone would store an IP address as an > integer rather than a string. I can imagine sysadmins having various rules for larger deployments, that would be easily used when it is indeed a 'numeric' field.
(In reply to csongor from comment #2) > I came here to read the motivation behind this feature request. > > I cannot see too many reasons why someone would store an IP address as an > integer rather than a string. I had only one idea: creating subsequent IP > addresses via autofill. To me, it looks like a very rare edge case and I > would rather do it by this formula in A2 to convert the integer of A1: > > =TEXTJOIN(".", 0, > BITRSHIFT(A1, 24), > BITAND(BITRSHIFT(A1, 16), 255), > BITAND(BITRSHIFT(A1, 8), 255), > BITAND(A1, 255) > ) > > I may be wrong and there can be more valid use cases. If this is the case, > could you show some, please? For one, one may be able to setup a IP Address Management for a few separate office in a single spreadsheet without the need to install a whole system and a database to do it. For another, one cannot order subnetted IP ranges with the TEXTJOIN function you suggested.
(In reply to Heiko Tietze from comment #1) > I believe this is not a function that most users (not even many) would need. > So my take is to rather keep it a 3rd party macro solution. > > Plus, it's not just formatting the integer as IP or vice versa but some > calculation. For example 2130706433 = 127.0.0.1. Yes. Some calculation to and from will be necessary. But it will be orders of magnitude faster than having to do them with macros.
[Automated Action] NeedInfo-To-Unconfirmed
We discussed the topic in the design meeting. There are plenty of solutions like the one in comment 2 (although not supporting IPv6). If changes are needed we should invest in the user-defined formatting rather than introducing another number format that likely is not standardized in the open document format.
(In reply to Heiko Tietze from comment #8) > There are plenty of solutions like the one in comment 2 (although not > supporting IPv6). If changes are needed we should invest in the user-defined > formatting rather than introducing another number format that likely is not > standardized in the open document format. Why? If the reason is the fact that this is not standardized in the ODF - I think that's a very weak reason; not because IP addresses themselves should have their standard referred to in the ODF, but rather because we should be able to use the components of an IP representation, i.e. using pure functions on the value to obtain pieces of the displayed value, and adding literals between these pieces.