For counting the number of characters in single or multiple cells, you may use the Excel LEN function. In the LEN function, you may provide a text string, cell or use LEN with SUM function for counting characters in multiple cells.
First example: Counting single cell characters
Let me start with a simple example of counting characters in Excel. For that, a text string is entered in the A2 cell that contains alphabets, numbers, and spaces. See how LEN function returns character count:
The LEN formula:
You can see, LEN returned the characters count of “Hello U123, how is it going?” string as 28.
Get the count of multiple cells independently
If you require the character count of multiple cells, then simply copy paste the above LEN formula in cells where you want to display the count and replace the A2 by corresponding cell (which character count is required).
For example, I used =LEN(A2) formula in B2 cell in above example. Just copy the B2 cell; after selecting B2 cell, press ctrl+C. Now paste the formula in B3, B4 and so on. In the formula, the A2 should be replaced by A3 for B3 formula and so on automatically. See the example below:
Alternatively, after selecting the B2 cell, bring the mouse at the right bottom of B2 till thin solid line + sign appears. Now, drag the mouse down to the desired cell. The formula should be copied and A(cells) should update automatically for the respective B cells as shown below:
The example of getting SUM of multiple cells character count
If you require the sum of all cell’s character count, use the LEN function with the SUM function. To demonstrate that, let me extend the above example and display the sum of character count of each cell in B6 cell.
The formula used in B6 cell:
The sum of A2, A3 and A4 cell is 49 characters.
Another way of getting the SUM by range in LEN
Rather than entering the LEN formula for each cell separately, you may specify a range in the LEN function. For example:
For getting SUM this way, you need to follow these steps:
Select the cell where you want to display the character count. For the example, I selected B6.
Enter the above formula in the formula bar and press Ctrl + Shift + Enter key. This should display the sum of letters count. If you enter the formula manually, it will not work.
A formula to count words in Excel
Well, you may refine the following formula further as per the complexity of cell’s text. However, for the general text you may use this for counting the number of words in Excel.
The formula for counting words in A5 cell:
1 =IF(LEN(TRIM(A5))=0,0,LEN(TRIM(A5))-LEN(SUBSTITUTE(A5," ",""))+1)
- The SUBSTITUTE replaces all occurrences of spaces to “” i.e. returns text with removed spaces.
- The TRIM function removes any unwanted spaces from the specified text/cell.
- The LEN method is used to get the length with spaces and without spaces.
- Whereas, the IF function checks the initial length of text in the cell. If it is empty, the result displayed as 0.
- If the result is non-zero then length before and after SUBSTITUTE function is subtracted and added by 1; that is the count of words.
The result as different texts are entered in A column cells:
You can see, it counted & = signs as words (used in A5). The number in A3 cell is taken as one word. The third column in the sheet displayed the number of characters.
How to get the count of a specific character?
In order to get the count of the specific character, you may use the combination of formulas. One of the ways is using the LEN with SUBSTITUTE function as follows.
In the example, the text where we want t0 count a specific character(s) is given in A2 cell. The character(s) that we want to get the count of is entered in B2 cell.
The following formula applied in C3 cell that displayed the occurrence of the specific character.
The resultant Excel sheet:
The ‘C’ occurred twice in the text string. This is how it worked:
- The LEN function got the total length of A2 cell.
- After that, the character that we want to count is replaced by “” by using SUBSTITUTE function.
- So, it returned the length of A2 after replacement of given term.
- Finally, the length of A2 after and before replacement is calculated that is the count of given character.