Skip to content Skip to main navigation Skip to footer

How to count characters in Excel.

Sometime. you may need to count characters in a cell or in a range of cells in Microsoft Excel. This content will guide you on how to count characters in Excel. This is an easy and simple tutorial you may like most.

To count characters in Microsoft Excel you can use the LEN function.

SYNTAX: =LEN(text)

The followings are the Arguments for the formula

text (required) – the text you want to count the characters. free space is also counted as a character.

Character means text, number and free spaces.

Read More: How do you merge cells in Excel?

How to count characters in Excel

Count characters in a cell

I have prepared a worksheet to count characters. Please see the screenshot below.

Count characters in Excel_01

As stated above I will use the LEN function to count the character in each cell. See, how it works.

Count characters in Excel_02

As I want to count the number of characters in cell B3, I have used the formula LEN(B3) for counting.

=LEN(B3)

Count characters in a range of cells

If I want to count the character in a range of cells B3:B8, I can add all cells in the range to count the characters.

count the character in a range of cells

=LEN(B3)+LEN(B4)+LEN(B5)+LEN(B6)+LEN(B7)+LEN(B8)

Check the alternative way to count the character in a range of cells B3:B8, using the SUM function

Count characters in Excel_0

=SUM(LEN(B3),LEN(B4),LEN(B5),LEN(B6),LEN(B7),LEN(B8)

Yes, you are right, this formula is long and it kills time to write and boring too. Is there any solution to this? An array can solve this issue.

count characters in an array

={=SUM(LEN(B3:B8))}

There is a little difference, you see the formula is closed by a curly bracket. Do not write by yourself. It will not work. First, you complete the formula =SUM(LEN(B3:B8)) then press Ctrl+Shift+Enter button on the keyboard. Microsoft Excel will add those curly brackets by itself.

The same purpose can be solved by the SUMPRODUCT function. It seems a little bit easy. See the screenshot below.

Count characters in Excel using SUMPRODUCT function

=SUMPRODUCT(LEN(B3:B8))

Count specific characters in a cell

What we have done above is count characters in a cell or range of cells. But do you think is it possible to count any specific character in a cell? It can be done with a single function but need a combination of LEN and SUBSTITUTE function together. See, how it works.

count specific numbers in a cell

=LEN(B3)-SUBSTITUTE(B3,”a”,””))

There is something to explain this time. SUBSTITUTE is a function that replaces anything with any other things. What is happening here is very simple. check the steps below.

  • LEN(B3) counts the number of characters in cell B3.
  • The SUBSTITUTE function replaces all characters in cell B3 with the empty string (“”) except “a”. LEN function counts the number of characters.
  • The difference between LEN(B3) and LEN(SUBSTITUTE(B3,”a”,””)) is our answer.

Count specific characters in a range of cells

It is a little bit difficult but very tough to understand. The formula is long but I will explain how it works.

count specific character in a range of cells

=SUMPRODUCT(LEN(B3:B8)-SUMPRODUCT(LEN(SUBSTITUTE(B3:B8,”a”,””)))

See, how it works.

  • SUMPRODUCT(LEN(B3:B8) counts the total character in the range.
  • LEN(SUBSTITUTE(B3:B8,”a”,””) replace characters with empty strings except “a”. and count them. SUMPRODUCT counts total characters in the range.
  • The difference is our required answer.

Dear learner, if you find any difficulties understanding do not hesitate to post a comment. I will come back to you with a reply soon.

Thank You.

0 Comments

There are no comments yet

Leave a comment

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