LEN

How LEN works

LEN takes one piece of text and tells you how many characters it contains. Letters, digits, punctuation, spaces, and line breaks all count as one character each.

If you pass a number, LEN converts it to text first and then counts the digits, so LEN(12345) returns 5. If the cell is blank, LEN returns 0.

The function is most useful nested inside other formulas: comparing LEN of two cells, building a progress message, or feeding the count into IF for conditional logic.

= LEN(text)

Arguments

LEN basic: count characters in a product title

You manage a Shopify store. The platform limits product titles to 60 characters and rejects anything longer at upload time. Before importing 800 SKUs you want a column showing the length of every title so you can spot the offenders.

=LEN(A2)

• •A2 -> the cell holding the product title
• •Returns -> an integer: the character count, spaces included
• •Drag down -> the formula adjusts to A3, A4, ... for every row

LEN inside IF: flag rows that exceed a character limit

Reading the count is fine for ten rows. For eight hundred you want a column that says OK or Too long so you can filter and fix only the bad ones.

=IF(LEN(A2)>60,"Too long","OK")

• •LEN(A2) -> the character count of the title
• •>60 -> the platform's limit
• •"Too long" -> label when the limit is busted
• •"OK" -> label when the title fits

LEN with TRIM: find rows with invisible whitespace

Data pasted from a website or exported from a legacy system often arrives with extra spaces at the start, end, or between words. The text looks fine on screen but breaks lookups silently.

Compare LEN(original) to LEN(TRIM(original)). When they differ, the cell has hidden whitespace.

=LEN(A2)-LEN(TRIM(A2))

• •LEN(A2) -> character count of the original cell
• •LEN(TRIM(A2)) -> character count after collapsing duplicate and leading/trailing spaces
• •Difference -> the number of bonus whitespace characters TRIM removed
• •0 -> means the cell is clean. Anything above 0 means it needs trimming.

LEN inside text: build a progress message

Sometimes the right place for LEN is inside a longer string, building a message your colleagues can read at a glance. The & operator joins the count to surrounding text.

=LEN(A2)&" of 60 characters"

• •LEN(A2) -> the integer count
• •& -> joins the number to the text that follows
• •" of 60 characters" -> the literal suffix; mind the leading space
• •Result -> something like "47 of 60 characters" in plain text

Where people go wrong

1. 01
   Forgetting that spaces count

   LEN counts every character including spaces, tabs, and line breaks. A name like 'Sarah Chen' returns 10, not 9. Off-by-one bugs creep in when authors expect only visible glyphs.

   Fix: Read the count as 'every keystroke', not 'every letter'. If you only want letters, use a SUMPRODUCT pattern or strip with SUBSTITUTE first.
2. 02
   Comparing LEN of formatted and raw values

   LEN works on the underlying value, not what you see on screen. A cell formatted as $1,234.56 stores the number 1234.56, so LEN returns 7 (the digits and decimal point), not 9.

   Fix: Wrap the cell in TEXT() with the format string you want LEN to count: =LEN(TEXT(A2,"$#,##0.00")) returns the displayed length.
3. 03
   Trying to LEN a whole range at once

   Writing =LEN(A:A) returns the length of the first cell only in classic Excel. Beginners expect a total or a list and get one number.

   Fix: Wrap in SUMPRODUCT for a total: =SUMPRODUCT(LEN(A2:A100)). In Excel 365 with dynamic arrays you can pass a range to LEN directly and it spills the results.
4. 04
   Forgetting LENB exists

   LEN counts characters, but characters in non-Latin alphabets and emoji can take 2 or 4 bytes. If your downstream system limits by bytes (some legacy databases do), LEN gives the wrong answer.

   Fix: Use LENB instead. LEN counts what humans count; LENB counts what storage counts. Pick the one that matches the limit you're checking against.

Notes

• LEN counts every character including spaces, line breaks, and tabs. There is no option to ignore whitespace.
• Numbers are converted to text before counting. LEN(12345) returns 5. LEN(0.5) returns 3 because 0, ., and 5 are all characters.
• An empty cell returns 0. A cell containing only a space returns 1.
• LEN works on the underlying value, not the formatted display. A date like 5/3/2026 stored as a serial number returns 5 because the underlying value is 46145.
• Wrap in TEXT() to count the formatted display: =LEN(TEXT(A2,"yyyy-mm-dd")) returns 10.
• In Excel 365 LEN accepts a range directly and spills the results down or across.
• For multi-byte character handling (Japanese, Chinese, emoji), use LENB which counts bytes instead of characters.

What to learn next

Now prove it