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

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. ## 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:

- 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

3. ## 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-)

5. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Convert Subnet address and mask to CIDR and vice-versa

You are welcome

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

#### 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