90 Digital CEO Nick Garner gives us an overview on Working with Excel in this edition of CalvinAyre.com’s SEO Tip of the Week.
Put the right SEO information sources together and you get amazing insights, but to even begin doing this you need a very useful excel trick.
This is important because when you want to aggregate information from different sources, you will need a common reference point. There are three main ones:
-
The URL you are analysing
-
subdomain
-
domain
This common reference point is important because it means I can bring together different sources of information into one place and so get a far better understanding of a domain.
For instance I might want to bring together data on a domain or page from Moz, Majestic, AHrefs, SEMRUSH, SearchMetrics, Buzzsumo, URLprofiler…
The more data sources of data I can bring together, the better I will know a domain or page. This is really important if I am doing disavow files or link analysis.
There are two main ways you can do this. One is by adding a function to Excel and the other is by using a formula.
This add-on for excel allows you to use a function: =subdomain(A1). This strips a url like this:
TO: www.calvinayre.com
You can get the excel add-on here: http://connect.icrossing.co.uk/url-tools-addin-excel_5458
The other route is to use a formula. There are a number of them knocking around the internet, but this one is the best I have found so far. It will reliably strip a URL down to the root domain i.e. domain.com
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A1,FIND(“://”,A1)+3,99),A1))&”/”,”/”,REPT(” “,99)),99))),”.”,REPT(” “,99)),99*(2+(LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(SUBSTITUTE(TRIM(IFERROR(MID(A1,FIND(“://”,A1)+3,99),A1))&”/”,”/”,REPT(” “,99)),99)))&”.”,”.”,REPT(” “,99)),198)))=2)))),” “,”.”)
TIP: Because this formula is so long, the best way to set it up on Excel is to use notepad and then use ‘find and replace’ to change the cell references. i.e. change A1 to C3 or whatever and then place the formula into the relevant part of the spreadsheet.
