Formulas for Counting the Times a Word Appears in a Cell in Excel!!
Tags: excelformula, excelformulas
Published: 2020-10-23 08:11:35

---
title: Formulas for Counting the Times a Word Appears in a Cell in Excel!!
published: true
date: 2020-10-23 05:42:53 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/formulas-for-counting-the-times-a-word-appears-in-a-cell-in-excel/
---

In this short tutorial, we will discuss the **Excel Formulas for Counting the Times a Word Appears in a Cell or Range**. Let’s get into this article!! Get an official version of **MS Excel** from the following link: **[https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)**
## Formula Syntax:

The below formula is used to [**count the times of a word that appears in a cell or range in Excel**](https://geekexcel.com/count-the-times-a-word-appears-in-a-cell-or-a-range-in-excel-office-365/).

**{=[SUM](https://geekexcel.com/sum-function/)([LEN](https://geekexcel.com/how-to-use-len-function-in-microsoft-excel-365/)([$B$2](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/))-LEN([SUBSTITUTE](https://geekexcel.com/how-to-use-substitute-function-in-microsoft-excel-2013/)($B$2,”Excel”,””)))/LEN(“Excel”))}**

### Example:

- Let’s consider the below table, to count the words appear in a cell.
- First, you need to enter the text.
- Then, enter the given formula in the selected area.
- After applying the formula, you can get the results as shown below.

![Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/10/Output-3.png)

## Explanations for Syntax:

- **SUM** – The Excel [**SUM Function**](https://geekexcel.com/sum-function/) will add the values in the cell.
- **LEN** – The main purpose of this function is to find the length of the given text string and returns into the number of character. Read more on **[LEN Function](https://geekexcel.com/how-to-use-len-function-in-microsoft-excel-365/)**.
- **SUBSTITUTE** – It will replace the existing text with a new text in a text string. Read more on [**SUBSTITUTE Function**](https://geekexcel.com/how-to-use-substitute-function-in-microsoft-excel-2013/).
- **Absolute Reference ($)** – The [**Absolute Reference ($)**](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/)is an actual fixed location in a worksheet. It won’t be changed any values from a row or column when it is copied.
- **Minus operator (-)** – This symbol will subtract two values.
- **Division (/)** – It is used for dividing values or numbers.
- **Comma** **(,)** – It is a separator which helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.

## Conclusion:

From this article, you can get clarification about the **formulas for counting the times of a word that appears in a cell** or range in Excel Office 365. If you have any queries, feel free to share it with us. Thank you so much for Visiting Our Site!! Continue learning on **[Geek Excel](https://geekexcel.com/)!! **

### Read Also:

- **[Excel Formulas for Calculating Age by Date of Birth ~ Easy Guide!!](https://geekexcel.com/excel-formulas-for-calculating-age-by-date-of-birth-easy-guide/)**
- **[Excel Formula to Count the Occurrences of a Character in a String!!](https://geekexcel.com/excel-formula-to-count-the-occurrences-of-a-character-in-a-string/)**