*The tutorial explains how to count characters in Excel. You will learn the formulas to get the total character count in a range, and count only specific characters in a cell or in several cells.*

Our previous tutorial introduced the Excel LEN function, which allows counting the total number of characters in a cell.

The LEN formula is useful on its own, but in liaison with other functions such as SUM, SUMPRODUCT and SUBSTITUTE, it can handle far more complex tasks. Further on in this tutorial, we are going to have a closer look at a few basic and advanced formulas to count characters in Excel.

When it comes to counting a total number of characters in several cells, an immediate solution that comes to mind is to get the character count for each cell, and then add up those numbers:

`=LEN(A2)+LEN(A3)+LEN(A4)`

Or

`=SUM(LEN(A2),LEN(A3),LEN(A4))`

The above formulas might work fine for a small range. To count total characters in a bigger range, we'd better come up with something more compact, e.g. the SUMPRODUCT function, which multiplies the arrays and returns the sum of the products.

Here's the generic Excel formula to count characters in a range:

=SUMPRODUCT(LEN(*range*))

And your real-life formula may look similar to this:

`=SUMPRODUCT(LEN(A1:A7))`

Another way to count characters in a range is to use the LEN function in combination with SUM:

`=SUM(LEN(A1:A7))`

Unlike SUMPRODUCT, the SUM function does not calculate arrays by default, and you need to press Ctrl + Shift + Enter to turn it into an array formula.

As demonstrated in the following screenshot, the SUM formula returns the same total character count:

This is one of the most straightforward formulas to count characters in Excel. The LEN function calculates the string length for each cell in the specified range and returns them as an array of numbers. And then, SUMPRODUCT or SUM adds up those numbers and returns the total character count.

In the above example, an array of 7 numbers that represent the lengths of strings in cells A1 to A7 is summed:

Sometimes, instead of counting all characters within a cell, you may need to count only the occurrences of a specific letter, number, or special symbol.

To count the number of times a given character appears in a cell, use the LEN function together with SUBSTITUTE:

=LEN(*cell*)-LEN(SUBSTITUTE(*cell*, *character*,""))

To better understand the formula, consider the following example.

Suppose, you maintain a database of delivered items, where each item type has its own unique identifier. And each cell contains several items separated by comma, space, or any other delimiter. The task is to count how many times a given unique identifier appears in each cell.

Assuming that the list of delivered items is in column B (beginning in B2), and we are counting the number of "A" occurrences, the formula is as follows:

`=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))`

To understand the formula's logic, let's break it down into smaller parts:

- First, you count the total string length in B2:

`LEN(B2)`

- Then, you use the SUBSTITUTE function to remove all occurrences of letter "
*A*" in B2 by replacing it with an empty string (""):

`SUBSTITUTE(B2,"A","")`

- And then, you count the string length without "
*A*" character:

`LEN(SUBSTITUTE(B2,"A",""))`

- Finally, you subtract the length of the string without "
*A*" from the total length string.

As the result, you get the count of "removed" characters, which equals to a total number of that character occurrences in the cell.

Instead of specifying the character you want to count in a formula, you can type it in some cell, and then reference that cell in a formula. In this way, your users will be able to count occurrences of any other character they input in that cell without tampering with your formula:

If you need a case-insensitive character count, embed the UPPER function inside SUBSTITUTE to convert the specified character to uppercase before running the substitution. And, be sure to enter the uppercase character in the formula.

For example, to count "A" and "a" items in cell B2, use this formula:

`=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2),"A",""))`

Another way is to use nested Substitute functions:

`=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE (B2,"A",""),"a","")`

As you can see in the below screenshot, both formulas flawlessly count uppercase and lower case occurrences of the specified character:

In some cases, you may need to count many different characters in a table, but you may not want to modify the formula each time. In this case, nest one Substitute function within another, type the character you want to count in some cell (D1 in this example), and convert that cell's value to uppercase and lowercase by using the UPPER and LOWER functions:

`=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))`

Alternatively, convert both the source cell and the cell containing the character either to uppercase or lowercase. For example:

`=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))`

The advantage of this approach is that regardless of whether the uppercase or lowercase character is input in the referenced cell, your **case-insensitive character count formula** will return the right count:

If you want to count how many times a **specific combination of characters** (i.e. certain text, or substring) appears in a given cell, e.g. "A2" or "SS", then divide the number of characters returned by the above formulas by the length of the substring.

**Case-sensitive** formula:

`=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)`

**Case-insensitive** formula:

`=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)`

Where B2 is the cell containing the entire text string, and C1 is the text (substring) you want to count.

For the detailed explanation of the formula, please see How to count specific text / words in a cell.

Now that you know an Excel formula to count characters in a cell, you may want to improve it further to find out how many times a certain character appears in a range. For this, we will take the Excel LEN formula to count a specific char in a cell discussed in the previous example, and put it inside the SUMPRODUCT function that can handle arrays:

SUMPRODUCT(LEN(*range*)-LEN(SUBSTITUTE(*range*, *character*,"")))

In this example, the formula takes the following shape:

`=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))`

And here's another formula to count characters in Excel's range:

`=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))`

Compared to the first formula, the most obvious difference is using SUM instead of SUMPRODUCT. Another difference is that it requires pressing Ctrl + Shift + Enter because unlike SUMPRODUCT, which is designed to process arrays, SUM can handle arrays only when used in an **array formula**.

If you don't want to hardcode the character in the formula, you can of course type it in some cell, say D1, and reference that cell in your character count formula:

`=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))`

`=SUM((LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1, ""))) / LEN(D1))`

As you may remember, the SUBSTITUTE function is used to replace all occurrences of the specified character ("A" in this example) with an empty text string ("").

Then, we supply the text string returned by SUBSTITUTE to the Excel LEN function so that it calculates the string length without A's. And then, we subtract that character count from the total length of the text string. The result of these calculations is an array of character counts, with one character count per cell.

Finally, SUMPRODUCT sums the numbers in the array and returns the total count of the specified character in the range.

You already know that SUBSTITUTE is a case-sensitive function, which makes our Excel formula for character count case-sensitive as well.

To make the formula ignore case, follow the approaches demonstrated in the previous example: Case-insensitive formula to count specific characters in a cell.

In particular, you can use one of the following formulas to count specific characters in a range ignoring case:

- Use the UPPER function and enter a character in uppercase:
`=SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(UPPER(B2:B8),"A","")))`

- Use nested SUBSTITUTE functions:
`=SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(SUBSTITUTE((B2:B8),"A",""),"a","")))`

- Use UPPER and LOWER functions, type either an uppercase or lowercase char in some cell, and reference that cell in your formula:
`=SUMPRODUCT(LEN(B2:B8) - LEN(SUBSTITUTE(SUBSTITUTE((B2:B8), UPPER($E$1), ""), LOWER($E$1),"")))`

The below screenshot demonstrates the last formula in action:

This is how you can count characters in Excel using the LEN function. If you want to know how to count words rather than individual characters, you will find a few useful formulas in our next article, please stay tuned!

In the meantime, you can download a sample workbooks with character count formula discussed in this tutorial, and check out a list of related resources at the end of the page. I thank you for reading and hope to see you soon!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Shared email templates

Category: Excel Tips

60+ professional tools for Excel

2019–2010 to do your daily work

2019–2010 to do your daily work

Incredible product, even better tech support…AbleBits totally delivers!

Deborah Bryant

Anyone who works with Excel is sure to find their work made easier

Jackie Lee

The best spent money on software I've ever spent!

Patrick Raugh

Ablebits is a fantastic product - easy to use and so efficient.

Debra Celmer

Excel is at its best now

Annie C.

I don't know how to thank you enough for your Excel add-ins

Jennifer Morningstar

Anybody who experiences it, is bound to love it!

Kumar Nepa

AbleBits suite has really helped me when I was in a crunch!

Nelda Fink

I have enjoyed every bit of it and time am using it

Christian Onyekachi Nwosu

It's the best $100 we've ever spent!

Mike Cavanagh

I love the program, and I can't imagine using Excel without it!

Robert Madsen

One word… WOW!

Dave Brown

Love the products!

David Johnston

It is like having an expert at my shoulder helping me…

Linda Shakespeare

Your software really helps make my job easier

Jeannie C.

Thanks for a terrific product that is worth every single cent!

Dianne Young

I love your product

Brad Gibson

Awesome!!!

Sheila Blanchard

## 48 responses to "How to count characters in Excel: total or only specific characters in a cell / range"

Hi,

I have a Question,

If i Enter a single word in Cell, Entire Row color Should be change.

Hi Vinayaka,

You can find the detailed steps and examples in this tutorial:

How to change a row color based on a cell's value in Excel

How to count specific characters in a range in a specific date as per the above example

How to count numbers in cell Exp:-6,4,9,10=4

Hi,

Could you please solve my problem?

I want to count continue a letter.

Here I am trying to explain that, how many "P" is continue to 6 days.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

P P P P P P P P P P P P P P P P P P P P P P P P

Result will be : 3. 3 time its count continue 6P.(1 to 6, 13-18,19-24)

How this is count in excel?if there is any formula,pls help.

Hi, could you please solve the below issue with Macro. I have a Data with different names but not exact match. I want the maxi matched text displayed in other row or column. Match Names1 with Names 2 and seprate the values.

Please past the below names in excel.

Names 1 Names 2

john.nikki 0123-12 jhon-01, nikki

Rosa 8788-a45- Rosa;R, 56 & German

Y.Cristina ycristina.All-654.09, As

ThomasAndrew GR 56-as, Thomas.gr&Andrew.M/for

All are ok 123 All.are.ok,now @ 123

Need solution, for Solution for

examples example.Needsolution

Thanks in Advance.

Kalyan Challa

Hello, could you please solve the below issue with excel

i have many more "YES" or "NO" on my Excel Row i wannn to count of how many "YES" or "NO" in the Row Which formula is better on the case.

Hi, How to count the total of a specify letter (exa. N) from efferent columns.

Do do i get the text after a number of instances of a unique char

from

E:\Data Bases\Access VB and stuff\Cell references and Date and Time.xlsx

I want to get the text between the 2nd and 3rd "\" and the text after the last "\"

The answers would be

1. Access VB and stuff

2. Cell references and Date and Time.xlsx

Cheers

Ian

Hello,

I can suggest you the following formulas:

1. =MID(A1,FIND("\",A1, FIND("\",A1)+1)+1,FIND("\",A1,FIND("\",A1,FIND("\",A1, FIND("\",A1)+1))+1)-FIND("\",A1, FIND("\",A1)+1)-1)

2. =RIGHT(A1,LEN(A1)-FIND("\",A1,FIND("\",A1,FIND("\",A1, FIND("\",A1)+1))+1))

where cell A1 is “E:\Data Bases\Access VB and stuff\Cell references and Date and Time.xlsx”

Hope it will help you.

For your first example of an array formula, using SUM and LEN, I think you inadvertently reprinted the formula from SUMPRODUCT right before. Shouldn't the formula be {=SUM(LEN(A1:A7))}?

How can I make the character appears higher than other characters in the same cell. For example:

12th of January (the twelfths day of Jan)

X(2X+2) (X to the power of 2X+2)

Thanks for your time

This is perfect!

How to count specific character(s) in a range

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range, character,"")))

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

I've been trying to find a solution like this for years.

Thank you so much!

i am looking for a formula to count the following continous numbers in different cells 111111011101111. I need the answer to be four i.e. only the last four characters to be counted. Any assistance would be appreciated .

example 1005000055555

How to count same digit on the right of the cell

=LEN(A1)-LEN(SUBSTITUTE(A1,A2,""))

Put value in "A1" cell & Enter your lookup Value in "A2"

i.e. A1 = 1005000055555, A2 = 0 then output would be "6"

Kino:

Can you clarify your question, please?

What do you mean by "same digit on the right of the cell"?

Hi, Sorry if I am missing something, but I didn't see a case when you need count times certain number happen in a sequence, example I have a column A2:A2700 with a random list, I would like to count how many time 2 is after 1, and the same for each numbers, the numbers are from 1 to 48 in a random sequence.

I want formula to remove right side alpha character till number number.

for example number is:- MH15HY5859AAAA then result should be - MH15HY5859

MH15HY5859A then result should be - MH15HY5859

please guide the same.

How can I count tom occurences in given single cells exp like Tom,Raj,tom,tom with the help of sumproduct and Len formula please help me for this

Hello everyone

i am trying to use this formula to count how many "m" characters are in specific cells in different sheets {FORMULA =SUMPRODUCT(LEN(Histori!H3+Matematike!L3+Fiskulture!K3+'B. sipermarrjes'!K3+Mjedis!H3)-LEN(SUBSTITUTE(Histori!H3+Matematike!L3+Fiskulture!K3+'B. sipermarrjes'!K3+Mjedis!H3,"m","")))} but every time it finds the character i get #VALUE!.

Can anyone help me?

Thanks in advance

How can I count a text "H" in different mixed columns Alphabatic and numeric

i want count only character not space ,special symble Please solve my problem

Hi

Can you please help here:

This is a attendance sheet in excel. What is the formula to get a count of only P in total column.

Sl.no. 1st April 2nd April 3rd April 4th April 5th April 6th April Total

1 P P P P P W/O ?

Hello!

If each character is typed in a separate cell, you can use the following formula to count the total number of "P":

=COUNTIF(A9:H9,"P")

If all the characters are in one cell, then please try out this formula:

=LEN(A2)-LEN(SUBSTITUTE(A2,"P",""))

Hope you’ll find this information helpful.

I want to count the number of times the letter “D” is used in a row. Columns C to G with the total in column I. Can you please help.

Hello Robert!

If I understand your task correctly, maybe the following formula should work for you:

=SUM(LEN(C1:G1) - LEN(SUBSTITUTE(C1:G1,"D","")))

If there is anything else I can help you with, please let me know.

Building contractors must have a 'tool' that can tell them how many distinct numbers then need to place a 'unit' number on a dwelling. For example a builder is building a place that has 144 units and they want to place a unit number on the door (or somewhere). When the numbers are purchased, how many ones, or twos or fours are required to get the job done, without 'writing' it out?

If I need to start count from right end of the cell, instead of left, how do we do that?

Hello Vishal!

To determine the position number counting from the right, from the total number of characters (LEN function), subtract the position number counting from the left (FIND function or SEARCH)

a1 Cell is containing following formula -

=53.95+56.26+90

I want following result B1 cell ( how many values have been added or how many "+" sign)

result = 3

Hello

If I understand your task correctly, the following formula should work for you:

=LEN(FORMULATEXT(A1)) - LEN(SUBSTITUTE(FORMULATEXT(A1),"+","")) + 1

Hi, I'm trying to string together a formula,but not yet quite succeeding.

Case: We have 100 production orders. on these orders are individual materials. One specific material can be on a production order multiple times.

I want to count on how many production orders a specific material is used, not counting the double entries of a material on the same production order.

Example:

- Order A > material 1, 2, 3, 2, 1, 6

- Order B > material 1, 2, 4, 5, 5

Required outcome:

- material 1 on 2 orders = count is 2

- material 2 on 2 orders (1 double entry) = count is 2

- material 3 on 1 order = count is 1

- material 5 on 1 order (1 double entry) = count is 1

etc

GREATLY appreciated! :-)

John

Hello!

The information presented to you is not enough to give you advice. How many cells does the text "Order A> material 1, 2, 3, 2, 1, 6" take? One? Two? Seven? Write an example of the source data and the result you want to get. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

a1 Cell is containing following text -

"'1.5+2.6+3+2.4+0.5"

I want following result B1 cell ( Sum total of the A1) "=1.5+2.6+3+2.4+0.5" (which is displayed as 10)

Basically I want to change the text string to a formula

Thanks

Hello!

You can only convert text to an Excel formula using a VBA macro.

Can you share the VBA Code for that. Whenever I try it in (my limited knowledge of VBA), it goes back to the same text string.

Thanks

Hello!

I can only help you with working with standard Excel formulas.

Hello everyone,

can anyone help me please to do the same above process for counting words in mixed cell?

Hello!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you.

hi sir

pls give a Autogenerate serial number formula.

for example

column a column B

receipt REC-001

PAYMENTS PAY-001

RECEIPT REC-002

RECEIPT REC-003

PAYMENTS REC- 002

Hi

Can anyone help me with a formula.

I want to count the numbers in a range ( For this I can use countif ), but I want to set max limit.

Ex: IF A1 to Z1 is the range in which I want to count number of "B". But I want to set limit of 10. If B count is more than 10, then my result should be 10 & If B count is 6, then my result should be 6.

Hi,

Please check the formula below, it should work for you:

=IF(COUNTIF(D2:D20,5)>10,10,COUNTIF(D2:D20,5))

I hope I answered your question.

I want to count character between 2 rows at a time, where change in char. took place; :

ex.

1) abcd-.123.456.77

2) abcd-.123.456.12345

Like in above ex. Changes taken after 14 charecter.,

So Ans. Should be 14, it should be automated by applying just formula, instead of count manually.

Thanks.

Hello!

The formula below will do the trick for you:

=MATCH(FALSE,MID(A1,ROW(1:99),1)=MID(A2,ROW(1:99),1),0)

You can learn more about MATCH function in Excel in this article on our blog.

I have a list of names.

e.g.

Bob Anderson

Alexander Trifuntov

John Andrus

Andre Johnson

I want a formula to tell me what the most common string of 3 letters is?

If it involves VB that is fine. It is the logic of the coding that I haven't been able to create. I can just use CTRL+F to put in the ones I THINK are most common, but I might be missing something. And there are 3018 names in my list.

In this case we know, by brute force, observation that it is "a-n-d". Answer: 4 times it happens in this list. The spreadsheet is uses Tab and space to create the delimiters. So, separate cell for each part of the names.

Thank You.

Hey,

You have taught me well. I got it:

{=INDEX(range,MODE(XMATCH(range,range,0)))} *({} Array brackets from CTRL SHIFT ENTER)

I would still like to know a better way. :)

Sorry. It worked to tell me most common item in list but I still need to know how to get most common "piece" of the items. ;) Sigh.