Whenever you work with a dataset that contains some sentences or words, you may need to count the alphabet in the Excel sheet. Excel shines here, providing features regarding this topic. One of the most common functions to count the alphabet in an Excel sheet is the** LEN** function. This function helps you estimate the characters, alphabets, etc. It also counts the spaces. In this article, we’re going to show you the methods to count the alphabet in an Excel sheet. So, let’s get started.

**Table of Contents**hide

## How to Count Alphabet in Excel Sheet: 5 Methods

For counting the alphabet, you need to put a dataset first. We have taken a dataset of some random **Names**. Now, we count the number of the alphabet in the cell.

Not to mention, we have used the ** Microsoft 365** version. You may use any other version at your convenience.

### 1. Using LEN Function

The **LEN** function counts the letters, numbers, characters, and spaces. Basically, it is the short form of length. So it shows the length of a word or string. We have demonstrated the simple steps to use the function.

**📌**** Steps:**

- Firstly, go to cell
**C5**and insert the below formula.

`=LEN(B5)`

The syntax counts the letters of the alphabet in the text existing in cell **B5**.

- Eventually, press
**ENTER**and drag it down for other cells.

You will get the below output then.

**Read More: **How to Count Specific Characters in a Cell in Excel

### 2. Count Alphabet in a Range

You may need to count the range of sentences to evaluate the total alphabet. To do this, you need to insert the **LEN **and the **SUMPRODUCT** functions together. Follow the steps below.

**📌**** Steps:**

- Initially, move to cell
**D5**, and input the following formula.

`=SUMPRODUCT(LEN(B5:B10))`

The above logic is quite simple. The **LEN** function estimates the length of the string of each cell and returns an array of numbers of the alphabet from **B5:B10**, and then the** SUMPRODUCT** function adds those numbers and returns the total count of the alphabet.

**Read More: **How to Count Specific Characters in a Column in Excel

### 3. Count Alphabet in a Cell Without Space

The **LEN **function counts all the characters such as commas, hyphens, and even spaces between two words. So, if you need to remove the spaces from your counting, you need to use the **SUBSTITUTE function** to eliminate all the spaces from your counting. Follow the simple steps to use this.

**📌**** Steps:**

- Firstly, move to cell
**C5**and input the formula.

`=LEN(SUBSTITUTE(B5," ",""))`

Here,

The **SUBSTITUTE** function replaces all the spaces in the text of cell **B5 **and returns an empty string **(“”) **and then the **LEN** function evaluates the number of alphabets returned by the **SUBSTITUTE** function.

Finally, you get the output as shown in the image below.

**Read More: **How to Count Characters in Cell without Spaces in Excel

### 4. Count All Alphabets in a String

You can apply the above formula to a sentence as well. All the procedures will be the same as we mentioned in **method 3**. You need to enter the below formula.

`=LEN(SUBSTITUTE(B5," ",""))`

After initializing the above formula, press **ENTER**, and get the below output, as shown in the image.

### 5. Count Specific Alphabet in a Cell

If you want to calculate the number of a specific alphabet in a particular cell, then you need to substitute the alphabet from the total length of the string. Follow the below steps.

**📌**** Steps:**

- Primarily, In cell
**C5**enter the below formula.

`=LEN(B5)-LEN(SUBSTITUTE(B5,"s",""))`

**Formula Breakdown:**

**SUBSTITUTE(B5,”s”,””)**takes the**text**of**B5**and removes “**s**”. Returns the text without “s”.

**LEN(SUBSTITUTE(B5,”s”,””))**counts the length of the string returned by the**SUBSTITUTE**function.

**LEN(B5)**calculates the total length of the string of cell**B5**.

**LEN(B5)-LEN(SUBSTITUTE(B5,”s”,””))**subtracts the string without “**s**” from the total string of cell**B5**.

Eventually, press **ENTER**, and you’ll get the result shown in the image below.

** Note:** The

**SUBSTITUTE**function is a case-sensitive function. So it takes the

**old_text**argument “

**s**” and replaces the small letter “

**s**” from the string. The capital “

**S**” is not applicable here.

**Read More: **How to Count Space Before Text in Excel

## How to Count Words in Excel

You can count the total number of words in your sentences with the help of the **LEN**, **SUBSTITUTE**, and **TRIM** functions. The **SUBSTITUTE** function will remove the spaces, and the **LEN **function counts the length of the string.

- Firstly, you have to go to cell
**C5**to insert the following formula.

`=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1`

**Formula Breakdown:**

**SUBSTITUTE(B5,” “,””)**substitutes the spaces of cell**B5**with no spaces. In other words, it removes all the spaces from the words in cell**B5**.**LEN(SUBSTITUTE(B5,” “,””))**calculates the total number of characters in cell**B5**without space.**TRIM(B5)**removes all spaces from text except for single spaces between words in cell**B5**.**LEN(TRIM(B5))**calculates the total characters of the trimmed text in cell**B5**.**LEN(TRIM(B5))-LEN(SUBSTITUTE(B5,” “,””))+1**here, the total number of characters in cell**B5**without spaces is subtracted from the total number of characters with spaces. Hence, essentially, we get the total number of spaces in cell**B5**. As we know, for one space, we get two words in a sentence. Therefore, we add an extra**1**to the formula to get the final word count.

Finally, you get the output after pressing **ENTER**.

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it yourself.

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

Hopefully, you have grasped the methods of how to count the alphabet in an Excel sheet. Leave comments if you have any queries, we will get back to you asap. Thanks for your patience in reading this article.

Quite clean elaboration. I have a dataset where i have different types of sign and symbols in a sentence. But i want to count only the alphabets there. What modification should be done in this particular case? Can you show me a way Fahim?

Hi Nathan,

Thanks for your valuable feedback. If I am not wrong, you want to count the characters without the symbols. For this factor, we have taken a sentence that contains commas. To remove the commas and count the characters insert the following formula in cell

C5.`=LEN(SUBSTITUTE(B5," ",""))-LEN(B5)+LEN(SUBSTITUTE(B5,",",""))`

Press

ENTERand get the following output.It counts the total characters without commas.