Skip to main content
Excel Cookbook
ExcelLENtext functionsdata validationcharacter count

The Problem: How Long Is This Text?

You're importing customer data from a web form, and your database has a 50-character limit for names. Some entries are too long and will cause import errors. Or perhaps you need to verify that phone numbers are exactly 10 digits, or that product codes follow a specific format. Manually counting characters? That's medieval!

The LEN function is your digital ruler — instantly measuring the length of any text.

The Ingredients: Understanding LEN's Setup

LEN is one of Excel's simplest functions. It counts every character in a cell, including spaces and special characters.

=LEN(text)

Parameter Description
text The text string or cell reference to measure.

Key detail: Spaces count! "Hello World" has a LEN of 11, not 10.

The Recipe (Step-by-Step): Validating Data Length

Scenario: Check if customer names exceed 50 characters.

A (Name) B (Length) C (Status)
John Smith 10 OK
Alexandra Konstantinopolous 29 OK
  1. In cell B2, type: =LEN(A2) → Returns the character count.
  2. In cell C2, combine with IF: =IF(LEN(A2)>50, "TOO LONG", "OK")
  3. Drag down to validate all entries.

Advanced Recipes

Counting Words (Approximate)

LEN can help estimate word count by counting spaces:

=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1

This counts words by subtracting the length without spaces from the total length, then adding 1.

Detecting Extra Spaces

Find cells with leading/trailing spaces:

=IF(LEN(A2) <> LEN(TRIM(A2)), "Has extra spaces", "Clean")

Pro Tips: Sharpen Your Skills

  • LENB for bytes: For double-byte character languages (Chinese, Japanese, Korean), use LENB() to count bytes instead of characters.
  • Combine with Data Validation: In Data Validation, use custom formula =LEN(A1)<=50 to prevent users from entering text that's too long.
  • Empty cells: LEN("") returns 0. Use this to check for empty cells: =IF(LEN(A2)=0, "Empty", "Has data").

Troubleshooting: Common Pitfalls

  • Numbers vs text: LEN(12345) returns 5 — Excel converts the number to text first.
  • Hidden characters: If LEN returns more than expected, use =CODE(MID(A2, n, 1)) to inspect individual characters. Non-printing characters (tabs, line breaks) are counted.
  • Dates: LEN on a date cell returns the length of the underlying serial number, not the displayed date.

Related Recipes