Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

IP Address as a datatype.

I would love to see IP Addresses added as a format option and functions added for validation of the IP addresses..

For instance InSubnet(Network as IP,Netmask as Integer,address as IP) to determine if an IP is within a Subnet;

Being able to do addition/subtraction on the field would be awesome as well!

219 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Alex Chard shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    Accepting Votes  ·  AdminExcel Team [MSFT] (Admin, Office.com) responded  · 

    Thanks for the suggestion Alex, and thanks to those that making their vote heard. This is a pretty interesting ask – take a look at the comment I made. I’d like us to understand a few related things. And as always, please keep voting for the things you care about most.

    Cheers,
    John [MS XL]

    27 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • queen__frostine commented  ·   ·  Flag as inappropriate

        Yes to more data types!!!!!!! I'm sure the consumer goods industry would love you forever if UPCs/GTINs were also a data type.

      • Anonymous commented  ·   ·  Flag as inappropriate

        In format cells, you could try using "000.000.000.000,,," as an option. It's not perfect, but works fine if all the octets are with 3 numbers (192.168.128.100).

      • Jaime Castells commented  ·   ·  Flag as inappropriate

        The ability to sort by IP and/or subnet in CIDR notation as well as perform logical tests between IP values, IP and CIDR or multiple CIDRs would be a relief. I have a number of hoops I keep at hand so I can jump through them. It's time consuming and the results are rarely more than passable.

        PLEASE add my vote to this list.

      • Grimthaw commented  ·   ·  Flag as inappropriate

        Operations/Functions on IP/Mac Addresses would be good:
        * Off the top of my head for IPs
        ** Convert from Dotted Decimal to Dotted Binary
        ** Split into network and host portions of address
        ** Perform binary operations on dotted decimal addresses to create:
        *** network plans
        *** routing summarization tables,
        *** network addresses,
        *** host addresses
        *** broadcast addresses
        * Off the top of my head for MACS like IPs
        ** Split into OUS and NIC portions

      • mike westkamper commented  ·   ·  Flag as inappropriate

        I never got a comment, so I doubt this is monitored anymore. I suspect that MS resources are not in the Office products anymore. Not much has changed in the last few versions.
        I wrote a vb script that parses and does the math and converts the dotted notation in ibv4 to an integer and back to dotted notation. This allows me to sort and convert a number from/to a IPV4 notation. I am not a Excel VB script guru but in my specific instance it does what I need. Some really skilled in vb scripting could add a bunch of features and ipv6 too. The functions are pretty easy to code in Basic.
        Good Luck KEn.

      • KEn commented  ·   ·  Flag as inappropriate

        It's 2016, this has been an issue since the beginning. I am surprised it hasn't been addressed already. I would be happy if I could get the IP cell format to work correctly. Having it tell me if it's part of the same subnet is gravy.

      • mike westkamper commented  ·   ·  Flag as inappropriate

        I think this was in here a year or so ago, couldn't find it. I don't see it in Excel. Is it something that is being considered?

        Sure would be helpful to able to manipulate, sort, validate, etc IPv4 and IPv6 addresses. Same general features as Date datatype.

      • Gastrich commented  ·   ·  Flag as inappropriate

        Many network engineers use excel to manage IP addresses. It's currently an involved process to sort addresses. Having an option under sorting would make this easier to perform.

      • AdminExcel Team [MSFT] (Admin, Office.com) commented  ·   ·  Flag as inappropriate

        Thanks for the additional info folks. We're doing some thinking on making data easier to handle/work with in general, and will take a deeper look at more out of the box support for this area specifically.

        Thanks,
        John [MS XL]

      • Anonymous commented  ·   ·  Flag as inappropriate

        John,

        The obvious function I see is to check whether an address exists in a subnet. Lets say I have a range of IP's for each OSPF area. I input an IP address, then I want the next cell to show the OSPF area, it could check the IP address that I put in against a table of OSPF area's.. a more basic example:

        A1 = 10.44.0.0/16
        A2 = 10.44.16.1
        A3 = IF(IN_SUBNET(A2,A1),0.0.0.44)

      • Alex Chard commented  ·   ·  Flag as inappropriate

        Oh and MAC addresses would be good too.. And allow manual formatting similar to number fields on them as there are many different ways to display them. for instance these are all equivalent:

        00-AA-BB-CC-DD-EE
        00aa.bbcc.ddee
        00:AA:BB:CC:DD:EE
        00aabbccddee

      • Alex Chard commented  ·   ·  Flag as inappropriate

        John,
        A few things worth considering.. IPv4 and IPv6 support would be important - they could easily be separate data types. Perhaps the datatype should store IP Address and netmask.. and assume that the netmask is /32 if it is not entered. Formatting options should include being able to display only the IP, IP and CIDR (like x.x.x.x/24) or IP and Netmask (x.x.x.x/y.y.y.y)

        It would be good if it could understand IP addresses input with CIDR notation, and possibly be able to convert that to a subnet mask with a function as well.

        And yes I would support being able to ping or resolve hostnames to IP addresses as mentioned in the other suggestion (I will vote there too!)

        Thanks very much for looking in to this!
        Alex

      • Anonymous commented  ·   ·  Flag as inappropriate

        As the original poster mentioned, a function that can determine if a certain IP address falls into a defined subnet would be incredibly useful to network admins. Also, the ability to add IP addresses as a data type so they can be sorted without extensive data wrangling.

        Other basic networking functions would be nice too (such as what would be included in any basic CIDR calculator):

        1. convert net mask to prefix notation, and vice-versa
        2. Return first or last host, or broadcast address in a given network (or perhaps any user-defined value in an IP range)
        3. Breaking up networks into subnets.

        Thanks!

      • Anonymous commented  ·   ·  Flag as inappropriate

        It is important to me that Excel supports all the common ways of formatting IPv4, IPv6 and MAC addresses. This includes, but is not limited to:

        IPv4:
        1.2.3.4
        001.002.003.004
        255.255.255.255

        IPv6:
        ::1
        0000:0000:0000:0000:0000:0000:0000:0001

        MAC:
        00-23-24-12-AB-EF
        00232412ABEF
        00:23:24:12:AB:EF
        0023.2412.ABEF

        This means not removing "0" from the front of the numbers for example. It could also be helpful to be able to refer to the subnet mask in the /n notation after any format of IP address. Functions to calculate the sizes of subnets, broadcasts and so on could be helpful in many cases.

      • Matthew Vicari commented  ·   ·  Flag as inappropriate

        This would make my life so much easier. It would be great if excel was able to sort IPv4 and IPv6 addresses. It would also be great if it was able to auto-fill a range of IPs so I don't have to enter hundreds of addresses when making a list. And it would be awesome having it as an understood datatype so I can do a sum against an IP addresses to increment them to find important IPs.

      ← Previous 1

      Feedback and Knowledge Base