Excel LEN function: count characters in a cell

Column G holds each rep's contact name, typed messily on purpose: "  dana ruiz", "LUIS ortega ", "priya   shah". That's what exported lists look like.

Syntax

=LEN(text)

Examples

• =LEN(A2) returns 4.
• =LEN(G3) returns 12 for "LUIS ortega ", because the trailing space counts.
• =LEN(TRIM(G3)) returns 11.

Find cells with extra spaces

=LEN(G2)<>LEN(TRIM(G2)) returns TRUE when a cell has extra spaces. Use it with conditional formatting to highlight the messy rows.

Check ID and phone lengths

Data entry tests often ask you to flag wrong-length entries. =IF(LEN(A2)=10,"OK","Check") flags anything that isn't 10 characters, like a phone number missing a digit.

Count a character

To count how many times a letter appears, remove it and compare lengths: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")) counts the lowercase a's in a cell.

Watch out for

• Numbers are counted as displayed in the formula bar, not with formatting. =LEN(1250) is 4, even if the cell shows $1,250.00.
• Non-breaking spaces from websites count as characters but TRIM won't remove them. See cleaning text.