Being a bit of a whizz with spreadsheet packages such as Excel is a very important skill to possess for SEO’s. The industry is becoming more and more data orientated thanks mainly to the great link analysis software that is now available in the marketplace from places such as SEOMoz, MajesticSEO and SEO Spyglass.
This series of blog posts is designed for webmasters to help analyse their backlink profiles and help carry out some basic competitor backlink analysis using Microsoft Excel.
Extracting the Domain Name from a URL
When using a link analysis tool such as Open Site Explorer you will inevitably return lots of line data. This can often be overwhelming especially if you are analysing an expansive list of backlink urls. The quickest way to return the domain name is to use the text to columns tool.
Simply highlight the URL’s column and choose data and then text to columns.
Now using the Convert Text to Columns wizard choose delimited
And insert / into other under delimiters and select treat consecutive delimiters as one.
Click next and then finish, you will now have all your data split across several columns but more importantly you will now have your domains in one column. You can now use the remove duplicates tool to remove any repeated domain names so you now have a list of websites to monitor or target for new links.
Another way to do this is using the Excel text functions LEFT & FIND
The formula I use is =LEFT(A1, FIND(“/”,A1,8)) (assuming Cell A1 is the cell containing the URL)
This formula basically says find the first “/” in the URL, after letter 8, and return everything to the left of it.
I hope this post was useful and I’ll be posting up some more Microsoft Excel SEO tips.