Site icon excelwrap

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.

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

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.

=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

=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.

={=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.

=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.

=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.

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.

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

See, how it works.

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.

Exit mobile version