How to find location based on IP address in Excel

Question

I've got a spreadsheet of about 5000 user events associated with IPs, and I'm trying to use IP to determine location using just an Excel formula. The IPs in my log are structured as "dotted quads" (decimal notation).

I figured VLOOKUP is the way to go here, so I downloaded WEBNet77's IPV4 IpToCountry database, which seems like a comprehensive and up-to-date resource (is there a better resource out there for this purpose?). The database includes about 140K rows of IP ranges, with the sheet structured like so:

Colum labels A - G are, respectively: IP FROM, IP TO, REGISTRY, ASSIGNED, CTRY, CNTRY, COUNTRY.

Notice, a problem: columns A to B represent a range of IPs, but they are in the "long format" (I didn't realize this at first). So, before using these values as a reference, I'll have to convert my dotted quads. Then, I'd like to return Country (column G) for each of the IPs in my log.

Any help here?

And if you think there's a better way to lookup IP > Country in Excel (maybe using the web ie. http://api.hostip.info/flag.php?ip=xxx.xxx.xxx.xxx), please do let me know.

Lyre

FYI: classful routing (class A, B and C networks) has been replaced in 1993. IP address blocks are not class-based anymore. – Shulman

@SanderSteffann Thanks for the note. I've updated the question accordingly. – Lyre

Answer 1

You can convert your quad IP to a numeric using this function

Function GetNumericIP(quadIP As String) As Long

    Dim sections
    On Error Resume Next
    sections = Split(quadIP, ".")

    GetNumericIP = sections(3) * 256 ^ 0 + sections(2) * 256 ^ 1 + sections(1) * 256 ^ 2 + sections(0) * 256 ^ 3
End Function

And you can use MATCH/INDEX to get your location. The trick is to have your IP TO column sorted ascending. The Match function will return index of the row for the last value in the range which is less the given value.

57.182.90.111  your input
968252015      =GetNumericIP(A1)
France         =INDEX(Country,MATCH(J6,IPTO,1)+1)
  (Or alternatively) =INDEX(Country,MATCH(J6,IPFROM,1))

Notice I had to import the CSV (not just reference it externally) and I turned the columns into named ranges (Country, IPTO). For other's information, the CSV linked in the question has column names of:

• IP FROM
• IP TO
• REGISTRY
• ASSIGNED
• CTRY
• CNTRY
• COUNTRY

Alternately, you can convert your dotted quad IP into its constituent parts using only formulas, though it's a bit of a hassle. Here are the first two sections (my IP was in I5)

=LEFT(I5,FIND(".",I5)-1)
=LEFT(RIGHT(I5,LEN(I5)-I10),SEARCH(".",RIGHT(I5,LEN(I5)-I10))-1)

You can see...kind of a pain. Easier: use the VBA method explained above, OR use Text to Columns (on the ribbon: Data > Data Tools > Text To Columns > Delimited > Next > Other: "." > Next > Finish). Then write a formula to convert your split dotted-quad IP to the IP format in your reference database. This formula works:

=sumproduct(J5:M5, 256^{3,2,1,0})

Remember to sub in your column range for J5:M5 above. Then run MATCH/INDEX against the new value.

Aeromarine

Wow, I didn't realize those ranges in the database aren't de-dotted IPs; they're IPs converted to decimal. – Lyre

But I do wonder if converting dotted IP to decimal can be done without VBA. Any idea? – Lyre

@SamtheBrand See my edit. Yes it can be done w/o VBA – Aeromarine

Thanks @brad, I made that work. And now getting hung up on converting to decimal. For some reason my formula =sumproduct(J5:M5, 256^{3,2,,0})` is working in some cells, but not others. Hmmm. – Lyre

Are you missing a 1? =sumproduct(J5:M5, 256^{3,2,1,0}) – Aeromarine

my comment was a typo (there was in fact a 1 in my formula). But something buggy was happening with my comp (couldn't paste into a browser). I think it had something to do with all the cpu usage from the 140K row csv. This was extraordinarily helpful. Thanks so much! – Lyre

Just to add, the declaration of the function should really be either ULong or double (depending on the version of VBA you are using) so that IP addresses such as 196.249.118.251 don't overflow the range of a signed Int64. – Benzoyl

The question's CSV link no longer works; but I believe this is the same: github.com/kaseya/IpToCountry – Tymon

Answer 2

I was also in need of finding country names from IPs of our website visitors in order to make some data analysis. For this purpose, I was using bulk converters on the web, such as:

• http://software77.net/geo-ip/multi-lookup/
• https://www.infobyip.com/ipbulklookup.php

These tools were nice but they allow you to lookup max. number of 500 items at once. So, what you can do is to run your data in batches. (I guess they have paid services which allows higher number of items to be looked up at once but did not try them)

I am an excel expert. So I thought there should be an easy way (some kind of a pattern or formula) to convert these ranges into countries. And built a tool for our own internal use. Recently decided to publish it, you can check here: IP to Country Converter Excel Template

It is a paid tool, so I don't want to pitch it here. But you can build one for yourself:

Basically, it runs a VBA macro to convert the ip address into numerical values and find the suitable match according to the country ranges. If you are familiar with VBA, the loop is something like this:

Dim dta() As Variant
dta() = Range("IP_List").Value

Dim arr() As Variant
Dim arr_v6() As Variant

arr() = Range("IP_v4").Value
arr_v6() = Range("IP_v6").Value

'generate IPV4 according to pattern 1.2.3.4 = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256)

For i = 1 To UBound(dta)

If InStr(1, dta(i, 1), ".") > 0 Then
spl = Split(dta(i, 1), ".") 'split IP in 4 integers

ip_converted = spl(3) + (spl(2) * 256) + (spl(1) * 256 * 256) + (spl(0) * 256 * 256 * 256)

'loop through array and find the country

rw = 0
rw = Application.WorksheetFunction.Match(ip_converted, Range("IP_v4"), 1)
If rw > 0 Then
dta(i, 2) = Range("V4_Country").Cells(rw, 1)
GoTo skip:
End If

This really eased my daily life. So I recommend either using a service or a template like that.

Strickler