IP address

[sandeep28585]

Hi ,
I was trying to run a formula where if any bigger subnet (/20,/19) added to column and it will fill the /24 in another column.
I was somewhat succeeded for the subnet which i am using but if the subnet changes then the formua did not worked for me.
As an example :

I tried for 10.110.120.0 and it worked but if the any changes on the first three octets it did not worked.I am not sure whether this can be achieved with formulas or any scripting is required.

 

[DanteAmor]

Hi and welcome to MrExcel!

You can put some examples of what you would have originally.

Then what change do you make and what result do you expect.

 

[sandeep28585]

Thanks Dante.I have multiple subnets of /20 and want to break into /24 .For example 10.0.0.0/20 needs to be breakdown into 10.0.0.1/24 and more.
I was succeded using left and mid formula but if i change the 2nd and 3rd octet of /20 the result is not what i was expecting.Hope this calrifies.

 

[sanrv1f]

Can you post few lines of sample, the current formula you are using, the results you are getting and the expected correct results

 

[sandeep28585]

subnet	mask		Breakup	mask
10.0.0.0	255.255.224.0		10.0.0.1	255.255.255.0
		Formula i used	=LEFT(A2,7)&MID(A2,8,8)+1	=LEFT(B2,9)&MID(B2,10,10)+31&".0"
Issue arises if I changed the octets
172.168.0.0	255.255.192.0		#VALUE!​	255.255.1123.0

 

[DanteAmor]

Sorry, but I still don't understand.

Could you put maybe 10 example and the result of those 10 examples.
And explain 4 of those examples.

But we need to see the final results. It doesn't help that you put #VALUE !, we need to see the result that you need.