Calculated Column – Subnet from IP Address

SharePoint is extremely useful for organizing information, and has an Excel-like capability to slice and dice your information by using “calculated columns”.  For now I want to keep track of any columns I use that took some thought, so here goes…

IP-Subnet from [AD-IPv4Address] column…

=CONCATENATE(LEFT([AD-IPv4Address],FIND(“.”,[AD-IPv4Address])),LEFT(RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))),FIND(“.”,RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))))),LEFT(RIGHT(RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))),(LEN(RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))))-FIND(“.”,RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address])))))),FIND(“.”,RIGHT(RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))),(LEN(RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address]))))-FIND(“.”,RIGHT([AD-IPv4Address],(LEN([AD-IPv4Address])-FIND(“.”,[AD-IPv4Address])))))))),”0″)

OK now say that five time fast! I am not sure if this is the most efficient way to get this done, but here is what I did to put this together. In Excel, I started dividing and conquering as far as getting the portions of the IP address that I wanted to keep…

Step IP fragments Info
1 10.0.12.13 A sample IP address
2 3 locating the first period:
3 10. isolating the first octet
4 0.12.13 and the rest of the string
5 2 locating the second period
6 0. isolating the second octet
7 12.13 and the rest of the string
8 3 locating the third period
9 12. isolating the third octet

The Excel formulas that got me each line…

Step Excel formula
1
2 FIND(“.”,G16)
3 LEFT(G16,FIND(“.”,G16))
4 RIGHT(G16,(LEN(G16)-FIND(“.”,G16)))
5 FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))
6 LEFT(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))),FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16)))))
7 RIGHT(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))),(LEN(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))-FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))))
8 FIND(“.”,RIGHT(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))),(LEN(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))-FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16)))))))
9 LEFT(RIGHT(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))),(LEN(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))-FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16)))))),FIND(“.”,RIGHT(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))),(LEN(RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))-FIND(“.”,RIGHT(G16,(LEN(G16)-FIND(“.”,G16))))))))

In the end I just concatenated the first three octets and added the trailing zero. If an easier way comes along I’ll probably just update this page but for now it gets the job done 😛

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s