Need help with a forumla for mapping IP addresses to Private or Public defined spaces

**robiton** · 12-31-2013, 02:25 PM

Hello,

I need help with a formula. First to explain what I am doing. I have a macro that pulls specific data from a CSV, then I copy that extract to my master excel doc (setup by year), so I can put the data into pivot tables and charts. This is used to create "management" style reports.

This data has IP addresses in it. Both internal and external. Trying to find a formula that will look at Cell A, where the IP address is and input either External or Internal into column AA, based on the number.

Internal would be numbers between 10.0.0.0 and 10.255.255.255, 172.16.0.0 and 172.31.255.255, and 192.168.0.0 and 192.168.255.255. Anything not in those three ranges would show as External.

Thanks in advance.

**kspeese** · 12-31-2013, 02:44 PM

Here's a start:

For example, list your start IP's range (Your internal network IPs) in Cells D1, D2, D3
Highlight those cells and name them something like IPSTART

In Cells E1, E2, E3 list your IP end range.
Highlight those cells and name them something like IPEND

IN Cell A1, list the IP to be checked. In B1, do this =IF(AND(A1>=INTSTART,A1<=INTEND),"Internal","External")

Now, the issue you have is you will probably need to convert those IP's to something that can be recognizebale / sortable.

I googled and came up with this that may be a help: http://dmcritchie.mvps.org/excel/sorttcp.htm
Looking at that, I'd probably convert to the regular number range (i.e., 122.123.124.125 = 2054913149)

**robiton** · 12-31-2013, 02:49 PM

Thank you. This helps some, though I was trying not to have to break the source data into separate cells. Some times the extract is 5000+ lines in size.

Was hoping there would be a formula that could do a range match. ie something like IF A1 <192.168.0.0 but less than 192.168.255.255 then B1 = Internal.

**FDibbins** · 12-31-2013, 04:09 PM

Thanks for the title change

**martindwilson** · 12-31-2013, 10:36 PM

you can convert ip to its equivalent decimal then look up the range, i have a nice formula ready for you but you need to change your thread title first or else i'll get banned!