Find and count text within text using Excel functions

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.

Contents

1. The need – 00:00
2. The solution – 00:46
3. Cannot use Find or Search – 01:20
4. The solution – Len and Substitute – 02:09
5. Substitute function – 02:45
6. Check length after substitute – 03:15
7. Divide by search text length – 03:28
8. Ignore case setting – 04:00
9. Combined formula – 04:54
10. Use Let function – 05:36
11. Apply to real data – 06:20
12. Apply to each row in a table – 06:43
13. Apply to multiple search texts – 07:08

Related videos

Excel Best Practices – Data Management – Part 1 of 3 – 2023

Excel Best Practices – Part 2 of 3 – Formulas – 2023

Excel Best Practices – Part 3 of 3 – Analytics – 2023