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.
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," ",""))+1Formula 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.
Related Articles
- Excel VBA: Count Characters in Cell
- How to Count Characters in Cell Including Spaces in Excel
- How to Count Occurrences of Character in String in Excel
<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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 ENTER and get the following output.
It counts the total characters without commas.