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

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

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

or maybe this one: link

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

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

have a nice day

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.

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)

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

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.

You are welcome