Text

LEN Function in Excel

Returns the number of characters in a text string.

Syntax

  • =LEN(text)

Arguments

  • text (required): The text to measure

Examples

  • =LEN("Hello") - Count characters in Hello - Result: 5
  • =LEN(A1) - Count characters in cell - Result: Character count
  • =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 - Count words - Result: Word count

Excel LEN function documentation

  • LEN returns the number of characters in a text string — including spaces and symbols.
  • Syntax: =LEN(text). Counts characters, not byte length; works with Unicode in modern Excel.
  • Use for validation rules, password length checks, and audit columns before imports.
  • Related: [LENB](/functions/lenb/) for double-byte languages, [TRIM](/functions/trim/), [LEFT](/functions/left/).

LEN syntax and common uses

  • Blank cell LEN is 0. Cell with formula ="" has LEN 0 for display but cell may not be blank to COUNTA.
  • LEN counts digits in a numeric string — LEN("00123") is 5.
  • Compare LEN before/after TRIM: =LEN(A2)-LEN(TRIM(A2)) finds extra padding.
  • Data validation: =LEN(B2)<=10 for max length constraint in helper column.

Step-by-step: flag IDs with wrong length

  • Step 1 — Product codes must be exactly 8 characters.
  • Step 2 — =LEN(A2) in helper column.
  • Step 3 — Flag: =IF(LEN(A2)<>8, "Bad length", "OK").
  • Step 4 — Filter helper to "Bad length" and fix source data.
  • Step 5 — Combine with ISNUMBER for numeric-only codes using nested checks.

LEN vs LENB vs string width

  • LEN — character count for most English/Latin text workflows.
  • LENB — byte count in double-byte character set languages.
  • Display width in columns differs from LEN — use LEN for formula logic only.
  • Pair LEN with [MID](/functions/mid/) and [RIGHT](/functions/right/) for dynamic extraction.

Worked examples to copy

  • Password length: =LEN(D2)>=12 for policy check.
  • Trim audit: =LEN(A2)-LEN(TRIM(A2)).
  • Extract rest of string: =RIGHT(A2, LEN(A2)-FIND(".", A2)).
  • Count characters excluding spaces: =LEN(SUBSTITUTE(A2," ","")).

People also ask

  • Does LEN count spaces? — Yes. All characters count including spaces.
  • LEN on number? — Coerces to text; LEN(12345) is 5.
  • LEN vs COUNT characters? — LEN is the Excel function name.
  • Why LEN differs from column width? — Column width is display formatting, not LEN.

Common errors

  • Counts spaces and special characters
  • Returns 0 for empty cells

Use cases

  • Data validation
  • Text analysis
  • Dynamic formulas

Frequently asked questions

  • Does LEN count spaces? Yes, LEN counts all characters including spaces. =LEN("A B") returns 3. To count without spaces: =LEN(SUBSTITUTE(A1," ","")).
  • How do I count words in a cell? Use =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1. TRIM removes extra spaces, then count spaces and add 1. Returns 1 for single word, 0 for empty.
  • What is the difference between LEN and LENB? LEN counts characters. LENB counts bytes (relevant for double-byte languages like Chinese, Japanese, Korean where one character = 2 bytes). For English text, they're the same.
  • How do I validate text length? Use in Data Validation or conditional formatting: =LEN(A1)<=10 checks if text is 10 characters or less. =AND(LEN(A1)>=5, LEN(A1)<=10) for range.
  • Can LEN count specific characters? Indirectly: =LEN(A1)-LEN(SUBSTITUTE(A1,"a","")) counts occurrences of 'a'. The difference in length equals the count of removed characters.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use LEN

  • Data validation — common Text scenario for LEN.
  • Text analysis — common Text scenario for LEN.
  • Dynamic formulas — common Text scenario for LEN.

LEN in the Text category

  • Browse all Text functions at /categories/text/ for related formulas.
  • LEN syntax: =LEN(text)
  • text (required): The text to measure
  • Confirm LEN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm LEN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • LENB (/functions/lenb/): Returns the number of bytes used to represent the characters in a text string.
  • LEFT (/functions/left/): Returns the specified number of characters from the start of a text string.
  • RIGHT (/functions/right/): Returns the specified number of characters from the end of a text string.
  • MID (/functions/mid/): Returns a specific number of characters from a text string, starting at a specified position.
  • FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).

Errors to watch for

  • Counts spaces and special characters — review causes on linked error pages in the directory.
  • Returns 0 for empty cells — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter LEN on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

LEN worked examples to copy

  • =LEN("Hello") — Count characters in Hello. Expected result: 5.
  • =LEN(A1) — Count characters in cell. Expected result: Character count.
  • =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1 — Count words. Expected result: Word count.

LEN reference summary for crawlers and offline review

  • LEN belongs to the Text category in Excel. Returns the number of characters in a text string.
  • Full syntax: =LEN(text). Open /functions/len/ for parameters, FAQs, and related pages.
  • Common mistakes: Counts spaces and special characters; Returns 0 for empty cells
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.