+ Reply to Thread
Results 1 to 11 of 11

Convert Subnet address and mask to CIDR and vice-versa

  1. #1
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Convert Subnet address and mask to CIDR and vice-versa

    Hi there. New to the forum and I have looked at length for a solution. I think there are lots here, but they are either old of they dont really say clearly enough how to use them. I'm no Excel expert so just receiving some VB code or even 'here is a function' would need some help explaining. I have two lists. One contains subnet mask and subnet address so

    Subnet Address Subnet Mark I need CIDR here
    1.1.1.0 255.255.255.0

    The other has CIDR

    CIDR I need subnet address here I need Subnet mask here
    1.1.1.0/24

    I think I can live with just the conversion of subnet address and mask to CIDR, but it would be nice to have both.
    Subnets could be class A,B,C or D

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Convert Subnet address and mask to CIDR and vice-versa

    maybe it will help
    (don't ask me how it was done )

    what ip-calc can do:

    - increment an IP address
    - IP address sorting
    - calculate a net mask or a wildcard (i.e. inverse mask)
    - check if an address is in a subnet
    - match an address against a list of subnets (similar to Excel VLOOKUP and MATCH functions)
    - find overlapping subnets
    - sort and summarize subnets or routes
    - convert an IP range to a list of networks in CIDR notation
    - subtract subnets from a list of larger subnets
    - calculate the difference between 2 IP addresses
    - calculate the size of a subnet
    - check if an address is public or private
    - IPv4 and IPv6 (not all functions are available for IPv6 yet)
    + provide a set of helper functions for IP address parsing so you can easily write your own macro
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Re: Convert Subnet address and mask to CIDR and vice-versa

    Thanks. I did find that but I didn't really know how to actually get it working. I'll try harder 8-)

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Convert Subnet address and mask to CIDR and vice-versa

    or maybe this one: link

  5. #5
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Re: Convert Subnet address and mask to CIDR and vice-versa

    Yes, I saw that too, but I don't know how to use it. I am not a great XL user................

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Convert Subnet address and mask to CIDR and vice-versa

    Sorry about that but I hate vba so I can't help you more. maybe someone else will do that

    have a nice day

  7. #7
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Re: Convert Subnet address and mask to CIDR and vice-versa

    Yeah. Me too. Thanks so much for trying and the prompt replies though. Hopefully I'll get an easy answer for all VB haters, or I'll work out how to use the scripts and explain here for lazy duffers like me.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Convert Subnet address and mask to CIDR and vice-versa

    You can try this way:

    module.jpg

    then copy code and paste into white window on the right side then save file as xlsm (macro enabled)

  9. #9
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Re: Convert Subnet address and mask to CIDR and vice-versa

    OK. I really was being lazy and technophobic. IPCALC does it nicely. I can get the values i need with the IPMASK and IPWITHOUTMAK functions and then concatenate and vlookup. You just open the IP-CALC.XLSM and then you can see the functions in the Formulas, Insert function menu. I don't know how to get them in my actual spreadsheet but I will skirt that by copying the function results pasting just values when I have done the Vlookup. Many thanks

  10. #10
    Registered User
    Join Date
    02-16-2018
    Location
    Norfolk, UK
    MS-Off Ver
    O365
    Posts
    6

    Re: Convert Subnet address and mask to CIDR and vice-versa

    I tried that too and it confused the hell out of me! I seem to remember it doing the same about 10 years ago when I dipped my feet in the vba world. Even as technicians, we all have those technical places where we just don't want to go! I'll put my grown up pants on later and see if I can do it. Meanwhile I'm just happy I can plough ahead with working out which subnets I have got covered with the tool I am using that doesn't really work, but the coverage stats will keep the managers happy for the meanwhile anyway.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Convert Subnet address and mask to CIDR and vice-versa

    You are welcome

+ 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. Convert Decimal fractions to binary and vice versa
    By vinbhoo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-11-2014, 11:04 AM
  2. Replies: 3
    Last Post: 12-12-2013, 11:13 AM
  3. 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
  4. 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
  5. Excel 2007 : tcpip IP Network Address/Subnet Mask/IP Range
    By dan_in-need-of-help in forum Excel General
    Replies: 1
    Last Post: 08-10-2010, 03:16 AM
  6. How to convert binary to decimal & vice versa?
    By hmmm in forum Excel General
    Replies: 5
    Last Post: 08-02-2005, 11:05 PM
  7. [SOLVED] How do I move excel address lists to outlook. and vice versa.
    By Lost in Office in forum Excel General
    Replies: 1
    Last Post: 04-09-2005, 12:06 PM

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