Formula to Convert IP Address to Location (City,State)?

[tpir72]

Hi,
I need your help please. I have a spreadsheet that will be generated in Excel 2010. One column will have hundreds of IP Addresses. Is there a way (formula) to also display/convert that IP to location city & state or specific location in the world in the next column to the right?
If that is possible then is it also possible to have a list of several hundred user names associated with known login IP's? This would be a check list of known IPs that user used to login with. Then that list would point to the first sheet (above) and turn the IP/Location green if valid.
This could be used to quickly check if registered members have given out their user name / password combo.

I'm a novice when it comes to setting up Excel formulas. I don't have a clue where to start to accomplish this. I know I can export Google Analytics data in a csv format to import into Excel and I have found an obscure way of tracking IP addresses in Google Analytics.

Thank you very much gang for the help.

I sincerely appreciate it.

Regards,

Terry

 

[tpir72]

Bump...

Can anyone please help or point me in the right direction to solve this?

Regards,

Terry

 

[Biz]

Hi tpir72

Website below gives location stuff
http://www.formyip.com/ipcountry.php

I believe UDF or Custom function is required. Sorry I don't have knowledge to do this but would interesting.

Biz

 

[tpir72]

Thanks Biz,
There is another company that sells the complete IP address list with City and State as separate columns in Excel but I don't have a clue as to how to implement it even as a look-up.

Regards,

Terry

 

[lutils]

this link can help u ?

IP / Country Checker

convert up to 500 ip to country

 

[tpir72]

Thank you very much.