Excel Len Function

What this does

LEN returns how many characters a value contains. Spaces count, which is the property that makes it the standard trailing-space detector: comparing =LEN(A2) against =LEN(TRIM(A2)) reveals padding that is invisible on screen and silently breaks every lookup against that column. Its second common use is validation — a postcode, a VAT number or a product code with a fixed length can be checked in one formula. Numbers are counted as their displayed digits, so =LEN(1234) returns 4, and formatting is ignored: a value shown as 1,234.00 still counts its underlying digits. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “excel len function”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a text operation that turns messy entries into clean, usable data, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

Finding invisible padding that breaks a VLOOKUP: =LEN(A2)-LEN(TRIM(A2)) returns how many stray spaces a cell carries, and anything above 0 explains the failed match. Validating a 13-character product code: =IF(LEN(B2)<>13, "Bad code", "OK"). Counting how often a character appears: =LEN(A2)-LEN(SUBSTITUTE(A2, ",", "")) returns the number of commas. LEN is the fastest way to prove a lookup is failing on invisible whitespace rather than on the data itself. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Treat “excel len function” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Expecting LEN to ignore spaces — it counts them, which is exactly why it finds padding.
• Using it on a formatted number and expecting the formatted length; LEN sees the underlying value, not the display.
• Overlooking non-breaking spaces from web pastes, which TRIM does not remove — SUBSTITUTE with CHAR(160) does.

Frequently asked questions