Check IP Address Range
As a Fraud Analyst, I need to quickly identify the country block an IP address belongs to. This involves extracting the first octet (the digits before the first dot) from a list of IP addresses so I can then filter or categorize them, often to detect suspicious activity originating from specific regions. While the first octet is often three digits, it can be shorter.
=LEFT(A2, FIND(".", A2)-1) How it works: The `FIND(".", A2)` part of the formula locates the position of the first dot in the IP address string in cell A2. Subtracting 1 from this position gives us the exact number of characters that constitute the first octet. The `LEFT(A2, ...)` function then extracts precisely that many characters from the beginning of the IP address. This method ensures that whether the first octet is one, two, or three digits long (e.g., '10', '172', '192'), it is correctly extracted, providing a consistent basis for country block analysis.
Data Setup
| IP Address | Description |
|---|---|
| 192.168.1.1 | Internal Network |
| 10.0.0.1 | Local Host |
| 172.16.0.1 | Private Network |
| 203.0.113.45 | Public Server |
Step-by-Step Guide
Assume your IP addresses are in column A, starting from cell A2.
In cell B2, enter the formula: `=LEFT(A2, FIND(".", A2)-1)`.
Press Enter. This will extract the first octet (e.g., '192' from '192.168.1.1').
Drag the fill handle (the small square at the bottom-right of cell B2) down to apply the formula to the rest of your IP addresses.