# LEN function: Basic and Advanced Examples

**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)**

Argument | Microsoft Description |

text |
The text whose length you want to find. Spaces count as characters. |

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

**Basic Example:**

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:

=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