» 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:
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Microsoft Word Version 2002 Inside Out
- Analysis of Financial Statements
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- The Sweet Potato Queens' Big-Ass Cookbook and Financial Planner
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
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)