While Excel was designed for numbers, it also involves text in cells. So, for example, if you want to transfer your data and limit the characters for each cell, you may want to know how to count characters in Microsoft Excel.
Keeping all that in mind, we have designed this post that offers some proven methods to help you count characters in Microsoft Excel, just like you would like to count Words in Excel.
How to Count Characters in Excel
Having said that, while you may need to count characters in a single cell, you may also need to do the same for multiple cells, for a single/multiple cell range, count specific characters in a cell, in a cell range, or without the letter case. Here we offer a detailed post explaining everything to help you count characters in Microsoft Excel.
- Counting characters for single/multiple cell
- Counting characters in single/multiple cell range
- Counting specific characters in Excel
1] Count characters using the LEN formula
For single cell
To count the characters in a single cell, the text function in Excel, LEN comes in handy. While the syntax is LEN(cell), the exact formula to find out the number of characters in a single cell is as follows:
=LEN(cell)
Replace (cell) with the cell reference you want to count the character for. So, for example, if you want to find the number of characters in cell G6 (as shown in the above image), the formula would be:
=LEN(G6)
Please note that all punctuation marks, spaces, even the ones after a string, count as characters in a sentence in Microsoft Excel.
Also, to count the number of bytes in a cell, you can use the LENB function, for example, =LENB(A2).
For multiple cells
But, if you want to count the total number of characters in multiple cells, you can use the below formula to arrive at the results:
=SUM(LEN(cell1),LEN(cell2),LEN(cell3))
So, in this case, you need to use the LEN function for all the cells you want to count the characters for and add the SUM function to it.
For example, if you want to count the total number of characters in cells A4, A6, and A8, here’s what the formula will look like:
=SUM(LEN(A4),LEN(A6),LEN(A8))
Read: How to convert Numbers stored as Text to Numbers in Excel
2] Count Characters in single/multiple cell range
Single-cell range formula
But, if you want to count characters in a cell range next to each other (adjacent), there are two ways. While the above formula can also help you to easily count the number of characters for a range of cells, using the below formula can help you achieve the same pretty quickly too. So, for example, to count the total number of characters in cells A4 through A8, the formula should look like this:
=SUM(LEN(A4:A8))
Alternatively, you can also use the below formula to obtain the same result:
=SUMPRODUCT(LEN(A4:A8))
Multiple cell range formula
But if you want to find out the total number of characters in multiple cell ranges, for example, from A4 to A8, and B4 to B8, here’s the formula you can use:
=SUM(LEN(A4:A8),LEN(B4:B8)) OR =SUMPRODUCT(LEN(A4:A8),LEN(B4:B8))
Read: How to put a Circle around a Number in Excel
3] Counting specific characters in Excel
For specific characters in Excel
Sometimes, after adding text to a cell in Excel, you may want to count the total number of specific characters for that specific cell. In this case, you can use LEN with the SUBSTITUTE function. So, for example, if you want to count the number of E‘s in the cell A4, the formula should look something like this:
=LEN(A4)-LEN(SUBSTITUTE(A4,”e”,””))
Here, the SUBSTITUTE is case-sensitive. So, if you enter the letter in lowercase, it will return the result accordingly, and vice versa.
However, you can also choose to ignore the letter case, you can use the below formula in the said format:
=LEN(A4)-LEN(SUBSTITUTE(UPPER(A4),”E”,””))
For specific characters in a cell range
Now, to find out the total number of characters in a cell range, you need to use all three functions, LEN, SUM, and SUBSTITUTE. So, for example, if you want to count the specific characters like the number of E’s cell range A4 through A8, here’s what the formula looks like:
=SUM(LEN(A4:A8)-LEN(SUBSTITUTE(UPPER(A4:A8),”E”,””)))
Read: How to Spell Check specific regions, parts, cells, or columns in Excel
How do I count the number of text in Excel?
To count the number of cells with text in Excel, you can use the formula =COUNTIF(range, “*”). You can then replace the range with the cell range you want to count. This formula calculates all cells within the specified range that contains text. It ensures accurate data management and analysis.
What is the formula for Len in Excel?
The formula for LEN in Excel is =LEN(text), where text illustrates the cell reference or string you want to measure. This formula generates the total count of characters present, which includes spaces and punctuation marks, in the specified cell’s content. For instance, =LEN(A1) counts the character count in cell A1.
