To count how many times a character appears in a cell, subtract the length of the text after removing that character from the original length. With the text in A2 and the character to count in B2, use =LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")). For example, if A2 contains banana and B2 contains a, the result is 3.
What the LEN and SUBSTITUTE formula does
LEN and SUBSTITUTE solve different parts of the problem. LEN returns the number of characters in a text value, including spaces. SUBSTITUTE replaces specified text with other text. When the replacement is an empty string (""), the matching text is removed. Comparing the length before and after removal reveals how much text was taken out.
For a single character, each match removes one character, so the difference in lengths equals the number of matches. In the banana example, the original length is 6. Removing every a leaves bnn, whose length is 3. The difference, 6 minus 3, is 3.
This is a formula technique rather than a dedicated “count characters” function. It is useful when a cell contains a string and you need the count of a particular character, punctuation mark, digit, or space—not merely the number of cells that meet a condition.
How LEN and SUBSTITUTE work together
Excel evaluates the formula from the inside out. SUBSTITUTE first creates a version of the source text with the target removed. LEN then measures that shorter text, and the outer subtraction compares it with the original length.
LEN(A2)measures the original text.SUBSTITUTE(A2,B2," ")would replace the target with a space, but the counting method uses""to remove it completely.LEN(SUBSTITUTE(A2,B2,""))measures the text after removal.- The original length minus the shortened length is the number of removed characters.
In the working formula, the replacement argument is an empty string, written as two straight double quotes with nothing between them. Do not omit the quotes: they tell Excel to replace the match with no characters. SUBSTITUTE’s argument order is original text, text to find, replacement text, and optionally an instance number.
Count a single character in a cell
With the text to inspect in A2 and one character to count in B2, enter =LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")). If A2 is mississippi and B2 is s, the formula returns 4. You can put the character directly into the formula instead, but a separate input cell makes it easier to change what you are counting.
This version is intended for a one-character target. If B2 contains more than one character, the length difference measures the number of characters removed, not the number of matching strings. For instance, removing every occurrence of ab removes two characters per match. Divide the difference by the target’s length to count those matches, as described below.
To count a character in many rows, place the formula beside the first text cell and fill it down. If the text is in A2:A20 and the target is in B1, use =LEN(A2)-LEN(SUBSTITUTE(A2,$B$1,"")) in C2, then copy it through C20. The dollar signs keep the target reference fixed while the source reference changes by row. Each result is that row’s count; add the results with a sum if you need a total across the list.
Count a word or multi-character string
For a target that may contain several characters, divide the number of characters removed by the target’s length. With the source text in A2 and the search string in B2, use =IF(B2="","",(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2)). If A2 contains banana and B2 contains ana, the formula returns 1: SUBSTITUTE removes one non-overlapping match, removing three characters, and the difference is 3 divided by the target length of 3.
The IF check prevents a blank search cell from causing a division-by-zero error. It returns a blank result when B2 is empty. The count is of exact, non-overlapping matches. In banana, the two possible appearances of ana overlap at the middle letter, so SUBSTITUTE does not count them as two separate replacements. This distinction matters for repeated patterns and is a common reason a result may seem lower than expected.
For a phrase such as New York, the same method counts each exact occurrence of the whole phrase, including the space between the words. It does not count separate appearances of New and York unless those are used as the search strings.
Case sensitivity, spaces, and punctuation
SUBSTITUTE is case-sensitive. A search for lowercase a does not remove uppercase A. If A2 contains Banana and B2 contains lowercase a, the basic formula returns 2, because it matches the two lowercase letters only.
To count a single character without distinguishing uppercase and lowercase, convert both the source and target to the same case first. With the text in A2 and the character in B2, use =LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),LOWER(B2),"")). If A2 is Banana and B2 is a, the result is 3. LOWER changes the case for comparison; it does not alter the contents of the original cells. UPPER can be used in the same way instead.
Spaces are characters too. If A2 contains New York with one space between the words and one trailing space, the formula =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) returns 2. This can help reveal extra spaces in imported data. It counts ordinary space characters; other whitespace characters, such as tabs or line breaks, are different characters and will not be counted by searching for a regular space.
The same approach can count punctuation or a digit by changing the target. Make sure the target is exactly the character present in the source. Curly quotation marks, straight quotation marks, hyphens, en dashes, and em dashes can look similar while being distinct characters.
Common errors and ways to check the result
If the formula returns zero, first check whether the target really appears in the cell and whether its capitalization matches. Then check for visually similar characters, leading or trailing spaces in the target cell, or a search string that is longer than expected. Because SUBSTITUTE requires an exact match, a hidden space in B2 changes what Excel searches for.
- Check the length difference: Compare
LEN(A2)with the length after SUBSTITUTE removes the target. A difference of zero means nothing was removed. - Check capitalization: Use the LOWER or UPPER version if the count should ignore case.
- Check the target cell: Ensure B2 contains only the character or text you intend to find.
- Check the formula punctuation: Use straight double quotes around the empty string, and keep the parentheses balanced.
- Check substring overlap: The multi-character method counts non-overlapping matches, not every possible overlapping position.
Excel formulas use commas between arguments in the examples here. A computer’s regional settings can affect list separators, so some installations may display or require semicolons instead. If Excel rejects an otherwise correct formula, check the separator convention in that installation. In Microsoft 365 for Windows, enter the formula in a cell and press Enter; on Mac, the same formula logic applies, though regional settings can likewise affect separators.
When to use another approach
Use LEN with SUBSTITUTE when you need a transparent count of a particular character or exact text within one cell. It is also easy to copy down a column and inspect row-by-row results. It is not the same as COUNTIF: COUNTIF counts cells that meet a condition, while this formula counts matches inside a text value.
FIND and SEARCH are alternatives when you need to locate a match’s position. FIND is case-sensitive and SEARCH is not, but each returns the position of a match rather than the total number of matches. They are useful for locating the first occurrence, not as a direct replacement for the length-difference counting method.
If the goal is to count how many cells contain a word, rather than how often that word occurs inside each cell, use a cell-counting method instead. Choosing the right approach depends on whether the unit being counted is a character, a text occurrence, or a matching cell.
Frequently Asked Questions
Can LEN and SUBSTITUTE count a letter in every cell in a column?
Yes. Put the single-character formula beside the first cell, use a fixed reference for the target if needed, and fill the formula down. Each row returns its own count. Sum the result column if you need a combined total.
Does the formula count spaces?
Yes. LEN includes spaces, and SUBSTITUTE can remove a regular space so the difference gives the number of spaces. Tabs and line breaks are not regular spaces, so they require searching for those specific characters instead.
Is the LEN SUBSTITUTE formula case-sensitive?
Yes, because SUBSTITUTE matches text case-sensitively. To count a letter regardless of case, apply LOWER or UPPER to both the source text and the search character before substituting.
Why does the formula count only one instance of a repeated phrase?
If matches overlap, SUBSTITUTE removes non-overlapping instances. A target such as ana appears in overlapping positions in banana, but only one full replacement can be made. The length-difference method therefore reports one.
What happens if the search cell is blank?
For the single-character formula, a blank target removes no characters and the result is zero. For the multi-character formula, dividing by the blank target’s length would produce an error, so use the IF-wrapped version to return a blank instead.
Related
- Insert and Delete Rows in Excel: Keyboard Shortcuts
- Insert Checkbox in Excel Without the Developer Tab
- Insert Image in Excel: Steps, Options, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel VLOOKUP Between Two Dates: Find the Matching RangeTo use VLOOKUP for a date that falls between a start date and an end date, sort the…
- Excel Delete Every X Rows: Safe Methods and FormulasYes. In Microsoft Excel for Microsoft 365 on Windows, add a helper column to mark every xth data…
- Is INDIRECT Volatile in Excel? Performance ExplainedYes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named…
- Updating Data Validation Lists in Excel the Right WayTo update an Excel drop-down list, select a cell that uses it, choose Data > Data Validation, and…
- Trim Number in Excel Formula: Remove Decimal PlacesTo remove decimal places without rounding, use TRUNC. With the original number in A2 and a target of…
- Excel Slicer to Filter Data: How to Set Up and Use ItTo filter data with an Excel slicer, select a table cell and choose Table Design > Insert Slicer,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.