Jun
4
2013

Working in Excel with IP Range – Tip

 

While working in excel for IP addresses, got a task to generate new IP address based on an existing one in order to configure a device, after doing some Googling find few articles on this topic, there are two good approaches to it,

 

1. Using excel formula like REPLACE, MIN, LEN, MID, CONCATENATE, calculate new IP range, for e.g.

Suppose your IP address is 10.76.192.0 and you like to generate new IP address as 10.76.(192+1).(0+15) then you can use following formula,

 

=CONCATENATE(REPLACE(A2, FIND(“.”,A2,FIND(“.”,A2)+1)+1, LEN(A2), MIN(255, MID(A2, FIND(“.”,A2,FIND(“.”,A2)+1)+1, LEN(A2)) + 1)),”.15″)

 

Similarly, if you just want to increase the last value in octet for e.g. 10.76.192.(0+15), then

    =REPLACE(A2, FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1, LEN(A2), MIN(255, MID(A2, FIND(“.”,A2,FIND(“.”,A2,FIND(“.”,A2)+1)+1)+1, LEN(A2)) + 15))

 

If you like full control of each bit of IP address then solution is available at following link, http://stuart.weenig.com/2011/05/excel-and-ip-addresses.html

=VALUE(LEFT(A1,FIND(“.”,A1)-1))*2^24+VALUE(MID(A1,FIND(“.”,A1)+1,FIND(“.”,A1,FIND(“.”,A1)+1)-FIND(“.”,A1)-1))*2^16+VALUE(MID(A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)-FIND(“.”,A1,FIND(“.”,A1)+1)-1))*2^8+VALUE(RIGHT(A1,LEN(A1)-FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1)+1)+1)))

 

2. Using VBA function,

Reference: http://www.excelforum.com/excel-general/548005-how-do-create-a-formula-to-add-ip-addresses.html

 

a)

VBA Function-

Function NextIPAddress(IP As String) As String

Dim Arr As Variant

Arr = Split(IP, “.”)

Arr(UBound(Arr)) = Arr(UBound(Arr)) + 1

NextIPAddress = Join(Arr, “.”)

End Function

 

You can then call this from a worksheet cell with code like

=NextIPAddress(A1)

 

b)

Function NextIPAddress(IP As String) As String

Dim Arr As Variant

Dim V As Integer

Arr = Split(IP, “.”)

V = CInt(Arr(UBound(Arr)))

V = V + 1

If V Mod 4 = 0 Then

Arr(UBound(Arr)) = V

Else

V = V + 4 – (V Mod 4)

Arr(UBound(Arr)) = CStr(V)

End If

NextIPAddress = Join(Arr, “.”)

End Function

 

Other useful Reference: http://stackoverflow.com/questions/1111577/how-to-get-next-ip-in-range-from-excel

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 23 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

November 2017
M T W T F S S
« Oct    
 12345
6789101112
13141516171819
20212223242526
27282930  

View Post by Categories

%d bloggers like this: