Formula with IP octects Required

[Rui Pires]

Dear All,

I am looking for a formula,


I am looking for a formula to solve the cells B3: BX.

Knowing that the Ips cells from A1: AX can be variables

because in each octet can be up to 3 numbers.

I just want that in B3: BX present only the IP to the left of the "/", as seen in B2.

B2 (= MID (A2;FIND (".";A2) -3; 12)) only works with 12 characters.





Thanks in advance

Regards,

Rui Pires

 

[Somendra Misra]

Hi Try,

=LEFT(H8,FIND("/",H8)-1)

Put your cell ref.

Regards,

 

[Rui Pires]

Thank you very much , Its work!!
And if I want the Ip only up to "." ?? i.e 172.1.180. ??

 

[Somendra Misra]

Try below array formula:

=LEFT(H8,MAX(IF(MID(H8,ROW(INDIRECT("1:"&LEN(H8))),1)=".",ROW(INDIRECT("1:"&LEN(H8))))))

Enter with Ctrl+Shift+Enter.

Regards,

 

[Rui Pires]

Try below array formula:

=LEFT(H8,MAX(IF(MID(H8,ROW(INDIRECT("1:"&LEN(H8))),1)=".",ROW(INDIRECT("1:"&LEN(H8))))))

Enter with Ctrl+Shift+Enter.

Regards,

Great thanks

 

[Somendra Misra]

You are Welcome!!!

Regards,

 

[Haseeb A]

...or you can use the below one with just ENTER,

=LEFT(A1,LOOKUP(32768,FIND("*",SUBSTITUTE(A1,".","*",{1,2,3,4,5,6,7,8,9}))))

 

[NARAYANK991]

Hi ,

Since an IP address has a standard format , a much simpler formula would be :

=LEFT(A1,FIND("|",SUBSTITUTE(A1,".","|",3)))

where A1 contains your IP address.

Narayan