excelnetwork-programmingexcel-formulaip-address

Increment the 3rd octet in excel by value of 4

I have a cell in excel with an ip address of 10.0.0.0 but I want to increment the third octet by 4. so the output of the next cell below will be 10.0.4.0

How can i make this happen without any complicated VB scripts if possible.

Thanks!

Solution

  • Formula solution for Excel 2019 and up

    Try to set up table as below

    1] In Octet D2, enter position number of Octet by 1 to 4

    2] In Increment E2, enter any value of Increment

    Then,

    3] In Result B2, enter array (CSE) formula :

    =TEXTJOIN(".",1,BITAND(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",50)),{1;50;100;150},50))+IF(ROW(1:4)=D2,E2,0),{255;255;255;255}))

    Or,

    Using Filterxml function similar to mark fitzpatrick's formula.

    In Result B2, enter array (CSE) formula :

    =TEXTJOIN(".",1,BITAND(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","//b")+IF(ROW(1:4)=D2,E2,0),{255;255;255;255}))

    enter image description here

    Remark :

    1] Try to change the Octet number by 1 to 4 and Increment value in testing of the result.

    2] For Office 365 the formula will be normal entry