formula question...

[billh101]

I hope this isn't a stupid question, but I'm not very experienced with
Excel. I have a spreadsheet with a column of IP addresses, like
192.168.100.4 . I need to add another column beside it with the subnet
masks, which are the same except .254 instead of .4 (ie.
192.168.100.254). How can I write a formula to change the number at
the end after the last decimal point?
Thanks for your assistance.
Bill

 

[Gerry Kuta]

Not exactly sure of your intent here but the following
will copy the first three components of the address. You
then might be able to figure out something for your needs
perhaps with CONCATENATE from a helper column.

=LEFT(A1&".0.0.",FIND("$",SUBSTITUTE
(A1&".0.0.",".","$",3))-1)

HTH

 

[billh101]

Excellent, I'll try it out. Thanks!

Bill

*Not exactly sure of your intent here but the following
will copy the first three components of the address. You
then might be able to figure out something for your needs
perhaps with CONCATENATE from a helper column.

=LEFT(A1&".0.0.",FIND("$",SUBSTITUTE
(A1&".0.0.",".","$",3))-1)

HTH

-----Original Message-----
I hope this isn't a stupid question, but I'm not very experienced with
Excel. I have a spreadsheet with a column of IP addresses, like
192.168.100.4 . I need to add another column beside it with the subnet
masks, which are the same except .254 instead of .4 (ie.
192.168.100.254). How can I write a formula to change the number at
the end after the last decimal point?
Thanks for your assistance.
Bill



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.
*

 

[Ron Rosenfeld]

I hope this isn't a stupid question, but I'm not very experienced with
Excel. I have a spreadsheet with a column of IP addresses, like
192.168.100.4 . I need to add another column beside it with the subnet
masks, which are the same except .254 instead of .4 (ie.
192.168.100.254). How can I write a formula to change the number at
the end after the last decimal point?

=REPLACE(A1,FIND("~",SUBSTITUTE(A1,".","~",3)),4,".254")


--ron