How to use the LEN function
The LEN function returns the number of characters in a cell value.
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. |
The LEN function does not count the number of characters in a formula, however, it counts the number of characters a formula returns.
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.
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.
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.
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
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.
'LEN' function examples
The following 27 articles contain the LEN function.
This post explains how to lookup a value and return multiple values. No array formula required.
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Functions in this article
Functions in 'Text' category
The LEN function function is one of many 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