Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Count the Number of Times a Character / Word Appears in a Cell?
Excel is an effective spreadsheet programme that provides a variety of tools and capabilities to modify and analyse data. When working with sizable datasets or performing data analysis, knowing how to count the instances of a particular character or word within a cell can be helpful. Regardless of your level of Excel proficiency, this lesson will walk you through each step of the process so that you can do this assignment quickly. So let's get started and discover how to determine how many times a character or word appears in an Excel cell!
Count the Number of Times a Character / Word Appears
Here, we can complete the task directly using a single formula. So let us see a simple process to know how you can count the number of times a character or word appears in a cell in Excel.
Step 1
Consider an Excel sheet where the data in the sheet is similar to the below image.
First, to count the number of times a word appeared, click on an empty cell, in this case cell B2, and enter the formula as
=(LEN(A2)-LEN(SUBSTITUTE(A2, "thoughts","")))/LEN("thoughts") and click enter. In the formula, A2 is the cell and thoughts is the word we are counting.
Empty cell > Formula > Enter.
Step 2
Now, if you want to count the occurrence of a character, replace the word in the formula with a character. That is, use the formula =(LEN(A2)-LEN(SUBSTITUTE(A2, "r","")))/LEN("r") and click enter.
Empty cell > Formula > Enter.
Note
The formulas are case-sensitive. If you want to count the characters or words without case-sensitivity, use the formula as
=(LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),UPPER("remove"),"")))/LEN("remove"). This is how you can count the number of times a character or word appears in a cell in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can count the number of times a character or word appears in a cell in Excel.
2K+ Views
