formula question...

B

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
 
G

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
 
B

billh101

Excellent, I'll try it out. Thanks!

Bill

Gerry said:
*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/

.
*
Click to expand...
Click to expand...
 
R

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?
Click to expand...


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


--ron
 
Top