How to Count Number of Cells that Contain Exactly N Characters in Excel

This post will guide you how to count the number of cells that contain exactly N charcters within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells that contain a certain length  in your data range.

Count Number of Cells that Contain Exactly N Characters


If you want to count the number of cells that contain a certain length or a certain number of characters(7 characters) in range A1:B6, you can create a formula based on the COUNTIF function. Like this:

=COUNTIF(A1:B6,”???????”)

Or

=COUNTIF(A1:B6,REPT(“?”,7))

count number of cells that contain certain characters1

count number of cells that contain certain characters2

You would see that the above formulas will count the number of cells in a given range A1:B6 which contain exactly 7 characters.

Note: A1:B6 is the range from which to count the cells that contain seven characters.

LET’S SEE THAT HOW THIS FORMULA WORKS:

This above formula will use the question mark “?” character to match one character. So if you wish to match seven characters, and you just need to add 7 characters into the formula.

The REPT function will return a text string made of exactly 7 question marks.

The COUNTIF function will count all the cells in the range that contain exactly 7 characters.

 Related Functions


  • Excel REPT function
    The Excel REPT function repeats a text string a specified number of times.The syntax of the REPT function is as below:= REPT  (text, number_times)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

 

Related Posts

Repeating Character n Times in Excel

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so ...

Calculate Cumulative Loan Interest in Excel

What is Cumulative Loan Interest? When comparing different types of loans, many people want to know what is Cumulative Loan Interest. The sum of all interest payments you've made on a loan is referred to as cumulative interest. Different lenders ...

Calculate Cumulative Loan Principal Payments in Excel

How do you Calculate Cumulative Loan Principal Payments in Excel? There are a few different ways to calculate this information, but one way has been proven to be accurate more often than others. One option is to use the CUMPRINC ...

Trap Error or Replace Error by Specific Value with IFERROR function

We often use Excel formulas in our work life, and we may encounter this situation that the formula throws an error and finally an error like #DIV/0! Is displayed in the cell. In today’s tutorial, we will introduce you the ...

Calculate Compound Interest in Excel

This article will show you how to calculate compound interest in Excel. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest. One of the simplest ...

Sidebar