A Comprehensive Guide on How to Count Characters in Excel – 3 Easy Ways
(Note: This guide on how to count characters in Excel is suitable for all Excel versions including Office 365)
In a Word document, you would be familiar with the word count tool that displays character count, words, character count including spaces, and character count excluding spaces. Similarly, we can find the character counts in Excel using the formulas discussed in this article.
You’ll learn:
- Count Characters in a Single Cell
- Count Characters of Multiple Cells
- Formula to Count Specific Characters in a Cell
Related Reads:
HOW TO COUNT UNIQUE VALUES IN EXCEL? 3 EASY WAYS TO COUNT UNIQUE AND DISTINCT VALUES
HOW TO COUNT CELLS WITH TEXT IN EXCEL? 3 DIFFERENT USE CASES
HOW TO USE EXCEL COUNTIFS: THE BEST GUIDE
How to Count Characters in Excel in a Single Cell
We aim to determine the number of characters in a cell containing any text. The character count formula in Excel uses the LEN function, which refers to the length.
For example, you want to calculate the count of characters of a cell, say A1.
Step 1- Type any word in a cell for our reference. For this example, I have entered the name COMPUTER.
Step 2- Once you enter the formula, press Enter to know the exact number of characters in cell A1.
Step 3- Now you can notice that the character counts for the word COMPUTER in cell A1 is 8 as per the formula entered.
How to Count Characters in Excel for Multiple Cells
In the previous method, we saw how to count characters using the LEN function, which was limited to a single cell. We are modifying the existing formula by adding SUM function to detect the character count of multiple cells.
First, select the cell where you want to display the result. Then, enter the following combined formula:
=SUMPRODUCT(LEN(range))
For a practical example:
Step 1- Consider the scenario where we want to count characters from cell range A1 to A3 with apple, orange, and banana written in them. Enter the formula based on the syntax mentioned above in the adjacent cell, say B1.
Step 2- Once you have checked the values in the arguments, click Enter.
The formula returns the count of the characters as specified in the cell range in the destination cell.
Suggested Reads:
HOW TO APPLY THE ACCOUNTING NUMBER FORMAT IN EXCEL? (3 BEST METHODS)
HOW TO INSERT BULLET POINTS IN EXCEL? 5 EASY METHODS
HOW TO REMOVE SPACES IN EXCEL? 3 EASY METHODS
Formula to Count Specific Characters in a Cell
The word count in a cell, as we know, is easy to measure as most cells contain a value like an integer, noun, or symbol. However, when it is required to determine specific characters, you must use a formula to detect the count. It is possible by combining the LEN and SUBSTITUTE functions in the following way =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1
Note: The above formula is the syntax or general form. The arguments vary from person to person, based on their worksheets.
Step 1- Select a cell and enter a few words in it. For example, I have typed the title of an article in the A1 cell. The word count is easily visible for a title, but when determining specific characters, the formula is needed.
Step 2- Enter the formula corresponding to the syntax mentioned, and change the value of the arguments accordingly. Click Enter to display the character count based on the formula entered.
Also Read:
LOGICAL FUNCTIONS IN EXCEL (IF, IFS, AND, OR, COUNTIF, SUMIF)
HOW TO CALCULATE SQUARE ROOT IN EXCEL? 3 PROVEN WAYS
HOW TO USE THE CEILING FUNCTION IN EXCEL? A STEP-BY-STEP GUIDE
Frequently Asked Questions
How do I count the number of characters in a cell?
You can count the characters of a cell using the LEN function in Excel, which is the abbreviation for length. For instance, to count the characters in a cell, say A1, the formula =LEN(A1) must be entered in another cell. Hit Enter to return the character count of that cell.
How do I count the number of characters in multiple cells at once in Excel?
The only way to do that is to use the SUM function in union with the LEN function by specifying the range of cells that are to be considered. The final formula would be =SUM(LEN(1), LEN(2),… so on, where 1,2 refers to the cell address.
Final Thoughts
To optimize your sheet best, you must remove unnecessary characters and spacing from your worksheet. As you would maintain and track the word count of a Word document, an Excel sheet offers the character count function to identify the number of characters which was briefly discussed in this article.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 150+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
