Method 1 – Using the LEN Function
Steps:
- Go to cell C5 and insert the following formula.
=LEN(B5)The syntax counts the letters of the alphabet in the text existing in cell B5.
- Press ENTER and drag it down for other cells.
Method 2 – Count Characters in a Range
Steps:
- Move to cell D5 and input the following formula.
=SUMPRODUCT(LEN(B5:B10))The LEN function returns the length of the string of each cell and returns an array of numbers of the alphabet from B5:B10. The SUMPRODUCT function adds those numbers and returns the total count of the alphabet.
Method 3 – Count Characters in a Cell Without Space
Steps:
- Use this formula in C5, then AutoFill through the column.
=LEN(SUBSTITUTE(B5," ",""))The SUBSTITUTE function replaces all the spaces in the text of cell B5 and returns an empty string (“”), then the LEN function evaluates the number of characters returned by the SUBSTITUTE function.
Method 4 – Count All Alphabets in a String
We’ll use the same formula as in Method 3.
=LEN(SUBSTITUTE(B5," ",""))After initializing the above formula, press ENTER, and get the below output.
Method 5 – Count Specific Letters in a Cell
Let’s count the number of times the letter s appears in cells.
Steps:
- In cell C5, enter the following 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.
- Press ENTER and you’ll see the result 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.
How to Count Words in Excel
- 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 spaces.
- 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. 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.
Get the output after pressing ENTER. Use AutoFill if needed.
Download the Practice Workbook
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.