# How to find values with the wrong number of characters in Excel

Compare the length with the expected one: =IF(LEN(A2)=$E$1,"OK","Check") flags every value that is too short or too long.

**Excel:** `=IF(LEN(A2)=$E$1,"OK","Check")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| ZIP code | Length check |  | Expected length | 5 |
| 55401 |  |  |  |  |
| 5540 |  |  |  |  |
| 55102-1234 |  |  |  |  |
| 53703 |  |  |  |  |
| 581020 |  |  |  |  |
| 02134 |  |  |  |  |

Formula in B2, filled to B7: `=IF(LEN(A2)=$E$1,"OK","Check")`

Result: OK; Check; Check; OK; Check; OK

## How it works

LEN counts the characters, and comparing it with the expected length in E1 catches typos like a missing digit, an extra digit or an unexpected ZIP+4. Keeping the length in a cell lets you reuse the check for phone numbers, IDs or account codes.

## Other ways

- `=LEN(A2)<>$E$1`: TRUE for the rows to fix, ready to filter or to use in conditional formatting. (TRUE and FALSE instead of labels.)

## Common mistakes

- ZIP codes stored as numbers lose leading zeros, so 02134 becomes 2134 and fails the check. Store them as text.
- Spaces count. Trim imported values before checking: `LEN(TRIM(A2))`.

## Questions

**How do I count the values with the wrong length?**

Use `=SUMPRODUCT(--(LEN(A2:A7)<>$E$1))`.

**How do I stop wrong lengths being entered?**

Use Data Validation > Text length, equal to 5.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/find-values-with-the-wrong-number-of-characters