by Want to know how many characters there are in a certain cell? This tutorial will help you choose an Excel formula for character count best suited for your particular case.
Initially, Excel was designed to work with numbers. Fortunately, the developers of this helpful application didn't forget about text. Below, you'll find a number of formulas for counting characters in Excel. Just look through the examples and see which one best suits your needs.
Here are the cases we are going to cover:
Excel formula to count the number of characters in a cell
First things first, let's solve the most common case. To find how many characters there are in an Excel cell, the formula is as simple as:
For example, to count characters in each cell of column A beginning in A3, this is what you need to do:
- Enter the below formula in any empty cell in row 3:
=LEN(A3) - Double-click the fill handle to get the formula copied across the whole column.
Done!
Feel free to use this formula each time you need to count the number of characters in a string.
Note. Please pay attention that the Excel LEN function counts absolutely all characters in a cell, including letters, numbers, punctuation marks, special symbols, and all spaces (leading, trailing and spaces between words).
Count characters in a range of cells
To get the total of characters in an Excel range, you can use the LEN function together with SUMPRODUCT:
And your real-life formula may look similar to this:
=SUMPRODUCT(LEN(A3:A18))
Another way to count all characters in a range is using LEN in combination with the SUM function:
=SUM(LEN(A3:A18))
Unlike SUMPRODUCT, the SUM function does not process arrays by default, so you need to press Ctrl + Shift + Enter to turn it into an array formula in Excel 2019 and earlier. In Excel 365 and 2021, it works as a regular formula due to inbuilt support for dynamic arrays.
How this formula works:
The logic is very simple. The LEN function calculates the string length for each individual cell in the specified range and returns an array of numbers. And then, SUMPRODUCT or SUM adds up those numbers and returns the total character count.
How to count specific characters in a cell
To find out how many times a given character appears in a cell, the generic formula is:
Suppose you maintain a database of 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 get the number of occurrences of a certain unique identifier in each cell.
Assuming the list of items is in column A beginning in A3, and the target character is in column B in the same row, the formula is as follows:
=LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))
Note. Excel's SUBSTITUTE is a case-sensitive function, and therefore the above formula treats uppercase and lowercase letters as different characters. For example, cell A4 in the screenshot above contains one occurrence of "a" and two occurrences of "A". The formula counted only the uppercase "A" and returned 2 as the result.
How this formula works:
To understand the formula's logic, let's break it down into smaller parts:
- First, you find the total string length in cell A3 with:
LEN(A3) - Then, you remove all occurrences of the letter "A" in A3 by replacing it with an empty string:
SUBSTITUTE(A3, "A", "") - The next step is to find the string length without the letter "A":
LEN(SUBSTITUTE(A3, "A", "")) - Finally, you subtract the length of the string without "A" from the total length string:
LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))
As the result, you get the count of "removed" characters, which is the number of occurrences of that particular character in the cell.
Case-insensitive formula to count letters in Excel cell
When counting letters in Excel cells, you may sometimes need a formula that ignores the letter case. To make such a formula, use the UPPER function inside SUBSTITUTE to convert a given letter to uppercase before running the substitution.
For example, to count both "A" and "a" in cell A3, use this formula:
=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), "A", ""))
The LOWER function will also do:
=LEN(A3) - LEN(SUBSTITUTE(LOWER(A3), "a", ""))
A slightly more complex way is using nested SUBSTITUTE functions:
=LEN(A3) - LEN(SUBSTITUTE(SUBSTITUTE (A3, "A", ""), "a", "")
In our data set, the letters to be counted are input in column B, so we convert both the source cell and the cell containing the character to uppercase:
=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))
And this works beautifully irrespective of the target letter's case:
How to count certain text/substring in a cell
If you want to know how many times a certain combination of characters appears in a given cell (e.g. "C2" or "C-2" or "cat"), then divide the characters count by the length of the substring.
Case-sensitive formula:
=(LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))) / LEN(B3)
Case-insensitive formula:
=(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))) / LEN(B3)
Where A3 is the original text string and B3 is the substring to count.
For the detailed explanation of the formula, please see How to count specific text / words in a cell.
How to count specific characters in a range
Knowing a formula for counting certain characters in a single cell, it's quite easy to modify it a little further to count the number of occurrences of a given character in several cells. For this, just place the LEN formula inside the SUMPRODUCT function that can handle arrays:
For example, to get to know how many times the character in D2 occurs in the range A3:A18, the formula is:
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))
Instead of SUMPRODUCT, you can also use SUM:
=SUM(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))
But this formula requires pressing Ctrl + Shift + Enter because, in all versions other than Excel 365 and 2021, SUM can handle arrays only in an array formula.
How this formula works:
The SUBSTITUTE function replaces all occurrences of a given character ("A" in this example) with an empty string ("").
The text string returned by SUBSTITUTE is served to the LEN function so it calculates the string length without A's.
The string length without A's is subtracted from the total length of the original string. The result is an array of character counts per cell.
Finally, SUMPRODUCT sums the numbers in the array and returns the total character count in the range.
Case-insensitive formula to count letters in a range
To create a case-insensitive formula for counting specific characters in a range, follow the same approaches that we used for counting certain letters in a cell regardless of the text case.
Use the UPPER function and supply an uppercase letter:
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(UPPER(A3:A18), "A", "")))
Use the LOWER function and supply a lowercase letter:
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(LOWER(A3:A18), "a", "")))
Nest a couple of SUBSTITUTE functions one into another:
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(SUBSTITUTE((A3:A18), "A", ""), "a", "")))
In the character of interest is input in a predefined cell, UPPER or LOWER will work equally well:
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(UPPER(A3:A18), UPPER(D2), "")))
Or
=SUMPRODUCT(LEN(A3:A18) - LEN(SUBSTITUTE(LOWER(A3:A18), LOWER(D2), "")))
The below screenshot shows it in action:
How to count certain text / substring in a range
To count the number of occurrences of certain text in a range, use this generic formula:
For example, to count the number of times the word "Life" appears in the range A3:A18, the formula is:
=SUMPRODUCT((LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, ""))) / LEN(D2))
Excel character limits for cells
Microsoft Excel has a limitation on the number of characters that can be entered in a cell. If you have worksheets with large amount of text data, you may find the following information helpful.
- The total number of characters that a cell can contain is 32,767.
- A cell can only display 1,024 characters. At the same time, the Formula bar can show all 32,767 symbols.
- The maximum length of a formula is 8,192 characters in Excel 2007 and higher (1,014 in Excel 2003).
Please consider the facts when you are going to merge or import data from an external source.
These are the best practices for counting characters in Excel. For first-hand experience, you can download a sample workbook and check out a list of related resources at the end of the page. Thank you for reading and hope to see you soon!
Practice workbook for download
Count characters in Excel - formula examples (.xlsx file)
147 comments
I want to do sum for the following which contain number & Words
20 cows
60 Cows
70 cows
Result should be 150 or 150 cows.
But if I use auto sum it does not work. How can I do it? Pls help me.
Paul:
Split the numbers from the words using Text-to-Columns.
Highlight the cell, go to Data then choose Text-to-Columns and follow the prompts. After the words and numbers have been separated into their own cells you can use =Sum on the cells that contain numbers.
sum samad26 karim26
how I will sum? please answer?
Hi,
I have data numeric and text with numeric, pls help here how could i count how many times 123456 in data sheet.
i hv use =SUMPRODUCT(--(LEFT(Sheet1!$G$2:$G$28069,6)=E4)) work ok, but when i was change formula =SUMPRODUCT(--((LEFT(Sheet1!G2:G28068,6)+0)=Sheet2!C3)) this was not working.
123456-1234
321654-3214
456789-8579
PO111991
123456-1234
Hello
How do i count a reply yes for example for cells not arranged within a range
E. G
I created a checklist on excel and the reply to the answers yes/no as per the questions are not arranged in typical range
Result analysis formular in excel
i want to make result analysis in a way i want the number of As,Bs, Cs etc
on a excel templete using formular.
Best Regards
NWANNA, D.
i want to convert "PP" into numeric in attendance sheet
example :- "P" count 1, if we insert double P into one Cell of MS Excel then count 2
I am trying to count the number of names in a cell. What formula would I use to do this? The name format is first and last which would count as one name.
Dear Natalia,
I would like to clarify...I have a attendence sheet..it cotains Present,Sick leave and annual leave...I use countif formula to get total sick leave and annual leave.then got data.
But when i filer the sheet based on user the total sick leave and annual leave are not changing.still showing same number...i am requsting to get a formula to solve this issue...
When filter sheet by name result should be show only filtered user only...subtotal formula
I hope clear my question...your quick reply highly appreciated
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
How can use countif function in subtotal formula...
when i use subtotal function i couldnot find countif formula in the list...there is count and countA in the list...
thank you in advance for your valuable support
Dear support,
I have big sheet.there is contain text id..when i filter sheet i cannot get total count of filtered text...
Hello,
If I understand your task correctly, please try the following formula:
=SUBTOTAL(103,A2:A100)
Hope it will help you.
Hey I want to make shift schedule.. there are 40 members are there. I want to make sum for in any row or column if I will write down for example "m" i total how many "m" are there in row and column. please help me.
Hi,
I have data look like this below
R0754117
R0658417
P0256413
PX5698452
SO2584696
How do I do the countif wihtout the numeric? I just want to know how many count for R, P, PX and SO? Your help is appreciated. Thanks!
Hi, Amanda,
if the data is situated in A1:A5, use the following formula to count the number of cells with R at the beginning of the cell:
=COUNTIF(A1:A5,"R*")
To count the rest of the values simply change R in the formula to other letters.
You can read more about COUNTIF function on our blog post here.
FIRST APPLY THIS
=LEFT(TEXT CELL,MIN(FIND({1,2,3,4,5,6,7,8,9,0},TEXT CELL&"1234567890")-1))
IT'S GIVE U A NUMBER, AFTER THAT U WILL COPY THIS FORMULA AND PASTE IN THIS
=SUBSTITUTE(TEXT CELL, LEFT(TEXT CELL,MIN(FIND({1,2,3,4,5,6,7,8,9,0},TEXT CELL&"1234567890"),"")
I HOPE U WILL BE GET YOU ANSWER
Hi,
I’m working in one of the Trading Company, regularly need to prepare the delivery note report in Excel. Delivery note number is like D17/H0001. So can you please any one of them to provide code for add delivery note number +1.
For example “D17/H00001”, Next row should be “D17/H00002”. I need the code only.
Appreciate for your Help!
I need help with using Excel 2010 on a formula that will display how many spaces and characters combined are in each cell with a text file. Your help is appreciated.
Tammy
hI,
I have a workbook with 20 worksheets.In each worksheet I want to tally the amount of times the user selects from a drop down box of 10 options.I want to calculate the number of times each option is used on each worksheet and grand tally on the cover sheet.
Please help
How to Sum of this data
4 hours
8 hours
3 hours
6 hours
=sum(?????
Hello, Yogender,
at first, please make sure that the Time format is applied to your values.
Then, if the data starts in A2, use the following formula:
=SUM(A2:A5)
If the values are not in the cells use the following:
=SUM(TIME(4,0,0),TIME(8,0,0),TIME(3,0,0),TIME(6,0,0))
Im trying to count cells that contain numbers and text is this possible?
I just want to get the sum of the numbers in each cell but there is text also in this cell.
TOTAL MONTH OF PRESENT WORKERS FOR EX. P (1TO 15TH DAYS) & ABSENT A (16th to 31st)days in excel.
i have any help for using the formulas in this cell sum total. separate total present days & total absent days
I have a string of data, one number in its own cell which are mixed between numbers and H plus a number that we use for holidays on our timesheets, so may 6 H7 H7 7 8 8 How can I just record the numbers after the H so I can track the numbers of holidays being claimed?