Excel SEO Formulas you should know

Excel is a wonderful tool to manage large rows of data. It is particularly beneficial for SEO as there are many data-intensive tasks such as keyword research, backlink audits, SEO content quality assurance (checking for 404’s, <titles> etc…). Below is just some formulas I use day to day, this is mostly for my own use but I’ve put it here for the sake of house-keeping (For the below formulas to work the value’s to be changed should be in cell A2).

Excel formulas for URLS

Remove https and / from the end of an URL

=IF(RIGHT(SUBSTITUTE(A2,"https","http"),1)="/",LEFT(SUBSTITUTE(A2,"https","http"),LEN(SUBSTITUTE(A2,"https","http"))-1),A2)

Count number of levels i.e. “/” in domain after TLD

=LEN(IF(LEN(A2)<24,"",IF(LEN(MID(A2,19,6))-LEN(SUBSTITUTE(MID(A2,19,6),"/",""))=2,MID(A2,24,999),MID(A2,21,9999))))-LEN(SUBSTITUTE(IF(LEN(A2)<24,"/",IF(LEN(MID(A2,19,6))-LEN(SUBSTITUTE(MID(A2,19,6),"/",""))=2,MID(A2,24,999),MID(A2,21,9999))),"/",""))

Remove .ifolor from the end of an URL ‘ * issue I currently have at the

=IF(IF(RIGHT(A2,7)=".ifolor",TRUE,FALSE),LEFT(A2,LEN(A2)-13),A2)

Remove domain and language from URL

=IF(LEN(A2)<24,"/",IF(LEN(MID(A2,19,6))-LEN(SUBSTITUTE(MID(A2,19,6),"/",""))=2,MID(A2,24,999),MID(A2,21,9999)))

Keep only domain

=MID(A2,SEARCH("//",A2,1)+2,SEARCH("/",A2,13)-SEARCH("//",A2,1)-2)

Excel formulas for Date and time formatting to value

Convert hh:mm:ss in Toggl to minutes

=HOUR(C2)*60+MINUTE(C2)

How to convert Webtrends date format to value in excel (EU)

=IF(A2<>"Time Period",CONCATENATE(RIGHT(A2,4),".",IF(LEN(LEFT(A2,FIND("/",A2,1)-1))<2,CONCATENATE(0,LEFT(A2,FIND("/",A2,1)-1)),LEFT(A2,FIND("/",A2,1)-1)),".",IF(LEN(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1))),"/2",""),"/",""))<2,CONCATENATE(0,SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1))),"/2",""),"/","")),SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("/",A2,1)+1,FIND("/",A2,FIND("/",A2,1))),"/2",""),"/",""))),A2)

Regex formulas to find and replace text

I would highly recommend using notepad++ to do any mass HTML editing/cleaning.

RegEX for finding text before and after specific texts

BEGINING(.*?)END
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.