Select Page

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.

The following two tabs change content below.

#### Michael Cropper

Founder & Managing Director at Contrado Digital Ltd
Michael has been running Contrado Digital for over 10 years and has over 15 years experience working across the full range of disciplines including IT, Tech, Software Development, Digital Marketing, Analytics, SaaS, Startups, Organisational and Systems Thinking, DevOps, Project Management, Multi-Cloud, Digital and Technology Innovation and always with a business and commercial focus. He has a wealth of experience working with national and multi-national brands in a wide range of industries, across a wide range of specialisms, helping them achieve awesome results. Digital transformation, performance and collaboration are at the heart of everything Michael does.