=SUM(LEN(A3:A18) - LEN(SUBSTITUTE(A3:A18, D2, "")))īut this formula requires pressing Ctrl + Shift + Enter because, in all versions other than Excel 3, SUM can handle arrays only in an array formula. Instead of SUMPRODUCT, you can also use SUM: SUMPRODUCT(LEN( range) - LEN(SUBSTITUTE( range, character, "")))įor example, to get to know how many times the character in D2 occurs in the range A3:A18, the formula is: For this, just place the LEN formula inside the SUMPRODUCT function that can handle arrays: 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. How to count specific characters in a range Where A3 is the original text string and B3 is the substring to count.įor the detailed explanation of the formula, please see How to count specific text / words in a cell. "C2" or "C-2" or "cat"), then divide the characters count by the length of the substring. If you want to know how many times a certain combination of characters appears in a given cell (e.g. How to count certain text/substring in a cell =LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))Īnd this works beautifully irrespective of the target letter's case: 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(LOWER(A3), "a", ""))Ī slightly more complex way is using nested SUBSTITUTE functions: To make such a formula, use the UPPER function inside SUBSTITUTE to convert a given letter to uppercase before running the substitution.įor example, to count both "A" and "a" in cell A3, use this formula: When counting letters in Excel cells, you may sometimes need a formula that ignores the letter case. Case-insensitive formula to count letters in Excel cell Finally, you subtract the length of the string without "A" from the total length string:Īs the result, you get the count of "removed" characters, which is the number of occurrences of that particular character in the cell.The next step is to find the string length without the letter "A":.Then, you remove all occurrences of the letter "A" in A3 by replacing it with an empty string:.First, you find the total string length in cell A3 with:.To understand the formula's logic, let's break it down into smaller parts: The formula counted only the uppercase "A" and returned 2 as the result. For example, cell A4 in the screenshot above contains one occurrence of "a" and two occurrences of "A". Excel's SUBSTITUTE is a case-sensitive function, and therefore the above formula treats uppercase and lowercase letters as different characters.
0 Comments
Leave a Reply. |