» 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:
- Cashflow Quadrant: Rich Dad's Guide to Financial Freedom
- Accounting and Financial Fundamentals for Nonfinancial Executives
- Microsoft Excel Version 2002 Step by Step
- Microsoft Windows XP Inside Out
- Financial Statement Analysis: A Practitioner's Guide, 3rd Edition
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
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)