How to Use the LEN Function in Excel
LEN counts characters in Excel. Use it to audit invoice IDs, product codes, imported text, and fields that need a fixed length.
LEN counts how many characters are in a cell. It counts letters, numbers, spaces, punctuation, and hidden extra spaces that are easy to miss by eye.
That makes it useful for checking IDs, cleaning imported data, validating codes, and finding text fields that are too long for a system upload.
The syntax
=LEN(text)- text - the cell or text value you want to count
If A2 contains INV-1048, this formula returns 8:
=LEN(A2)Example: check invoice IDs before upload
Suppose invoice IDs must be exactly eight characters long. Column A contains the IDs you plan to upload.
Step 1. Count the characters
In B2, enter:
=LEN(A2)Fill the formula down the column.
Step 2. Flag the rows that need review
In C2, enter:
=IF(LEN(A2)=8, "OK", "Check")Rows marked Check may have missing digits, extra spaces, or a copied value that does not match the required format.
Step 3. Clean before counting if spaces are not meaningful
If leading or trailing spaces should not count, wrap the value in TRIM:
=LEN(TRIM(A2))That gives you the length of the cleaned value instead of the raw cell.
TIP
LEN(A2) and LEN(TRIM(A2)) side by side when you suspect hidden spaces. If the counts differ, the cell needs cleanup.LEN counts spaces
This surprises people because spaces are hard to see. These two values are different to LEN:
Acme
Acme
The second value has a trailing space, so LEN returns one extra character.
That matters when you are debugging failed lookups. Two customer names can look the same on screen while one carries a hidden trailing space from an export.
Common uses for LEN
| Use case | Formula pattern |
|---|---|
| Check fixed-length IDs | =LEN(A2)=8 |
| Find values with hidden spaces | =LEN(A2)-LEN(TRIM(A2)) |
| Flag long notes | =IF(LEN(A2)>250, "Too long", "OK") |
| Count cleaned text | =LEN(TRIM(A2)) |
LEN vs counting words
LEN counts characters, not words. If a cell contains a sentence, LEN tells you how long the sentence is, including spaces and punctuation.
For most spreadsheet work, that is enough. Use it to validate text fields, not to analyze writing style.
The Griddy way
Length checks are easy for one column and annoying across a whole import.
"Flag invoice IDs that are not exactly eight characters and show which ones have hidden spaces"
Griddy can add the LEN checks, compare raw and cleaned values, and mark the rows that need review before the file goes into another system.
Validate before export
A length check is most useful when it is explicit about what “valid” means. For a fixed-width identifier, compare LEN with the required count and return a clear status:
=IF(LEN(A2)=8,"OK","Review")If spaces may have been pasted around the value, check both the raw and cleaned lengths rather than silently changing the source:
=LEN(A2)-LEN(TRIM(A2))That second formula shows how many ordinary spaces were removed. LEN counts spaces, but it does not tell you whether a character is visually obvious. Keep the original column, create a cleaned or validation column, and review exceptions before importing the file. This makes the audit reversible and prevents a formula from hiding a bad identifier.
Sources
Do this with AI
Turn the instructions into a finished spreadsheet.
Tell Griddy what you need in plain English, then inspect and keep editing the result in the browser, Excel, or Google Sheets.