IP Address support in spreadsheets?
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us. At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers. Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-) -- Bryan Fields 727-409-1194 - Voice http://bryanfields.net
Web based database? What about NetBox? On Mon, Sep 15, 2025, 1:31 PM Bryan Fields via NANOG <nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
Yes. Some /20 and /24 subnets in Spreadsheet. Not that it's great but it's worked for ~18+ years. On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
If you had to in GSheets, I would just recreate inet_aton and inet_ntoa in Apps Script. It's just bit shifts and ORs, shouldnt be hard. On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
=TEXT(BITAND(A1,4278190080)/16777216,"0")&"."&TEXT(BITAND(A1,16711680)/65536,"0")&"."&TEXT(BITAND(A1,65280)/256,"0")&"."&TEXT(BITAND(A1,255),"0") apparnetly is inet_ntoa maybe? :) On Mon, Sep 15, 2025 at 2:52 PM Tom Beecher via NANOG <nanog@lists.nanog.org> wrote:
If you had to in GSheets, I would just recreate inet_aton and inet_ntoa in Apps Script. It's just bit shifts and ORs, shouldnt be hard.
On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
_______________________________________________ NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/D7ZEVMHZ...
On 2025-09-15 13:30, Bryan Fields via NANOG wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
If you're not allergic to MS Excel, you could use Python for Excel to convert back and forth with the excellent ipaddress module. https://www.google.com/?q=google%20sheets%20ip%20functions turned up an interesting add-on, have you looked into IP Functions at all? Could also just roll your own inet_aton() in your columns for the dotted quads using LEFT(), MID(), RIGHT(), and FIND(). Or just spin up Netbox and load your prefixes into that and be done. -Brian
As someone doing it in practice, I just drag and drop 255 rows for the IPs. It's not that big of a deal. One time I had to do a whole 2048 rows and it probably took me a minute. On Mon, Sep 15, 2025 at 3:29 PM Brian Knight via NANOG < nanog@lists.nanog.org> wrote:
On 2025-09-15 13:30, Bryan Fields via NANOG wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
If you're not allergic to MS Excel, you could use Python for Excel to convert back and forth with the excellent ipaddress module.
https://www.google.com/?q=google%20sheets%20ip%20functions turned up an interesting add-on, have you looked into IP Functions at all?
Could also just roll your own inet_aton() in your columns for the dotted quads using LEFT(), MID(), RIGHT(), and FIND().
Or just spin up Netbox and load your prefixes into that and be done.
-Brian _______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/QIZBM4KI...
On Mon, 15 Sep 2025, Bryan Fields via NANOG wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
I'm going to lead with echoing the sentiment: a spreadsheet is the wrong tool for this job. There are numerous free IPAM applications purpose built for, you know, managing IP address space...but just a bit of googling turned up recommendations for IP Tools for Excel (which I've never used / never heard of until a minute ago) which may be helpful. https://ip-tools-for-excel.software.informer.com/ At least it's not a stack of copy paper with each sheet representing a /24 (the IPAM I inherited at my second ISP job). ---------------------------------------------------------------------- Jon Lewis, MCP :) | I route Blue Stream Fiber, Sr. Neteng | therefore you are _________ http://www.lewis.org/~jlewis/pgp for PGP public key_________
For about 20 years, I’ve been using an Excel spreadsheet for IPv4 address documentation, even more, subnet calculations. Lately, I’ve been using it for IPv6 as well, kind of painful, but I can make it work. As mentioned, I actually have an in-line breakout section of binary to make the subnetting more visual and easier to track with applicable formulas in the cells to cause it to calculate the dotted decimal equivalent. My v6 spreadsheet doesn’t have similar underlying formals, because I don’t do under-the-hood binary-level subnetting, but rather only face-value hex subnetting. Aaron
On Sep 15, 2025, at 2:17 PM, Christopher Morrow via NANOG <nanog@lists.nanog.org> wrote:
=TEXT(BITAND(A1,4278190080)/16777216,"0")&"."&TEXT(BITAND(A1,16711680)/65536,"0")&"."&TEXT(BITAND(A1,65280)/256,"0")&"."&TEXT(BITAND(A1,255),"0")
apparnetly is inet_ntoa maybe? :)
On Mon, Sep 15, 2025 at 2:52 PM Tom Beecher via NANOG <nanog@lists.nanog.org> wrote:
If you had to in GSheets, I would just recreate inet_aton and inet_ntoa in Apps Script. It's just bit shifts and ORs, shouldnt be hard.
On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
_______________________________________________ NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/D7ZEVMHZ...
NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/D6EDM4QA...
This is absolutely sadistic. And also I think correct , lol On Mon, Sep 15, 2025 at 3:17 PM Christopher Morrow <morrowc.lists@gmail.com> wrote:
=TEXT(BITAND(A1,4278190080)/16777216,"0")&"."&TEXT(BITAND(A1,16711680)/65536,"0")&"."&TEXT(BITAND(A1,65280)/256,"0")&"."&TEXT(BITAND(A1,255),"0")
apparnetly is inet_ntoa maybe? :)
On Mon, Sep 15, 2025 at 2:52 PM Tom Beecher via NANOG <nanog@lists.nanog.org> wrote:
If you had to in GSheets, I would just recreate inet_aton and inet_ntoa
in
Apps Script. It's just bit shifts and ORs, shouldnt be hard.
On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/D7ZEVMHZ...
I do this a bunch for importing and exporting equipment in to different systems. I have used the google ipfunctions, but I mainly work in libre calc for that I used to have some of the functions programed in a sheet then I found this and just added the macros https://extensions.libreoffice.org/en/extensions/show/ip-calculator On 9/15/25 1:30 PM, Bryan Fields via NANOG wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
For subnet figuring my late wife had me make this up back in 1997 when she was the DNS/IP person. Or as she called it, The Domainatrix. http://thisoldhost.com/subnets.html Feel free to copy and share. It's just HTML 1.0. -Joe -- Joe Hamelin, W7COM, Portland, OR, +1 360 474 7474
I wrote a VB library years ago to manipulate IP addresses in Excel. Its documented in the tabs. I hope someone finds it useful https://www.dropbox.com/scl/fi/yijxbrkdluuo64o4wf1nn/TOC-1.4.xlsm?rlkey=2bwpylnfguznkszkxapuhr0hw&st=7md5q0hb&dl=0 Laris On 2025-09-15 4:36 p.m., Tom Beecher via NANOG wrote:
This is absolutely sadistic.
And also I think correct , lol
On Mon, Sep 15, 2025 at 3:17 PM Christopher Morrow<morrowc.lists@gmail.com> wrote:
=TEXT(BITAND(A1,4278190080)/16777216,"0")&"."&TEXT(BITAND(A1,16711680)/65536,"0")&"."&TEXT(BITAND(A1,65280)/256,"0")&"."&TEXT(BITAND(A1,255),"0")
apparnetly is inet_ntoa maybe? :)
On Mon, Sep 15, 2025 at 2:52 PM Tom Beecher via NANOG <nanog@lists.nanog.org> wrote:
If you had to in GSheets, I would just recreate inet_aton and inet_ntoa in Apps Script. It's just bit shifts and ORs, shouldnt be hard.
On Mon, Sep 15, 2025 at 2:31 PM Bryan Fields via NANOG < nanog@lists.nanog.org> wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
-- Bryan Fields
727-409-1194 - Voice http://bryanfields.net
_______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH... _______________________________________________ NANOG mailing list
https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/D7ZEVMHZ...
_______________________________________________ NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/MNPJNZVC...
Well. Im using .txt files for years to handle network stuff. So, I will ask question.. How much addresses you need to keep track of? Im small shop, but if you scale is not big I can hand you my tools ;) % blgrep -c -a - hosts.txt + Sum (Aggregated) Count: 5313 Pros of .txt files. VCS (SVN or GIT) friendly. You can have history, generate diffs :) They will run anywhere. Cons: well, its CLI and some people are alergic to it.. Regards, Borg ---------- Original message ---------- From: Bryan Fields via NANOG <nanog@lists.nanog.org> To: nanog@lists.nanog.org Cc: Bryan Fields <Bryan@bryanfields.net> Subject: IP Address support in spreadsheets? Date: Mon, 15 Sep 2025 14:30:52 -0400 Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us. At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers. Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-) -- Bryan Fields 727-409-1194 - Voice http://bryanfields.net _______________________________________________ NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/VDO2LGNH...
This extension also has google sheets and microsoft excel support, kinda nice. http://trk.free.fr/ipcalc/ On 9/15/2025 5:29 PM, Trey Scarborough via NANOG wrote:
I do this a bunch for importing and exporting equipment in to different systems. I have used the google ipfunctions, but I mainly work in libre calc for that I used to have some of the functions programed in a sheet then I found this and just added the macros https://extensions.libreoffice.org/en/extensions/show/ip-calculator
On 9/15/25 1:30 PM, Bryan Fields via NANOG wrote:
Has anyone found a solution to represent IPv4 addresses in a spreadsheet, preferably Google Sheets? I'm not here to debate the use of spreadsheets for tracking IP addresses, just find something better than storing v4 dotted decimal as text in a spreadsheet. We don't need v6 at this time; we're using a spreadsheet for tracking addressing, v6 is beyond us.
At minimum we need to store it as a 32 bit INT, and display as dotted decimal. If there's some way to make it aware of subnet slash notation, so much the better. I'm a bit at a loss as to why there's no IPv4 number format with how often spreadsheets are the source of truth in some rather large service providers.
Again, not looking for non-spreadsheet solutions to this, and ideally a web interface spreadsheet would be best for sharing. We don't need a real database if it's on the web :-)
_______________________________________________ NANOG mailing list https://lists.nanog.org/archives/list/nanog@lists.nanog.org/message/ZZQ6CQ7U...
On 9/16/25 10:55, Mark Wiater via NANOG wrote:
This extension also has google sheets and microsoft excel support, kinda nice.http://trk.free.fr/ipcalc/
Just as a followup to this, I gave a few of them a try and found this to be the best (least bad?) for my customers needs. It's not great in terms of speed in google sheets, but it works. -- Bryan Fields 727-409-1194 - Voice http://bryanfields.net
participants (13)
-
Aaron1 -
borg＠uu3.net -
Brian Knight -
Bryan Fields -
Christopher Morrow -
Joe Hamelin -
Jon Lewis -
Josh Luthman -
Josh Reynolds -
Laris Beņķis -
Mark Wiater -
Tom Beecher -
Trey Scarborough