Increment the 3rd octet in excel by value of 4-CodePudding

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!

CodePudding user response：

You could have a formula that adds 4 to the third octet like this:

=TEXTJOIN( ".", 1,
           {0;0;4;0}  
           FILTERXML( "<m><s>" & SUBSTITUTE( A1, ".", "</s><s>" ) & "</s></m>", "//s" ) )

where A1 is where the target string "10.0.0.0" is held and the 4 is clearly in the third position. You could also make this a generalized solution by putting in the offset that you want in the '{0;0;4;0}' array.

hmmmm - after further reflection, you need to ensure that the octets role over at 255. Here is a safer version:

=TEXTJOIN( ".", 1,
           BITAND( {0;0;4;0}  
                     FILTERXML( "<m><s>" & SUBSTITUTE( A1, ".", "</s><s>" ) & "</s></m>", "//s" ),
                   {255;255;255;255} ) )

CodePudding user response：

Try to set up table as below

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

2] In Criteria 2 E2, enter any value of replacement

Then,

3] In Result B2, enter formula :

=IFERROR(LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@",D2-1))-1)&".","")&E2&MID(A2,FIND("@",SUBSTITUTE(A2&".",".","@",D2)),99)

Remark : Try to change the Octet number by 1 to 4 and Replaced value in testing of the result