» Creating New IP Addresses
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Creating new IP addresses composed of the first three original segments coupled with the last original segment multiplied by two.
Solution:
Use the LEFT, FIND, and MID functions as shown in the following formula:
=(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)))&(MID(A2,
FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,99)*2)
Book Store:
Recommended Books:
- Accounting for Dummies
- Special Edition Using Microsoft Excel 2002
- Treason: Liberal Treachery from the Cold War to the War on Terrorism
- Millionaire Real Estate Mentor : The Secrets of Financial Freedom through Real Estate Investing
- Mortgages For Dummies®
- How to Use Financial Statements: A Guide to Understanding the Numbers
One fault
excel newbee
Can we make formula to take care of constraints on the IP address? ... checking for values greater than 256 et al.
I think it should read like this:
=(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)))&(MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,99)*2)
Another thought on this problem:
=REPLACE(A2,FIND("~",SUBSTITUTE(A2,".","~",3)),99,"." &MID(A2,FIND("~",SUBSTITUTE(A2,".","~",3))+1,99)*2)