IP address

sandeep28585

New Member
Joined
Mar 25, 2020
Messages
3
Office Version
2016
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,186
Office Version
2007
Platform
Windows
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

New Member
Joined
Mar 25, 2020
Messages
3
Office Version
2016
Platform
Windows
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

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
Can you post few lines of sample, the current formula you are using, the results you are getting and the expected correct results
 

sandeep28585

New Member
Joined
Mar 25, 2020
Messages
3
Office Version
2016
Platform
Windows
subnetmaskBreakupmask
10.0.0.0255.255.224.010.0.0.1255.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.0255.255.192.0
#VALUE!​
255.255.1123.0
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,186
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,089,218
Messages
5,406,920
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top