LEN function type: Text

Description: Returns the number of characters in a text string
LEN is a basic function widely used, many formulas built for text processing will need this function.

Take a look at the advanced applications examples:

Applications:

• Get the text with the lowest length from a column (first match only)
• Count how many cells within a range of cells have a text length higher than normal

Download the LEN function demo: LEN.xlsx

Function Arguments:

=LEN(text)

Note: If you are already familiar with basic use of the LEN Function, click here to Go To Advanced Examples.

Basic Example:

Basic Example LEN function

Note that all characters are counted, including spaces or other invisible characters, like Alt+Enter, which inserts a new line into the same cell ( or char(10), in cell D24).

Advanced Examples:

Application 1: Get the text with the lowest length from a column (first match only):

Note: The references used in the text below are the same used in the downloadable workbook.

Method: Instead of passing a single cell reference to LEN function, we can pass an array of cells, the return will be an array of text length: =LEN(C38:C41) will result in: {6;5;9;8}. This is an array formula, confirmed with Ctrl+Shift+Enter, not just Enter after editing the formula.
If we compare the length of the cells from a range with the smallest text length from the same range, we get an array of row numbers: IF(LEN(C38:C41)=SMALL(LEN(C38:C41),1),ROW(C38:C41),””)={“”;39;””;””}. Note that in the result array we will have row numbers only for the matching rows, the rest of cells will return a zero length string.

Using again the SMALL function with the array of results {“”;39;””;””}, will return the row number of the first match found: =SMALL({“”;39;””;””},1)=39

Full Formula (confirmed with Ctrl+Shift+Enter):

=INDEX(C:C, SMALL(IF(LEN(C38:C41)=SMALL(LEN(C38:C41),1),ROW(C38:C41),””),1))

Note that blank cells within the range will be taken into consideration, they will become the cells with the smalest length: 0.

Application 2: Count how many cells within a range of cells have a text length higher than normal:

Problem description: If you have a list of Product Codes, and those codes should have a limited number of characters, 7 for example, the formula based on LEN function will return the number of items where the text length is below or above 7.

Method:

Passing a range to LEN function, instead of a single cell reference, will return an array of results:

Advanced example 2 LEN function

=LEN(C51:C54) will return: {7;6;7;8}

Comparing the lengths array with 7, will return an array with TRUE or FALSE:

=LEN(C51:C54)<>7 returns: {FALSE;TRUE;FALSE;TRUE}

If we convert the TRUE and FALSE result to numeric values, by multiplying the array with 1, we get an array of 0 and 1:

=(LEN(C51:C54)<>7)*1 will return: {0;1;0;1}

All we need to do is to SUM the array of results:

=SUMPRODUCT((LEN(C51:C54)<>7)*1)

Note: due to the nature of SUMPRODUCT function, which is a native array function, the formula can be used as a regular formula, not an array formula.

Same result can be achieved with SUM function, but because SUM function is not a native array function, the formula should be entered as an array formula, with Ctrl+Shift+Enter, otherwise the result will be incorrect:

=SUM((LEN(C51:C54)<>7)*1)

If you inserted the formula correctly, you will see it in the formula bar surrounded by curly brackets, including the equal sign:

{=SUM((LEN(C51:C54)<>7)*1)}

The curly brackets are indicating that the formula is an array formula. The curly brackets are not manually typed, Excel will automatically create them when you press Ctrl+Shift+Enter keys after editing the formula.

Tip: To easily find the rows with problems, you can use a simple conditional formatting rule, based on text length. In this example, I set a rule applied to C51:C54, with a formula: =LEN($C51)<>7. See the downloadable workbook for a functional example.

Enjoy

Catalin