LEN + SUBSTITUTE (count occurrences) Function in Excel: Syntax, Examples and Free Practice
LEN + SUBSTITUTE (count occurrences) is an Excel Text function. A classic trick: removes every occurrence of a substring, compares the length difference, to count how many times it appeared.
LEN + SUBSTITUTE (count occurrences) syntax
Arguments
text: Text to search withinfind_text: The substring to count
When to use LEN + SUBSTITUTE (count occurrences)
Counting how many times the word 'urgent' appears across a block of notes.
Practice LEN + SUBSTITUTE (count occurrences) online, free
Open the live Excel-style spreadsheet and try LEN + SUBSTITUTE (count occurrences) yourself. No Excel install or sign-up needed to start.
Practice LEN + SUBSTITUTE (count occurrences) now →More Text functions
Frequently asked questions
What does the LEN + SUBSTITUTE (count occurrences) function do in Excel?
A classic trick: removes every occurrence of a substring, compares the length difference, to count how many times it appeared.
What is the syntax of LEN + SUBSTITUTE (count occurrences) in Excel?
The syntax is =(LEN(text) - LEN(SUBSTITUTE(text, "find_text", ""))) / LEN("find_text"). text: Text to search within; find_text: The substring to count.
Where can I practice LEN + SUBSTITUTE (count occurrences) online for free?
Open the free live spreadsheet on Cellgrids and try LEN + SUBSTITUTE (count occurrences) yourself. No Excel install is needed and you can start without signing up.
All Excel formulas · Cheat sheet · Excel exercises · Text functions · Excel case studies · Excel tutorials · Excel shortcuts