Excel LEN Function
LEN Function
The LEN function is a premade function in Excel, which returns the number of characters in a text.
It counts every character: letters, digits, symbols and spaces.
It is typed =LEN and has one part:
=LEN(text)
Text: The text to count the characters of. This is usually a cell, like A2.
Note: Spaces are characters too. Poke Ball has 8 letters, but LEN returns 9, because the space is also counted.
How to use the LEN function:
- Select a cell (
B2) - Type
=LEN - Double click the LEN command
- Select the cell with the text (
A2) - Hit enter
Let's have a look at an example!
These are the products in the Poke Mart shop. Copy them and paste them into cell A1 to follow along:
Example data
A B 1 Product Characters 2 Poke Ball 3 Great Ball 4 Ultra Ball 5 Potion 6 Super Potion 7 Hyper Potion 8 Revive 9 Antidote 10 Paralyze Heal 11 Awakening
Use the LEN function to count the characters in each product name, in column B:
| A | B | |
|---|---|---|
| 1 | Product | Characters |
| 2 | Poke Ball | 9 |
| 3 | Great Ball | 10 |
| 4 | Ultra Ball | 10 |
| 5 | Potion | 6 |
| 6 | Super Potion | 12 |
| 7 | Hyper Potion | 12 |
| 8 | Revive | 6 |
| 9 | Antidote | 8 |
| 10 | Paralyze Heal | 13 |
| 11 | Awakening | 9 |
=LEN(A2)
LEN counts the characters in A2. Poke Ball has 9 characters: 8 letters and 1 space.
The function is repeated with the filling function for each row, down to B11.
Paralyze Heal is the longest name, with 13 characters.
Note: LEN also works on numbers. It counts the digits of the value, not how the cell is formatted. A price of 1200 has a length of 4, even if the cell is formatted to show $1,200.00.
Find Extra Spaces With TRIM
Extra spaces are hard to see, but LEN counts them. Combine LEN with TRIM to find them.
TRIM removes the spaces before and after a text, and changes double spaces between words into single spaces. If the length is smaller after TRIM, the text had extra spaces.
These Pokemon names were copied from a sign-up form. Some of them have extra spaces:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Length | Trimmed | Extra Spaces |
| 2 | Pikachu | 7 | 7 | 0 |
| 3 | Eevee | 6 | 5 | 1 |
| 4 | Snorlax | 9 | 7 | 2 |
| 5 | Mr. Mime | 9 | 8 | 1 |
| 6 | Jigglypuff | 10 | 10 | 0 |
| 7 | Psyduck | 10 | 7 | 3 |
The formula in B2 counts all the characters:
=LEN(A2)
The formula in C2 counts the characters after TRIM has removed the extra spaces:
=LEN(TRIM(A2))
The formula in D2 shows the difference:
=B2-C2
The formulas are filled down to row 7.
Eevee has one space in front of the name. Snorlax has two spaces after it. Mr. Mime has two spaces between the words. Psyduck has two spaces in front and one after.
You can also count the extra spaces with one formula:
=LEN(A2)-LEN(TRIM(A2))
Note: Text copied from a web page can contain non-breaking spaces. They look like normal spaces, but TRIM does not remove them. If LEN still counts extra characters after TRIM, replace them with normal spaces first, with SUBSTITUTE(A2, CHAR(160), " ").
Check the Length of Codes
LEN is a quick way to check that codes have the right length.
The Poke Mart item codes have 13 characters, like PKB-0200-BALL. Use LEN inside an IF function to find the codes that were typed wrong:
| A | B | |
|---|---|---|
| 1 | Item Code | Check |
| 2 | PKB-0200-BALL | OK |
| 3 | GRB-600-BALL | Check |
| 4 | ULB-1200-BALL | OK |
| 5 | POT-0300-MED | Check |
| 6 | SPT-07000-MEDS | Check |
| 7 | HPT-1200-MEDS | OK |
=IF(LEN(A2)=13, "OK", "Check")
If the length of A2 is 13, the result is OK. If not, the result is Check.
GRB-600-BALL is missing a zero. POT-0300-MED is missing a letter. SPT-07000-MEDS has one digit too many.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Count a Character With SUBSTITUTE
LEN can also count how many times a character appears in a text. Remove the character with SUBSTITUTE, and compare the length before and after.
These shopping lists have one item after the other, separated by commas. Count the items in each list:
| A | B | |
|---|---|---|
| 1 | Shopping List | Items |
| 2 | Poke Ball, Potion | 2 |
| 3 | Great Ball, Super Potion, Revive | 3 |
| 4 | Antidote | 1 |
| 5 | Ultra Ball, Hyper Potion, Revive, Awakening | 4 |
=LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))+1
SUBSTITUTE(A2, ",", "") removes the commas. The difference in length is the number of commas.
A list always has one more item than it has commas, so the formula adds 1.
For A2: Poke Ball, Potion has 17 characters. Without the comma it has 16. 17 - 16 + 1 = 2 items.
If a cell is empty, this formula still returns 1. Use it only on cells that have a list in them.
Related Functions
LEN is often used together with other text functions: TRIM, LEFT, RIGHT, MID and SUBSTITUTE.
