Description
Use this function to determine the length of a text string by counting the number of characters it contains. Supported in Chains.
LEN is useful for text analysis, data validation, and in combination with other text functions.
Syntax
LEN(text)
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid input |
|---|---|---|---|
text |
Yes | The text whose length you want to find. | A text string, a reference to a cell containing text, or a formula that returns text. |
Example
Sample data
| A | B |
|---|---|
| 1 | Hello, World! |
| 2 | Excel |
| 3 | 123 |
| 4 | Space |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Count the total number of characters in a sentence, including punctuation and spaces. | =LEN(B1) |
Counts every character in "Hello, World!," including the comma, space, and exclamation point. Result: 13 |
| Count the number of characters in a simple word. | =LEN(B2) |
Counts every character in "Excel." Result: 5 |
| Count the number of characters in a number that's stored as text. | =LEN(B3) |
Counts each digit in "123" as a character, the same as it would for any text string. Result: 3 |
| Check whether a value has extra leading or trailing spaces by counting all its characters. | =LEN(B4) |
Counts every character in " Space ," including the leading and trailing spaces around the word. Result: 7 |
| Confirm that an empty string has no characters at all. | =LEN("") |
Counts the characters in an empty string typed directly into the formula. Result: 0 |
Notes
- LEN counts all characters, including spaces and special characters.
- When used with a number, LEN first converts the number to text, then counts the characters.
- LEN does not count the characters in the cell formatting.
- For empty cells, LEN returns 0.
- LEN treats each cell in a range separately; it doesn't sum the lengths of multiple cells.
Tips
- Use LEN in combination with LEFT, RIGHT, or MID to extract portions of text based on their length.
- Combine LEN with TRIM to count characters excluding leading and trailing spaces.
- Use LEN for data validation, e.g., to ensure a code or ID is the correct length.
- In array formulas, LEN can be used to process multiple cells at once.
- Remember that LEN counts Unicode characters, so some special characters or symbols may be counted as more than one character.