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.

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


"Ant" wrote in message
...
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.

On Tue, 5 Feb 2008 13:40:35 -0800 (PST), Ant wrote:

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.

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

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


On 5 feb, 22:40, Ant wrote:
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.

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

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




On 5 feb, 23:03, Ollie4 wrote:
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

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")


On Feb 5, 5:10 pm, Ollie4 wrote:
Oeps, and if you use a dot as decimal seperator the formula is

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

On 5 feb, 23:03, Ollie4 wrote:

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

On Tue, 5 Feb 2008 14:16:32 -0800 (PST), Ant wrote:

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")

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