How to Count Characters in Microsoft Excel Cells

Much better than doing it manually

Counting characters in an Excel cell is a lot easier than you might think. With a few simple formulas, you can count the number of characters in a single cell or multiple cells as well as get a total of specific characters.

If you’re preparing to export your data and must limit the characters per cell or want to find the number of occurrences of a particular character, here’s how in Microsoft Excel.

Count Characters in a Single Cell in Excel

To get the total number of characters in a cell, you’ll use the Excel LEN function. The syntax for the formula is simplyLEN(cell),where you enter a cell reference for the argument.

As an example, you can count the characters in cell A2 using this formula:

=LEN(A2)

Remember that both punctuation marks and spaces count as characters, as do any spaces after the last character in the string.

Tip: You can also count the number of bytes in a cell using the LENB function. For instance, use=LENB(A2)for the above example.

Count Characters in Multiple Cells

Maybe you want the number of characters in multiple cells. You can use the LEN function and add the SUM function to the formula.

For example, with the following formula you can count the number of characters in cells A2, A4, and A6:

=SUM(LEN(A2),LEN(A4),LEN(A6))

Count Characters in a Cell Range

If you want to get the total number of characters in multiple adjacent cells, you can easily list out each cell reference using the formula above, but there’s a better way. You’ll still use the LEN and SUM functions to count characters in a range of cells.

For instance, you can obtain the number of characters in cells A2 through A4 with this formula:

=SUM(LEN(A2:A4))

As you can see, you just add the cell range within the LEN part of the formula to get your total.

Tip: If you’re familiar with the SUMPRODUCT function, you can use it the same way as the SUM function in this case. For our example, you could enter=SUMPRODUCT(LEN(A2:A4))for the same result.

To count characters in two different cell ranges, you can continue to use the SUM and LEN functions.

Here, we’ll get the count for cells A2 through A4 plus C2 through C4 with this formula:

=SUM(LEN(A2:A4),LEN(C2:C4))

In this case, you simply add both LEN formulas within the parentheses for the SUM function’s formula.

Count Specific Characters in a Cell in Excel

Maybe it’s the count of a specificcharacter you need to find. For this, you’ll use LEN with the SUBSTITUTE function.

In this example, we want to count the number of L’s in cell A1. You would use this formula:

=LEN(A2)-LEN(SUBSTITUTE(A2,”L”,””))

You can see our result of 1 is correct. Here’s a breakdown of theformula structurefrom the inside out:

As you’ll notice, the above formula only gives us one instance of the letter L as the result. This is because the formula contains the uppercase letter L, so it doesn’t look for lowercase letters.

To account for this case-sensitivity, you can add either UPPER or LOWER to the SUBSTITUTE formula. In a nutshell, this then replaces all occurrences, regardless of case, with the empty text string.

Using the same example, we’ll add UPPER to the formula as follows:

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),”L”,””))

This time, we receive the result of 3 which includes both upper and lowercase L’s.

Count Specific Characters in a Cell Range

Want to take what you’ve learned so far and go even further? In this last section we’ll show you how to count a specific character in a range of cells. You’ll basically combine LEN, SUBSTITUTE, and SUM.

With this formula, you can count the number of L’s in the cell range A2 through A4:

=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),”L”,””)))

Here, we keep UPPER in the SUBSTITUTE formula to find all instances of the letter L regardless of letter case.

Once you start using these functions and formulas in your worksheet, counting characters in Excel becomes easier each time. No matter what you need the character count for, let Excel do the work instead of counting characters manually.

For more, check out our tutorial forusing COUNTIFS, SUMIFS, and AVERAGEIFS in Microsoft Excel.

Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

How to Count Characters in Microsoft Excel Cells#

Count Characters in a Single Cell in Excel#

Count Characters in Multiple Cells#

Count Characters in a Cell Range#

Count Specific Characters in a Cell in Excel#

Count Specific Characters in a Cell Range#