Compare last octet of IP address

A

Ant

Seems simple
however I've tried using some kind of combination of substitue /
find / mid and right functions however I can't seem to get it to
work..

please don't tell me not to use spreadsheets for ip address
management / natting (Thanks)

10.10.15.35 should match 192.168.95.35 but not match 192.158.96.135

can someone help here?

Thanks.
 
R

Rick Rothstein \(MVP - VB\)

You didn't give us any layout information or indication of how you want to
process whatever that layout is; however, you can use the following to find
the last octet of an IP address. Assume A1 contains an IP address, then the
last octet would be found with this...

=MID(A1,1+FIND("/",SUBSTITUTE(A1,".","/",3)),3)

Rick
 
R

Ron Rosenfeld

Seems simple
however I've tried using some kind of combination of substitue /
find / mid and right functions however I can't seem to get it to
work..

please don't tell me not to use spreadsheets for ip address
management / natting (Thanks)

10.10.15.35 should match 192.168.95.35 but not match 192.158.96.135

can someone help here?

Thanks.
Click to expand...

You need to extract the last octet which you can do with a formula:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),3))+1,3)

You could set up a separate column with just the terminal octet, and compare
the values.

Or, if you just wanted to compare the IP address in A1 and A2, you could use
this formula:


=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),3))+1,3)
=MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3))+1,3)


--ron
 
O

Ollie4

first you have to trim the ip-adres from the right to the first dot
(reading form right to left) so

10.10.15.35 becomes .35 and 192.158.96.135 becomes .135

to do this you use the unique caracter of the ip address: it has 3
dots!

to trim an ip in cel A2 use
=RIGHT(A2;FIND(".";A2;FIND(".";A2;FIND(".";A2;1))))
te formula trims from the right starting at the position of the 3th
dot

do the same for all other ips and compare the last octets

Hope it helps
Ollie4
 
O

Ollie4

Oeps, and if you use a dot as decimal seperator the formula is

=RIGHT(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2,1))))
 
A

Ant

Thank you to everyone, I got exactly what I wanted thanks :

=IF(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3))
+1,3)=MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,".",CHAR(1),3))
+1,3),"Match","NoMatch")
 
R

Ron Rosenfeld

Thank you to everyone, I got exactly what I wanted thanks :

=IF(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3))
+1,3)=MID(B2,FIND(CHAR(1),SUBSTITUTE(B2,".",CHAR(1),3))
+1,3),"Match","NoMatch")
Click to expand...

You're welcome. Thanks for the feedback. Glad to help.
--ron
 
Top