The LEN function returns the number of characters in a cell value.

1. LEN Function Syntax

LEN(text)

Back to top

2. LEN Function Arguments

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

Back to top

3. LEN Function Example

Formula in cell C3:

The LEN function counts all characters in a given cell. 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

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

Excel 365 dynamic array formula in cell E3:

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

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