How to use the LEN function
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.
Table of Contents
1. LEN Function Syntax
LEN(text)
2. LEN Function Arguments
| text | Required. The text string or cell reference you want to count the number of characters in. |
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.
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.
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.
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.
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.
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.
Useful resources
LEN function - Microsoft support
Count Specific Text in Cell
'LEN' function examples
This post explains how to lookup a value and return multiple values. No array formula required.
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors. Why […]
Functions in 'Text' category
The LEN function function is one of 29 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form