What is the LEN function?

The LEN function returns a number representing the total count of characters in a given cell value.

What is the difference between LEN and LENB functions?

The LEN and LENB functions handle character counting differently based on your default language setting in Excel. LENB is designed for double-byte languages like Chinese, Japanese, Korean. It counts each double-byte character as 2 when a DBCS language is set as the default.

LEN always counts each character as 1, single-byte or double-byte, regardless of language setting. So LENB will return larger position values than LEN for the same text in a DBCS language. The difference accounts for the double-byte characters taking 2 positions.

Japanese, Chinese (Simplified), Chinese (Traditional), and Korean are some examples of languages supporting DBCS.

1. LEN Function Syntax

LEN(text)

Back to top

2. LEN Function Arguments

text	Required. The text string or cell reference you want to count the number of characters in.

 

Back to top

3. LEN Function Example

The LEN function counts all characters in a given cell. The image above demonstrates the LEN function in column C.

Formula in cell C3:

Cell B3 contains "apple" without the double quotes, the LEN function in cell C3 returns 5 because apple has five characters.

Can I use the LEN function with a cell range?

You can also use a cell range, however, the output is an array of values. You need to enter the formula as an array formula if you use an Excel version earlier than Excel 365, as far as I know.

Array formula in cell D3:D6:

Excel 365 subscribers may enter the formula as a regular formula, the values spill automatically to cells below as far as needed.

Back to top

4. Count characters based on condition - example 1

This example

Excel 365 dynamic array formula in cell F3:

4.1 Explaining formula

Step 1 - Logical expression

The equal sign lets you compare value to value, you can also compare value to an array of values. The result is an array of boolean values TRUE or FALSE with the same number of items as the original array.

B3:B10=E3

becomes

{"bus"; "boat"; "train"; "airplane"; "bus"; "rocket"; "bike"; "bus"}="bus"

and returns

{TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}.

Step 2 - Filter values based on a logical expression

The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])

FILTER(C3:C10, B3:B10=E3)

becomes

FILTER({"PFBALWHHGPCVXYFOVIB"; "YDSUDFAQTB"; "LXUUCBSUVGPQQDMFD"; "BAKNRWOTCXXDVX"; "MEXPFTJTIO"; "IWGTPEBOFRLSMQ"; "SDFSTGUOCQS"; "BIJUNKKGJUAYJ"}, {TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE})

and returns

{"PFBALWHHGPCVXYFOVIB"; "MEXPFTJTIO"; "BIJUNKKGJUAYJ"}.

Step 3 - Count characters in the array, value by value

LEN(FILTER(C3:C10, B3:B10=E3))

becomes

LEN({"PFBALWHHGPCVXYFOVIB"; "MEXPFTJTIO"; "BIJUNKKGJUAYJ"})

and returns

{19; 10; 13}.

Step 4 - Add numbers and return a total

The SUM function calculates a total.
SUM(number1, [number2], ...)

SUM(LEN(FILTER(C3:C10, B3:B10=E3)))

becomes

SUM({19; 10; 13})

and returns 42.

Back to top

5. Count characters based on condition - example 2

The folllowing fomrula counts values that contain a specific substring specified in cell D3.

Excel 365 dynamic array formula in cell E3:

Cells B4, B5, and B6 all contain the specified substring, their character count is 4,5 and 8 which makes it a total of 17 chararcters.

5.1 Explaining formula

Step 1 - Find string in cell range B3:B10

The SEARCH function returns a number representing the position of the character at which a specific text string is found reading left to right.
SEARCH(find_text,within_text, [start_num])

SEARCH(D3, B3:B10)

becomes

SEARCH("a", {"bus"; "boat"; "train"; "airplane"; "bus"; "rocket"; "bike"; "bus"})

and returns

{#VALUE!; 3; 3; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!}

Step 2 - Check if number

The ISNUMBER function checks if a value is a number, and returns TRUE or FALSE.
ISNUMBER(value)

ISNUMBER(SEARCH(D3,B3:B10))

becomes

ISNUMBER({#VALUE!; 3; 3; 1; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE}.

Step 3 - Filter values

The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])

FILTER(B3:B10,ISNUMBER(SEARCH(D3,B3:B10)))

becomes

FILTER(B3:B10,{FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE})

and returns

{"boat"; "train"; "airplane"}.

Step 4 - Count characters

LEN(FILTER(B3:B10,ISNUMBER(SEARCH(D3,B3:B10))))

becomes

LEN({"boat"; "train"; "airplane"})

and returns

{4; 5; 8}.

Step 5 - Add numbers and return a total

The SUM function calculates a total.
SUM(number1, [number2], ...)

SUM(LEN(FILTER(B3:B10, ISNUMBER(SEARCH(D3, B3:B10)))))

becomes

SUM({4; 5; 8})

and returns 17.

Back to top

6. Count characters based on a list

Excel 365 dynamic array formula in cell F3:

6.1 Explaining formula

Step 1 - Compare the list with values in B3:B10

The COUNTIF function calculates the number of cells that meet a given condition.
COUNTIF(range, criteria)

COUNTIF(E3:E4,B3:B10)

becomes

COUNTIF({"bus"; "bike"},{"bus"; "bike"; "train"; "airplane"; "bus"; "rocket"; "bike"; "bus"})

and returns

{1; 1; 0; 0; 1; 0; 1; 1}.

Step 2 - Filter values based on a logical expression

The FILTER function lets you extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])

FILTER(C3:C10, COUNTIF(E3:E4,B3:B10))

becomes

FILTER({"PFBALWHHGPCVXYFOVIB"; "YDSUDFAQTB"; "LXUUCBSUVGPQQDMFD"; "BAKNRWOTCXXDVX"; "MEXPFTJTIO"; "IWGTPEBOFRLSMQ"; "SDFSTGUOCQS"; "BIJUNKKGJUAYJ"}, {1; 1; 0; 0; 1; 0; 1; 1})

and returns

{"PFBALWHHGPCVXYFOVIB"; "YDSUDFAQTB"; "MEXPFTJTIO"; "SDFSTGUOCQS"; "BIJUNKKGJUAYJ"}.

Step 3 - Count characters in the array, value by value

LEN(FILTER(C3:C10, COUNTIF(E3:E4,B3:B10)))

becomes

LEN({"PFBALWHHGPCVXYFOVIB"; "YDSUDFAQTB"; "MEXPFTJTIO"; "SDFSTGUOCQS"; "BIJUNKKGJUAYJ"})

and returns

{19; 10; 10; 11; 13}.

Step 4 - Add numbers and return a total

The SUM function calculates a total.
SUM(number1, [number2], ...)

SUM(LEN(FILTER(C3:C10, COUNTIF(E3:E4,B3:B10))))

becomes

SUM({19; 10; 10; 11; 13})

and returns 63. 19 + 10 + 10 + 11 + 13 equals 63.

Back to top

7. Count characters in a formula

Does the LEN function count characters in a formula?

The LEN function does not count the number of characters in a formula, however, it counts the number of characters a formula returns.

To count the characters of a formula use the FORMULATEXT function and the LEN function combined.

Formula in cell G7:

7.1 Explaining formula

Step 1 - Extract formula text

The FORMULATEXT function returns the contents of a cell as long as it contains a formula.
FORMULATEXT(reference)

FORMULATEXT(G3)

returns

"=SUM(LEN(FILTER(C3:C10,COUNTIF(E3:E4,B3:B10))))"

Step 2 - Count characters

LEN(FORMULATEXT(G3))

becomes

LEN("=SUM(LEN(FILTER(C3:C10,COUNTIF(E3:E4,B3:B10))))")

and returns 47.

Back to top

8. Count characters in multiple cell ranges

Formula in cell B12:

7.1 Explaining formula

Step 1 - Join cell ranges vertically

The VSTACK function lets you join cell ranges, it joins data to the first blank cell at the bottom of a cell range or array.
VSTACK(array1,[array2],...)

VSTACK(B3:B9,D3:D9,F3:F9)

becomes

VSTACK({"V"; "OECER"; "PDY"; "GOGA"; 0; 0; 0},{"M"; "KA"; "GXQ"; "SU"; "DH"; "VJ"; "DMX"},{"LXECV"; "OIWH"; "DRM"; "FT"; "SLQ"; 0; 0})

and returns

{"V"; "OECER"; "PDY"; "GOGA"; 0; 0; 0; "M"; "KA"; "GXQ"; "SU"; "DH"; "VJ"; "DMX"; "LXECV"; "OIWH"; "DRM"; "FT"; "SLQ"; 0; 0}

Step 2 - Count characters

LEN((VSTACK(B3:B9,D3:D9,F3:F9))

becomes

LEN({"V"; "OECER"; "PDY"; "GOGA"; 0; 0; 0; "M"; "KA"; "GXQ"; "SU"; "DH"; "VJ"; "DMX"; "LXECV"; "OIWH"; "DRM"; "FT"; "SLQ"; 0; 0})

and returns

{1; 5; 3; 4; 0; 0; 0; 1; 2; 3; 2; 2; 2; 3; 5; 4; 3; 2; 3; 0; 0}.

Step 3 - Add numbers and return a total

The SUM function calculates a total.
SUM(number1, [number2], ...)

SUM(LEN((VSTACK(B3:B9,D3:D9,F3:F9))))

becomes

SUM({1; 5; 3; 4; 0; 0; 0; 1; 2; 3; 2; 2; 2; 3; 5; 4; 3; 2; 3; 0; 0})

and returns 45.

Back to top

Useful resources

LEN function - Microsoft support
Count Specific Text in Cell

'LEN' function examples

Functions in 'Text' category

The LEN function function is one of many functions in the 'Text' category.

Excel function categories

Excel categories

Leave a Comment