Formula Question

[Steve Abrams]

Hi,

I'm trying to make a formula that will increase an IP address
by 1 and I'm running into my inexperience with Excel.

If I have an address, 211.252.191.105, I can break it into two pieces
but can seem to get it to increment.

=LEFT(A1,LEN(A1)-FIND(".",A1,1))&(RIGHT(A1,FIND(".",A1,1))) splits
the it up into seperate pieces,

211.252.191 .105

Does anyone know how to make 105 add 1 to 106? I'm hoping I'm on the
right track.

Thank you,

Steve

 

[GerryK]

Would not clicking on the cell and dragging down from the
left of the box do what you want?

 

[Guest]

Steve,

This formula will do what you want, but you'll need to modify it if the last
group has less than three digits. This should get you started, though.

=LEFT(A1,LEN(A1)-FIND(".",A1,1))&TEXT((VALUE((RIGHT(A1,FIND(".",A1,1))))*1000+1)/1000,".000")

 

[RagDyer]

This should work for what you're looking for.

However, as I understand it, the maximum value for an octet is 255, at which
point, the *previous* octet should increment by one.
For example:
199.199.199.255
increased by 1 should become
199.199.200.0

This formula will *not* perform that type of increment.

=IF(A1<>"",LEFT(A1,LEN(A1)-MATCH(".",LEFT(RIGHT(A1,{2,3,4}),1),0))&(RIGHT(A1
,MATCH(".",LEFT(RIGHT(A1,{2,3,4}),1),0))+1),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi,

I'm trying to make a formula that will increase an IP address
by 1 and I'm running into my inexperience with Excel.

If I have an address, 211.252.191.105, I can break it into two pieces
but can seem to get it to increment.

=LEFT(A1,LEN(A1)-FIND(".",A1,1))&(RIGHT(A1,FIND(".",A1,1))) splits
the it up into seperate pieces,

211.252.191 .105

Does anyone know how to make 105 add 1 to 106? I'm hoping I'm on the
right track.

Thank you,

Steve

 

[Steve Abrams]

This should work for what you're looking for.

RagDyer and JBoulton,

Thank you both for your responses. They are greatly
appreciated!

Steve