Excel guide · 3 min read

How to count characters in a cell in Excel

Q: How do I count a specific letter in a cell?

Use =LEN(A2)-LEN(SUBSTITUTE(A2,"e","")). It is case-sensitive, so wrap A2 in UPPER and search for the capital letter to count both cases.

Q: How do I count characters in a range of cells?

Use =SUMPRODUCT(LEN(A2:A20)). It adds the length of every cell in the range and works in all versions.

Quick answerUse =LEN(A2). It returns the number of characters in A2, including spaces, punctuation and digits. To leave spaces out, use =LEN(SUBSTITUTE(A2," ","")).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Ad headline (max 30)	Tags
2	Spring sale: 20% off all shoes	shoes, sale, spring
3	Free returns on every order	returns, service
4	New season trainers now in stock	trainers, new, stock, mens
5	Last chance: sale ends Sunday	sale

Count every character with LEN

LEN takes one argument and counts everything in it: letters, digits, spaces, punctuation and symbols. "Spring sale: 20% off all shoes" is 30 characters, exactly at the limit for a Google Ads headline.

Fill it down next to your list to see the length of every row at once. Then sort by that column to find the longest entries.

=LEN(A2)

Result: 30

Returns 30.

Flag text that is over a limit

Character limits are the main reason people count characters: ad headlines, SMS messages, product titles, database fields. Compare LEN to the limit inside IF and show how much needs cutting.

To stop people typing too much in the first place, use Data > Data Validation, set Allow to Text length, choose less than or equal to, and enter the limit.

=IF(LEN(A4)>30,"Too long by "&LEN(A4)-30,"OK")

Result: Too long by 2

Returns "Too long by 2". The headline is 32 characters.

Count characters without spaces

SUBSTITUTE swaps every space for nothing, and LEN counts what is left. Use this when a limit or a fee is based on visible characters only.

=LEN(SUBSTITUTE(A3," ",""))

Result: 23

Returns 23. The headline is 27 characters including its 4 spaces.

Count how many times one character appears

Take the full length and subtract the length with that character removed. The difference is how many there were. Here it counts commas, and the number of tags is one more than the number of commas.

SUBSTITUTE is case-sensitive, so counting "s" misses "S". To count a letter in any case, make the text one case first: =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"S","")).

=LEN(B4)-LEN(SUBSTITUTE(B4,",",""))

Result: 3

Returns 3 commas, so there are 4 tags.

Count a word or longer string

The same trick works for a word, but removing "sale" takes away 4 characters per match, not 1. Divide by the length of the word to get the number of matches.

It counts the string anywhere, including inside longer words, so "sale" would also match "wholesale".

=(LEN(A5)-LEN(SUBSTITUTE(A5,"sale","")))/LEN("sale")

Result: 1

Returns 1. "sale" appears once in "Last chance: sale ends Sunday".

Count characters in a whole range

LEN on a range returns one length per cell. SUMPRODUCT adds them up in every version of Excel, without Ctrl+Shift+Enter. In Excel 365 =SUM(LEN(A2:A5)) works too.

=SUMPRODUCT(LEN(A2:A5))

Result: 118

Returns 118, the total characters across all four headlines.

Common mistakes

Hidden spaces make the count too high

A trailing space counts as a character you cannot see. Check with =LEN(TRIM(A2)). If it is lower, clean the cell with TRIM.

Counting a formatted number

LEN counts the stored value, not what the cell shows. 1234.5 formatted as $1,234.50 has a LEN of 6, and a date returns the length of its serial number, such as 5. Use LEN(TEXT(A2,"$#,##0.00")) to count the displayed version.

Line breaks are counted too

Each Alt+Enter line break inside a cell is one character. Remove them first with SUBSTITUTE(A2,CHAR(10),"") if they should not count.

FAQ

Does LEN count spaces in Excel?

Yes. LEN counts every character, including spaces at the start, middle and end. Use LEN(SUBSTITUTE(A2," ","")) to count without spaces.

How do I count a specific letter in a cell?

Use =LEN(A2)-LEN(SUBSTITUTE(A2,"e","")). It is case-sensitive, so wrap A2 in UPPER and search for the capital letter to count both cases.

How do I count characters in a range of cells?

Use =SUMPRODUCT(LEN(A2:A20)). It adds the length of every cell in the range and works in all versions.

Why is LEN higher than the number of characters I can see?

The cell has characters you cannot see: trailing spaces, a line break, or a non-breaking space from a web page. Compare LEN(A2) with LEN(TRIM(A2)), and click into the cell to look for a second line.

Is there a character limit for an Excel cell?

A cell can hold 32,767 characters, but only the first 1,024 are shown in the cell itself. All of them are visible in the formula bar.

Functions used

Related guides

Updated November 2, 2026