formula for adding 1 to last octet in an IP

[I2omani]

Howdy i have a question:
cell L4 is a result of a vlookup that returns an IP address. then L4 is refrenced somewhere else in the document in a CONCATENATE function.

everything works perfect except i need to increase the last octet by one and is the result is more that .254 then it will increase the 3rd octet.

for example:
L4=192.168.1.2 and i want it to be 192.168.1.3 when called in the CONCATENATE function, but lets say L4 is 192.168.1.254 , i want the formula to add one to the 3rd octet , like this 192.168.2.1.

is this even possible to do , if not i will just stick with adding one to the 4th octet.

thank you so much in advance

 

[ChrisM]

Here's a partial answer for you. The formula below will return the last octet value. Use the returned value in another formula and add one to it to increase your IP count.

=TRIM(REPLACE(H13,1,FIND("^^",SUBSTITUTE(TRIM(H13),".","^^",LEN(TRIM(H13))-LEN(SUBSTITUTE(TRIM(H13),".","")))),""))

Replace H13 with your vlookup cell.

To do:
1. Create a formula to return the first three octets (variant of the formula above).

2. Create logic to increase the 3rd octet if necessary. This can be done with If statements and a modification of the above formula to return the 3rd octet.

Sorry, have to get back to work!

 

[I2omani]

gotchya, thank you so much sir. but now you let my head twisting already with your formula it work perfect , except i am missing the fist 3 octets

thank you again

 

[pgc01]

Hi

You could do it with vba, but this is a formula solution. In B1:

=LEFT(A1,FIND(".",SUBSTITUTE(A1,"."," ",1)))&(LOOKUP(256,INT(0+MID(A1,FIND(".",SUBSTITUTE(A1,"."," ",1))+1,{1,2,3})))+(RIGHT(A1,3)="254"))&"."&IF(RIGHT(A1,3)="254","1",RIGHT(SUBSTITUTE(A1,".",REPT(" ",3)),3)+1)

Copy down


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.1.2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.1.3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.3.4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.3.5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.3.253</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.3.254</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.3.254</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.2.4.1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.123.1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.123.2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.123.253</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.123.254</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.123.254</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">123.123.124.1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

 

[I2omani]

thank you so much sir, this saved my life.
i have a question, if i want to analyize your formula or any other formula , how do i read to make sense to me and see what each function is doing: i.e

Hi

You could do it with vba, but this is a formula solution. In B1:

=LEFT(A1,FIND(".",SUBSTITUTE(A1,"."," ",1)))&(LOOKUP(256,INT(0+MID(A1,FIND(".",SUBSTITUTE(A1,"."," ",1))+1,{1,2,3})))+(RIGHT(A1,3)="254"))&"."&IF(RIGHT(A1,3)="254","1",RIGHT(SUBSTITUTE(A1,".",REPT(" ",3)),3)+1)

which one do i resolve first, in the above example ? was it REPT() and then goes to SUBSITITUTE() etc or its the other way around LEFT() then FIND()?

also can you help me with what was your thoughts to get this accomplished? how did you think about it please?
teach and man to fish and he will eat for the rest of his days sort a thing

 

[pgc01]

Hi

I'm glad it helped.

also can you help me with what was your thoughts to get this accomplished? how did you think about it please?

I used a Top Down approach. The way I saw it, I'd just have to concatenate 3 parts

1) the first 2 octets

concatenated with

2) the third octet, adding 1 unit if the IP address ends with 254

concatenated with

3) the fourth octet +1, except if the fourth octet is 254, in which case I use 1 for the fourth octet


Drilling down on 1):

To get the first 2 octets, an easy way is to

1.1) substitute the first dot in the IP address with any other character
1.2) get the position of the first dot (after the substitution)
1.3) get the left part of the string up to the position calculated in 1.2)

So, for 1) with the expression:

LEFT(A1,FIND(".",SUBSTITUTE(A1,"."," ",1)))

,using for A1 the value "192.168.1.2"

1.1) SUBSTITUTE("192.168.1.2","."," ",1), result: 192 168.1.2
1.2) FIND(".","192 168.1.2"), result: 8 (position of the first dot)
1.3) LEFT("192.168.1.2",8), result: "192.168."

I hope this gives an idea of how I built the solution.

 

[b1183642]

Hi

You could do it with vba, but this is a formula solution. In B1:

=LEFT(A1,FIND(".",SUBSTITUTE(A1,"."," ",1)))&(LOOKUP(256,INT(0+MID(A1,FIND(".",SUBSTITUTE(A1,"."," ",1))+1,{1,2,3})))+(RIGHT(A1,3)="254"))&"."&IF(RIGHT(A1,3)="254","1",RIGHT(SUBSTITUTE(A1,".",REPT(" ",3)),3)+1)

Hello,

I found an issue in the formula, and since you are the expert, and I am the newb, Would you be able to help me correct this awesome formula?

When you go over 255, in the 3 octet from the left. It becomes 256, which I thought was related to the LOOKUP(256,INT...), but it didn't work when I changed it to LOOKUP(255,INT...)

[TABLE="width: 117"]
<tbody>[TR]
[TD]192.193.255.254[/TD]
[/TR]
[TR]
[TD]192.193.256.1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]192.193.256.254[/TD]
[/TR]
[TR]
[TD]192.193.257.1[/TD]
[/TR]
[TR]
[TD]192.193.25.2[/TD]
[/TR]
[TR]
[TD]192.193.25.3[/TD]
[/TR]
[TR]
[TD]192.193.25.4[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank You, so much for helping me save thousands of edits.

 

[shyy]

NVM misread what the OP wanted

 

[b1183642]

NVM misread what the OP wanted

Sorry, I missed your post. Did you have a solution?