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 😛
greg's wordpress... 