+ Reply to Thread
Results 1 to 7 of 7

Calculating CIDR from ipaddress and subnet mask

  1. #1
    Registered User
    Join Date
    05-08-2018
    Location
    toronto, canada
    MS-Off Ver
    office2007
    Posts
    2

    Calculating CIDR from ipaddress and subnet mask

    First time post...

    I have a csv file that gets sent to me every month from our network folks. see below.

    The third column I currently put in manually however it is a pain since we have 1700 subnets to deal with. Can anyone help me populate the third column using the CIRD syntax. Even better, my ultimate solution would have the third column look like

    eg: 172.25.103.64/26
    172.25.11.0/24
    etc...

    The reason for the proper syntax is that I take this list and import it into my network scanning tool.

    monthly csv example
    172.25.103.64 255.255.255.192 /26
    172.25.104.0 255.255.255.192 /26
    172.25.104.128 255.255.255.128 /25
    172.25.104.64 255.255.255.192 /26
    172.25.105.0 255.255.255.192 /26
    172.25.105.128 255.255.255.128 /25
    172.25.105.64 255.255.255.192 /26
    172.25.11.0 255.255.255.0 /24
    172.25.12.0 255.255.255.0 /24
    172.25.13.0 255.255.255.0 /24
    172.25.14.0 255.255.255.0 /24
    172.25.143.0 255.255.255.128 /25
    172.25.148.0 255.255.255.0 /24
    172.25.15.0 255.255.255.0 /24
    172.25.150.0 255.255.255.192 /26
    172.25.150.128 255.255.255.192 /26
    172.25.150.192 255.255.255.192 /26
    172.25.150.64 255.255.255.192 /26
    172.25.151.0 255.255.255.192 /26
    172.25.151.128 255.255.255.192 /26

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Calculating CIDR from ipaddress and subnet mask

    The macro is not needed.
    Elements:
    1. The cidr table from https://en.wikipedia.org/wiki/Classl...Domain_Routing
    2. VLOOKUP function

    Import data, set the columns as in the example, insert the formula and copy it down.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-08-2018
    Location
    toronto, canada
    MS-Off Ver
    office2007
    Posts
    2

    Talking Re: Calculating CIDR from ipaddress and subnet mask

    That will save me MANY!!! hours. Thanks a lot.

  4. #4
    Registered User
    Join Date
    05-14-2020
    Location
    Switzerland
    MS-Off Ver
    365
    Posts
    1

    Re: Calculating CIDR from ipaddress and subnet mask

    Thanks for the file ! It saved me a lot of time as well !

  5. #5
    Registered User
    Join Date
    08-09-2020
    Location
    Germany
    MS-Off Ver
    O365
    Posts
    1

    Re: Calculating CIDR from ipaddress and subnet mask

    Thank You ! This saved me a lot of time.

  6. #6
    Registered User
    Join Date
    03-16-2022
    Location
    South Africa
    MS-Off Ver
    Latest
    Posts
    1

    Re: Calculating CIDR from ipaddress and subnet mask

    Thank you! I see this was posted a while ago. Just stumbled on it now. You saved me a lot of time! Had over 7600 subnets to convert! Appreciate.

  7. #7
    Registered User
    Join Date
    09-07-2015
    Location
    Los Angeles
    MS-Off Ver
    Office 365 Mac
    Posts
    1

    Re: Calculating CIDR from ipaddress and subnet mask

    Quote Originally Posted by mjr veverka View Post
    The macro is not needed.
    Elements:
    1. The cidr table from wikipedia
    2. VLOOKUP function

    Import data, set the columns as in the example, insert the formula and copy it down.
    Thank you Mjr! So beautiful and simple.

+ 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. Convert Subnet address and mask to CIDR and vice-versa
    By redrockrobbo in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-16-2018, 03:31 AM
  3. Excel IP Mask and Subnet ID
    By fish221171 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-30-2017, 08:11 AM
  4. Creating IP Ranges from IP Add (Network) and Subnet Mask
    By dan_in-need-of-help in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 01-07-2015, 12:17 PM
  5. Generating IP addressses from a IP address and Subnet mask
    By syedaley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-12-2013, 05:11 AM
  6. Need to take an IP Subnet Range and Output Subnet Mask
    By Markh7999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2013, 08:53 PM
  7. Creating the IP ranges from the IP and the subnet mask
    By hari6677 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 11:27 AM

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