Counting Characters in Excel

Another useful SUMPRODUCT technique

If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?

That’s where Excel’s most flexible function comes in. SUMPRODUCT works well with ranges and it can process each cell in a range separately. This is just like an array formula, but without the drawbacks of array formulas. I’ll deal with arrays in future blog posts.

In the image below we want to calculate how many characters are in the range A2:A6.

Range To Count Characters

To calculate how many characters are in cell A2 you would use

=LEN(A2)

Which would return 3. (LEN is short for length).

To calculate the number of characters in a range you can use

=SUMPRODUCT(LEN(A2:A6))

In this case the answer is 22 and shown in the image below.

Count Characters in a Range

The SUMPRODUCT function processes each cell within the range to calculate its length and then adds up all the results to arrive at the count for the range.

I showed another use of the SUMPRODUCT function in a previous post here.

SPACES

Note: spaces are characters too. LEN treats a space just like any other character and counts it.

I’ll demonstrate more advanced uses of the SUMPRODUCT function in future posts.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “Counting Characters in Excel

  1. Word Count Software

    Here is best software for counting word and character in excel and word. The Word Count Software is an extremely powerful tool for carrying out the process of word count. This is latest word count software which is used by many transcription and translation industries. The software is a great way to count words and pages. It is also a great way for carrying out line count, page count & character count of various files. The word count tool can be used in multiple files in different formats.

    For more detail: http://www.windowindia.net/word-count-manager.html