Accumulate an IP address, in the last octet by +1

[bstammer]

Hello,

I have been searching for an answer to this for awhile. I have found variations of this question, but I have not been able to find quite what I need. Perhaps someone can help?

I have a cell (B10) that has an IP address in it. I want cell B11 to automatically reflect the same first 3 octets of B10, but the last octet accumulated by +1.

Example: 172.23.0.128 would be in B10. I would like to have a formula in B11 to increment B10 by +1 to show 172.23.0.129.

Does that make sense what I am looking for?

Thanks!

[shg]

The easy way would be to split the bytes into separate columns using text to columns.

[judgeh59]

what do you want to do if the octet is 255?

[AndyLitch]

Formula in B11 would be

=CONCATENATE(LEFT((VALUE(CONCATENATE(LEFT(B10,3),MID(B10,5,2),MID(B10,8,1),RIGHT(B10,3)))+1),3)& ".",MID((VALUE(CONCATENATE(LEFT(B10,3),MID(B10,5,2),MID(B10,8,1),RIGHT(B10,3)))+1),4,2) & ".",MID((VALUE(CONCATENATE(LEFT(B10,3),MID(B10,5,2),MID(B10,8,1),RIGHT(B10,3)))+1),6,1) & ".",RIGHT((VALUE(CONCATENATE(LEFT(B10,3),MID(B10,5,2),MID(B10,8,1),RIGHT(B10,3)))+1),3))

It's a bit clutzy but it when pasted down through column B will give you all incremental values of IP address to 99999999

[bstammer]

AndyLitch

It would appear that did not work.

B10 contained 172.23.20.0
Upon placing the formula in B11 the result was 172.23.2.321

To answer the other question, the last octet of the IP would never exceed 255, so an additional condition is not needed.

?

[AndyLitch]

The formula required consistent (fixed length) formatting....I thought IP addresses were formatted that way...My mistake

[bstammer]

I do see now how what you provided was correct based on the IP I provided you. So what this basically means (I am guessing) is that dependant on the amount of digits in the originating number would determine the formula used.

I think I am starting to get it, but modifying the formula is probably going to be the hangup for me moving forward.

Is it dependant on the amount of digits? If so then I think I would actually only need to know the formula for the third octet being 2 characters, as well the formula for the 3rd octet being 3 characters.

Thank you very much!

[AndyLitch]

So the consistent feature of an IP address is the number of stops ..(3) so we need to
1. find the position of that 3rd sto
2. Take the digits after it (Right)
3. Increment by 1 but only up to 255
4. Recompose the string ...
........... I don't have the answer to step 1 but i do know it's out there