This is one of those handy hacks used by the best SEO professionals out there to quickly find the IP address of hundreds of websites at the same time.
If you are just looking for for a quick copy and paste solution, then I suggest skipping all the way to the fourth step in the tutorial below.
Otherwise, follow the step-by-step below as it will give you a much better result at the end. I've also taken the time to include additional information along with the tutorial on why SEO professionals need to know the IP address of websites.
In short, sites need backlinks to rank well on the SERPS, and a widely-used tactic to bring those links is by acquiring them from other websites. This can be done in a variety of ways, such as guest blogging, asking a favor to a website owner you know, or even buying links, which I strongly advise against it.
Buying links could lead to devastating consequences. I'll come back to that in a future post, so in the meantime, consider yourself warned.
I'm planning to write more about how to build genuine backlinks soon, so be sure to subscribe to my blog using the form below to not miss these updates.
With that out of the way, how do you go on to find the IP of a website using Google spreadsheets? Here's a step-by-step just for that:
Step 1. Create a Google Spreadsheet
You will need to use a Google account for that. Create one using this link.
Once you have your account ready, create a new spreadsheet by clicking on New and then on Google Sheets, just as in the screenshot below.
Your spreadsheet should look like this:
In case you are trying to do this with Excel, I'm afraid I'm not sure if it will work. I will sure check that later on, and update this post if I find a way to do that.
Step 2. Add a list of URLs to your Spreadsheet
Now, quite obviously, you will need a list of URLs. These should be the URLs of websites you would like to place links back to your site.
If you already have a list of websites, then place them in the first column of your spreadsheet. If you don't have a list, then just populate it with a few popular websites such as Facebook, Twitter, and LinkedIn.
Your URL list might look a bit like this:
Step 3. Normalize all URLs
If you try to run the function in the next step without normalizing your URLs, you might get a few errors, so this is just to smooth things out a bit.
Remove all entries of “http://” and “www.” as well as any “/” at the end of URLs. You can use the Find & Replace menu item on Google Spreadsheets to locate and remove any mention of these entries.
All your URLs should be in their root format as “domainname.tld” for this to work, such as in the example below.
With that done, all you have to do is to use the IMPORTXML function.
Step 4. Find the IP with the IMPORTXML Function
This is it. All you have to to do now is to add the IMPORTXML function to an additional column. This function will use a third-party website to return the IP address for you.
I welcome you to test the function above by retrieving the IP of my website. You can copy and paste the function above without any change, and if it returns an IP address, it means the function is working as expected in your spreadsheet.
To make this function work with all the URLs in your spreadsheet, you need to use a dynamic version of it, such as this one:
=importxml("http://freegeoip.net/xml/" & A1,"//IP")
In the version above, the function will pull the URL saved in the cell A1 to look for an IP address. You must be sure that there is an URL in that cell, otherwise, it will not work as expected.
For the nerds out there, like myself, this is what is happening in the background, the IMPORTXML function looks at the XML structure of a document and returns a specified element, as long as it is present in that XML structure.
The function works with parts, where the first part uses this site, which dynamically retrieves an XML document with the data of any URL that you append in from of it, and the second element of the function select which piece of data should return into your spreadsheet.
If you find that FreeGEOIP is not working, you can also try this site.
You just need to keep in mind that the formula structure will change to this:
=importxml("http://xml.utrace.de/?query=" & A1,"//IP")
What to do next?
Now that you know the exact IPs for all the websites you are looking to acquire links from, you should focus on acquiring links from unique IPs. The more IPs linking back to your site, the better positions you reach for your pages in the SERPS and bring more traffic to your website.
You might also want to know that there is a very handy SEO tool named ScrapeBox which allows you to do the same but in a much more easy way. I'm not going to go into more details about this tool, or how to find bulk IP addresses with it at the moment, but will sure get back to this in a future post.
If there is anything else you would like to ask about this function or acquiring links to boost your rankings, please leave a comment below. Thanks!