Excel Tips & Tricks for SEO

Quick reference manual for myself as to regular Excel things I use and useful formulas for various tasks. If you find it useful too, then please share around :-) It will be a growing list as/when I find something useful that I use a lot but can never remember the exact way of doing it!

 

How to Count the Number of Occurrences of Text in a Cell

Quick reference tip; 

 

=SUM(LEN(<range>)-LEN(SUBSTITUTE(<range>,”text”,””)))/LEN(“text”)+1

=SUM(LEN(B2)-LEN(SUBSTITUTE(B2,”|”,”")))/LEN(“|”)+1 (example)

=SUM(LEN(B2)-LEN(SUBSTITUTE(B2,”separator”,”")))/LEN(“separator”)+1 (example)

 

Read the full blog post about how to count the number of occurrences of text in a cell here about how to use this. 

 

How to Count the Number of Occurrences of Text in a Column in Excel

Quick reference tip;

 

=COUNTIF(<range>, “text”)

=COUNTIF($A$2:$A$8, A2) (example)

=COUNTIF($A$2:$A$8, “Apples”) (example)

 

Read the full blog post about How to Count the Number of Occurrences of Text in a Column in Excel here about how to use this. 

 

How to VLOOKUP Using Partial Matches in Excel 

Quick reference tip;

 

=VLOOKUP(CONCATENATE(“*”, <lookup value>, “*”), <range>, 1, FALSE)

=VLOOKUP(CONCATENATE(“*”, A2, “*”), B2:B10, 1, FALSE) (example)

=VLOOKUP(CONCATENATE(“*”, “Jim”, “*”), B2:B10, 1, FALSE) (example)

 

Read the full blog post about How to VLOOKUP Using Partial Match for how to use this. 

 

How To Get The Domain Name From a URL in Excel

Quick reference tip;

 

=MID({CELL OF FULL URL}, FIND(“//”, {CELL OF FULL URL})+2, FIND(“/”, {CELL OF FULL URL}, 10)-8)

=MID(A1, FIND(“//”, A1)+2, FIND(“/”, A1, 10)-8)

=MID(“http://www.michaelcropper.co.uk/2012/10/how-to-scrape-the-href-attribute-using-xpathonurl-seo-tools-1252.html“, FIND(“//”, “http://www.michaelcropper.co.uk/2012/10/how-to-scrape-the-href-attribute-using-xpathonurl-seo-tools-1252.html“)+2, FIND(“/”, “http://www.michaelcropper.co.uk/2012/10/how-to-scrape-the-href-attribute-using-xpathonurl-seo-tools-1252.html“, 10)-8)

 

Read the full blog post on How To Get The Domain Name From a URL in Excel for detailed information about how to use this formula. 

 

Leave a Comment

Switch to our mobile site