Find and count text within text using Excel functions - LEN and SUBSTITUTE

Learn how to find text inside another text or data using Excel. I want to count the number of times the text appeared. I also want to consider or ignore upper / lower case.
Find or Search functions are not useful here. We have to use a combination of #substitute and #len #excel functions here.

Download sample file and follow along

Contents 📚
00:00 - The need
00:46 - The solution
01:20 - Cannot use Find or Search
02:09 - The solution - Len and Substitute
02:45 - Substitute function
03:15 - Check length after substitute
03:28 - Divide by search text length
04:00 - Ignore case setting
04:54 - Combined formula
05:36 - Use Let function
06:20 - Apply to real data
06:43 - Apply to each row in a table
07:08 - Apply to multiple search texts

Related videos

Buy Dr Nitin's latest book
-----------------------------------------
Efficiency Best Practices for Microsoft 365
130 best practices everyone must know
Work Smarter, Grow Faster

Dr Nitin Paranjape is the pioneer in maximizing work efficiency in a modern work scenario.
Dr Nitin's Office Masterclass
----------------------------------------------

Online Courses by Dr Nitin Paranjape
--------------------------------------------------------------

Learn more from 1000+ blog articles by Dr Nitin Paranjape

Production
--------------------

[subhaniqbal]

good job sir few minutes ago i was finding excel tricks for myself then suddenly you appeared and amazed me

[canirmalchoudhary]

Today I got to know precisely that substitute is case sensitive.