The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters, as you can see in cell C9.
Numbers in Excel (including dates, times, currency, etc.) are often formatted with a number format. It's important to understand that the characters that make up numbers are counted in their raw form (i.e. number formatting is not included). For example, because Excel dates are serial numbers, the result in cell C11 is 5, since the date in B11 is is actually the number 42005.
SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the items in this array and returns the...
This formula works by using SUBSTITUTE to first remove all of the characters being counted in the source text. Then the length of the text (with the character removed) is subtracted from the length of the original text. The result is the number of...
The Excel LEN function returns the length of a given text string as the number of characters. LEN will also count characters in numbers, but number formatting is not included.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.