In Excel, you can count the length of cells using the LEN function, which returns the number of characters. You can combine the LEN and SUMPRODUCT functions to get the number of cells that equals a certain length (e.g., 15).
Example: In the following database, the first name is in column A, the last name is in column B, and the email address is in column C.
Formula: To count the number of cells with the length of email addresses equal to 15.
=SUMPRODUCT((LEN(C2:C12)=15)*1)
=SUMPRODUCT(–(LEN(C2:C12)=15))
The result returns 3, so three cells have email addresses equal to 15.
- Step 1: (LEN(C2:C12)=15) returns true if the length of the cell equals 15 and false if not 15;
- Step 2: The double hyphen (or times 1) converts true into one and false into zero;
- Step 3: The SUMPRODUCT function returns the product of the new array.
Notes: The LEN Function
The LEN function returns the number of characters in a text string.
Formula:
=LEN(text)
Explanations:
– The text whose length you want to find and spaces count as characters.
