How to Count Alphabet in Excel Sheet (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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.

Using LEN Function to count alphabet in excel Sheet

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.

Using SUMPRODUCT and LEN Functions to count alphabet in excel sheet

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.

Count Alphabet in a Cell Without Space in Excel Sheet

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.

Count All Alphabets in a String


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.

Practice Section


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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

2 Comments
  1. 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?

    • Reply Avatar photo
      Fahim Shahriyar Dipto Dec 28, 2022 at 2:16 PM

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo