+ Reply to Thread
Results 1 to 4 of 4

Obtaining IP Ranges from IP Subnets (or) CIDR Notation / How to Use Specific Code

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Obtaining IP Ranges from IP Subnets (or) CIDR Notation / How to Use Specific Code

    Afternoon Guys -

    I've got over 1,000 subnets which have a variety of subnet masks which I need to convert into IP Ranges. I've searched all over the place for a way to do this with Excel (or anything else) including a couple of posts on this site, but still haven't had any luck. So far, I've been able to convert all of them to CIDR format simply by duplicating the subnet mask column then perfoming a find / replace for specific mask.

    I downloaded ipmask2.xls from this post on this site, added addresses and masks in the first two columns underneath, then dragged down the row from column C & D so their formulas would apply as was the pattern, but it only worked for /24 addresses.

    I then went to a post that was suggested at the end of that post on this site named "Creating IP Ranges from IP Add (Network) and Subnet Mask." It includes a lot of code which seemed to work for others, but cannot figure out how to apply these in Excel to my scenario - not to mention which posts are complete code and if some are partial. Honestly never really done any code-based stuff with Excel before.

    Anyone have any suggestions for how to do this - or - how to implement the code from the 2nd post perhaps? Thank You!!!

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Obtaining IP Ranges from IP Subnets (or) CIDR Notation / How to Use Specific Code

    Do you have an example of your workbook that you can upload showing you exact data structure, and show your expected output?
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    07-02-2014
    Location
    Thennessee
    MS-Off Ver
    2012 Pro
    Posts
    15

    Re: Obtaining IP Ranges from IP Subnets (or) CIDR Notation / How to Use Specific Code

    Thanks for the quick reply! Sure - attached are two examples.

    Example#1.xlsx has 4 columns. What I'm trying to do is find some type of formula that will use the data per row from columns A+B or C so that the cooresponding IP Ranges will appear in column D. I manually converted and entered the first one as an example of the output I'm desiring.

    ipmask2.xls was downloaded from the first post I mentioned. It has four columns with columns A+B being source data (same source data I have in A+B in Example1.) Columns C & D are formulas which output the ip range (C being the start of the range and D the end of the range). This would have been perfect for me so pasted in a few subnets and masks then dragged down to copy formula for C+D and it worked - but only for /24 addresses

    Hope that's what you were asking for. Any suggestions? Thanks!

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Obtaining IP Ranges from IP Subnets (or) CIDR Notation / How to Use Specific Code


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating 'Network IP' and 'Subnet IP' Ranges from IP CIDR subnets ranges
    By sokolum in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2019, 06:46 AM
  2. How to get my code to target only sheets that are named in a specific ranges?
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2014, 09:13 AM
  3. Help with obtaining MIN from consecutive, non-overlapping ranges
    By l.alex.g in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2012, 03:58 PM
  4. [SOLVED] How to use named ranges in R1C1 notation?
    By wolis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-24-2012, 11:13 AM
  5. obtaining data within a specific date range
    By angelj912 in forum Excel General
    Replies: 4
    Last Post: 10-04-2011, 08:35 AM
  6. Using ranges in R1C1 notation in SUMIF formula
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2009, 09:51 PM
  7. [SOLVED] Selecting multiple ranges using 'Cells' notation
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2005, 01:05 PM
  8. [SOLVED] Refer to Ranges using Cells notation
    By Scott P in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1