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.