Creating New IP Addresses In Excel 2010

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-text-formulas/creating-new-ip-addresses.html
SHARE




In this article, we will learn how to create new IP address.

If you want a formula that will generate IP Address, you can use combination of LEFT & MID functions.

 

LEFT: Returns the specified number of characters from the start of a text string.

 

Syntax: =LEFT(text,num_chars)

 

text: It is the text string that contains characters that you want to extract

num_chars: It specifies the number of characters from left you want to extract.

 

 

MID: Returns the characters from the middle of a text string, given a starting position and length.

Syntax: =MID(text,start_num,num_chars)

 

text: The text string containing the characters you want to extract.

start_num: The number in the string from which you want to extract data.

num_chars: Itspecifies the number of characters you want from MID to retrieve or extract.

 
Let us take an example:

  • In cell A2, a fictitious IP number has been entered.

 
img1
 

  • In cell B2, we want a formula that will return a new IP address based on the corresponding cell in column A.
  •  

  • In cell B2, the formula would be
    =(LEFT(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)))&(MID(A2,FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1,99)*2)

 
img2
 
Using the tips mentioned in the article, we can create new IP address.
 
 

Please follow and like us:
0


Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>