Skip to content

Excel function · Text

Excel LEN function

LEN counts the characters in a text, including spaces and punctuation. It is used to check code and barcode lengths and to spot extra spaces.

In Turkish Excel: UZUNLUK

What it does

LEN takes a single argument and counts the characters in it. Letters, digits, spaces, dots and hyphens each count as one character.

On its own it is most useful for data checks: together with IF it tells you whether a barcode really has 13 digits or a description stays within a character limit. It is also a common helper for other text functions; when RIGHT or MID needs a part of varying length, LEN calculates the number of characters.

The usual way to count how many times a character appears is to subtract the length of the text without that character from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")).

Syntax

English Excel=LEN(text)
Turkish Excel=UZUNLUK(metin)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
textRequiredThe text or cell whose characters are counted. An empty cell returns 0. For numbers, the stored value is counted, not the formatting: 1250 shown as 1,250.00 returns 4.

Examples

1Check barcode lengths

Every barcode should have 13 digits. Column C counts the length of each barcode and column D flags the ones that are not 13.

C2fx=LEN(B2)
Check barcode lengths: example table
ABCD
1ProductBarcodeLengthStatus
2Copper cable501234567890013OK
3Grounded socket50123456789112Check
4Circuit breaker501234567892413OK
5Cable duct5012345678931114Check
6Light switch501234567894813OK
  • C2 (filled down to C6)=LEN(B2)
  • D2 (filled down to D6)=IF(C2=13,"OK","Check")

The grounded socket's barcode has 12 characters and the cable duct's has 14; both show Check in column D.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Catch invisible spaces

The names were copied from another program and contain extra spaces that you cannot see. Column B returns the difference between the length of the text and the length of its trimmed version.

B2fx=LEN(A2)-LEN(TRIM(A2))
Catch invisible spaces: example table
AB
1Full NameExtra Spaces
2Emma Brown0
3Lucas Gray1
4 Ava Thompson1
5Noah Clark 2
  • B2 (filled down to B5)=LEN(A2)-LEN(TRIM(A2))

The first row is clean: 0. The others have 1, 1 and 2 extra spaces; clean them with TRIM before matching them against another list.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Count the products listed in a cell

The product codes of each order are written in one cell, separated by commas. The number of commas is the length of the text minus the length without commas; the product count is one more.

C2fx=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
Count the products listed in a cell: example table
ABC
1OrderProduct CodesProducts
2S-1001KBL-1525,PRZ-0210,SGT-00163
3S-1002KNL-20401
4S-1003PRZ-0210,ANH-03002
5S-1004KBL-1525,KBL-1540,KNL-2040,SGT-00164
  • C2 (filled down to C5)=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

S-1001 has 3 products, the single-item S-1002 has 1 and S-1004 has 4.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Larger than expected

Cause: The text has extra spaces at the start, at the end or between words. They are invisible but they count.

Fix: Compare with =LEN(TRIM(A2)); if they differ, clean the data with TRIM.

Missing digits

Cause: For numbers, the stored value is counted, not what you see. Leading zeros added by a number format such as 00125 are not counted, so the result is 3.

Fix: To count what is shown, convert it to text first: =LEN(TEXT(A2,"00000")).

Good to know

  • To find codes of the wrong length in a column, add a LEN column and filter or sort it; the faulty rows gather at the top or bottom.
  • Store long barcodes and reference numbers as text, not numbers; Excel turns the digits after the 15th into zeros.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Write "Check" in a Status column for products whose barcode is not 13 digits long

Convert to table

Bring your product list into the tool and type this. Nexell adds the Status column with real Excel formulas and the preview shows which rows are flagged; in the downloaded file, the flag disappears once you fix the barcode.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I count the words in a cell?

Count the spaces and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1. TRIM removes extra spaces, so double spaces do not spoil the count. An empty cell returns 1; avoid that with =IF(A2="",0,…).

How do I count how many times a letter appears in a cell?

Subtract the length of the text without that letter from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). SUBSTITUTE is case-sensitive, so "A" is not counted.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free