» 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:
- Not-for-Profit Accounting Made Easy
- Microsoft Office XP Introductory Concepts and Techniques
- How to Pay Zero Taxes (Annual)
- Microsoft Word Version 2002 Step By Step (With CD-ROM)
- Microsoft Excel Version 2002 Step by Step
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
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)