I had a simple problem to solve, I wanted to view lists of spammers and the IP addresses they came from in a pivot table. The problem is they often use multiple addresses in the same block. My pivot table in Open Office showed only one or two uses of each IP address yet there were thousands of spam form posts in my site.
So – I decided to lump the IP addresses together in blocks by removing the last of the four groups of numbers in the IP address. Doing this in Apache Macro was a bit harder than I thought and the regular expression examples didn’t work well. So here is what I came up with – multiple find statements inside each other. This solution will also work with Excel macro to find parts of an IP address (IP4 only but a simple hack of this example will work with UIP6).
Code:
This assumes the IP address is in cell A1. This formula is placed in the cell you want to display the shortened IP address in.
=LEFT( A1, FIND( ".", A1, FIND( ".", A1, FIND("." , A1)+1)+1)-1)
Breaking it down
Assume the ip address in A1 is 111.93.112.17 You want the the cell showing the block address to only display 111.93.112
=LEFT(
A1, FIND( ".", A1,
FIND( ".", A1,
FIND("." , A1)+1)
+1)
-1)
- FIND (“.” , A1 ) = 4 ( 111.)
- FIND ( “.”, A1, STARTING AT 4 ) = 7 ( 111.93. )
- FIND ( “.”, A1, STARTING AT 7 ) = 11 ( 111.93.112. )
- LEFT ( A1, 11 – 1 ) = 111.93.112
Simpler than regular expressions that change for every program or language you implement them in and transferable with little change to Excel, Visual Basic, Open Office Basic, PHP etc.
Enjoy.
