Creation of IP Address Ranges

[meppwc]

I am trying to automate the ability to create IP address ranges
An example
Cell A1 has the value of 10.127.65.16
In cell B1 I want to keep the first 3 octets and replace the last octet with 10 (end result for B1 is 10.127.65.10)
In cell C1 I want to keep the first 3 octets and replace the last octet with 99 (end result for C1 is 10.127.65.99)

Is there a way of accomplishing this with formulas in B1 and C1 ?

 

[RoryA]

You could use:

=TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",1000),3),1000))&".10"
=TRIM(LEFT(SUBSTITUTE(A1,".",REPT(" ",1000),3),1000))&".99"

 

[Fluff]

How about
=TRIM(LEFT(SUBSTITUTE(A1,".",".10"&REPT(" ",10),3),15))
=TRIM(LEFT(SUBSTITUTE(A1,".",".99"&REPT(" ",10),3),15))

 

[meppwc]

Thank you both so much. This will save me tons of time.

 

[Fluff]

Glad we could help & thanks for the feedback