## 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 article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

### 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 signsUse 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 OscarYou can contact me through this contact form