» 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:
- Advanced modelling in finance using Excel and VBA
- Investments + S&P Card + Powerweb + StockTrak discount coupon
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Financial Peace: Revisited
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)