Finding IPs covered by Subnets in an Excel document
up vote
0
down vote
favorite
So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two octets of the subnetted IP. What I'm wondering is if there is any way with excel I can check if the first two octets of an IP MATCH one of the rows with an /16 subnet. Basically I want to know if there is a way to find entrys that override each other. Ex 192.168.1.1 192.168.0.0 /16, the 192.168.1.1 would be highlighted red or whatever. I know you would use an if, or at least I think, but I can't figure out how I would split the two octets and compare them, and also factor in the fact that the one it needs to be compared with has to have an "/16" in the same row.
Any help would be greatly appreciated, and if this makes no sense whatsoever, comment and I'll try to clarify.
Thanks!
microsoft-excel worksheet-function ip
asked Jan 8 '14 at 3:51
Ethan
111
bumped to the homepage by Community♦ 2 days ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
up vote
0
down vote
favorite
So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two octets of the subnetted IP. What I'm wondering is if there is any way with excel I can check if the first two octets of an IP MATCH one of the rows with an /16 subnet. Basically I want to know if there is a way to find entrys that override each other. Ex 192.168.1.1 192.168.0.0 /16, the 192.168.1.1 would be highlighted red or whatever. I know you would use an if, or at least I think, but I can't figure out how I would split the two octets and compare them, and also factor in the fact that the one it needs to be compared with has to have an "/16" in the same row.
Any help would be greatly appreciated, and if this makes no sense whatsoever, comment and I'll try to clarify.
Thanks!
microsoft-excel worksheet-function ip
asked Jan 8 '14 at 3:51
Ethan
111
bumped to the homepage by Community♦ 2 days ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two octets of the subnetted IP. What I'm wondering is if there is any way with excel I can check if the first two octets of an IP MATCH one of the rows with an /16 subnet. Basically I want to know if there is a way to find entrys that override each other. Ex 192.168.1.1 192.168.0.0 /16, the 192.168.1.1 would be highlighted red or whatever. I know you would use an if, or at least I think, but I can't figure out how I would split the two octets and compare them, and also factor in the fact that the one it needs to be compared with has to have an "/16" in the same row.
Any help would be greatly appreciated, and if this makes no sense whatsoever, comment and I'll try to clarify.
Thanks!
microsoft-excel worksheet-function ip
asked Jan 8 '14 at 3:51
Ethan
111
So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two octets of the subnetted IP. What I'm wondering is if there is any way with excel I can check if the first two octets of an IP MATCH one of the rows with an /16 subnet. Basically I want to know if there is a way to find entrys that override each other. Ex 192.168.1.1 192.168.0.0 /16, the 192.168.1.1 would be highlighted red or whatever. I know you would use an if, or at least I think, but I can't figure out how I would split the two octets and compare them, and also factor in the fact that the one it needs to be compared with has to have an "/16" in the same row.
Any help would be greatly appreciated, and if this makes no sense whatsoever, comment and I'll try to clarify.
Thanks!
microsoft-excel worksheet-function ip
microsoft-excel worksheet-function ip
asked Jan 8 '14 at 3:51
Ethan
111
asked Jan 8 '14 at 3:51
Ethan
111
asked Jan 8 '14 at 3:51
Ethan
111
asked Jan 8 '14 at 3:51
Ethan
111
asked Jan 8 '14 at 3:51
Ethan
111
111
bumped to the homepage by Community♦ 2 days ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 2 days ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16
add a comment |
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be extremely useful.
http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/
I found that the formula for the second octet was a little buggy, so I reworked it.
Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)
Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
answered Oct 16 '14 at 10:17
David Kennedy
1
add a comment |
up vote
-1
down vote
Try this for fourth octet.
TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))
answered Sep 11 at 21:40
Chris Germany
1
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be extremely useful.
http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/
I found that the formula for the second octet was a little buggy, so I reworked it.
Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)
Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
answered Oct 16 '14 at 10:17
David Kennedy
1
add a comment |
up vote
0
down vote
I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be extremely useful.
http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/
I found that the formula for the second octet was a little buggy, so I reworked it.
Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)
Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
answered Oct 16 '14 at 10:17
David Kennedy
1
add a comment |
up vote
0
down vote
up vote
0
down vote
I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be extremely useful.
http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/
I found that the formula for the second octet was a little buggy, so I reworked it.
Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)
Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
answered Oct 16 '14 at 10:17
David Kennedy
1
I think the easiest thing to do is to break up the IP into octets first. Then you could use concatenate to rejoin octets 1 & 2. From there you can use conditional formatting, vlookup or whatever method you like to compare the values.
I was looking for some guidance on manipulating IPs for a similar pet project and found the following article to be extremely useful.
http://chentiangemalc.wordpress.com/2011/02/03/geeky-excel-formulas-subnet-math-group-by-subnet-using-built-in-excel-2010-formulas/
I found that the formula for the second octet was a little buggy, so I reworked it.
Return Octet 1 of the IP in Cell A2
=LEFT(A2,FIND(".",A2)-1)
Return Octet 2 of the IP in Cell A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Return Octet 3 of the IP in Cell A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Return Octet 4 of the IP in Cell A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
answered Oct 16 '14 at 10:17
David Kennedy
1
answered Oct 16 '14 at 10:17
David Kennedy
1
answered Oct 16 '14 at 10:17
David Kennedy
1
answered Oct 16 '14 at 10:17
David Kennedy
1
1
add a comment |
add a comment |
up vote
-1
down vote
Try this for fourth octet.
TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))
answered Sep 11 at 21:40
Chris Germany
1
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
add a comment |
up vote
-1
down vote
Try this for fourth octet.
TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))
answered Sep 11 at 21:40
Chris Germany
1
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
add a comment |
up vote
-1
down vote
up vote
-1
down vote
Try this for fourth octet.
TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))
answered Sep 11 at 21:40
Chris Germany
1
Try this for fourth octet.
TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",15)),15))
answered Sep 11 at 21:40
Chris Germany
1
answered Sep 11 at 21:40
Chris Germany
1
answered Sep 11 at 21:40
Chris Germany
1
answered Sep 11 at 21:40
Chris Germany
1
1
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
add a comment |
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
Please explain how this words. Please do not respond in comments; edit your answer to make it clearer and more complete.
– Scott
Sep 12 at 0:14
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f698495%2ffinding-ips-covered-by-subnets-in-an-excel-document%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Sorting Excel column by IP address: superuser.com/questions/620533/…
– STTR
Jan 8 '14 at 7:39
Without zero-padding, writing formulas to handle IPs properly in Excel can be tricky. I was starting to come up with some fairly simple ideas, but they won't work right unless each octet is exactly three digits long.
– Iszi
Aug 1 '14 at 22:06
Octets really have nothing to do with subnetting. The dotted-decimal notation using octets is simply to make IPv4 addresses easier for humans to read. Subnet boundaries can be inside an octet.
– Ron Maupin
Oct 17 at 0:16