Formula Question

  • Thread starter Thread starter Steve Abrams
  • Start date Start date
S

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
 
Would not clicking on the cell and dragging down from the
left of the box do what you want?
 
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")
 
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
 
Back
Top