Count Characters and Words in a Cell in Excel

To count characters in Excel, use LEN: =LEN(A2) returns the number of characters in A2, spaces and punctuation included. With LEN, TRIM and SUBSTITUTE you can also count one specific character, count words and total the characters in a whole column. This guide is for anyone checking character limits, auditing data or answering a text question in an Excel test.

How to count characters in a cell with LEN#

LEN returns the length of the text in a cell, counting every character.

=LEN(text)

Here are product titles that must fit a 40-character limit:

In B2, type =LEN(A2) and fill down. The first title has 42 characters, so it is too long. To flag it automatically, use =IF(LEN(A2)>40,"Too long","OK").

LEN counts:

• letters, digits and symbols, including the dash in "USB-C";
• every space, including invisible ones at the start or end;
• line breaks inside a cell (each counts as one character).

Why LEN gives a surprising number#

LEN measures the value Excel stores, not what the cell shows. Three cases catch people out:

Number formats such as commas and decimals aren't part of the value, so LEN ignores them. Dates are stored as serial numbers, so LEN counts the digits of the serial. To measure what you see, convert it first with TEXT, for example =LEN(TEXT(A2,"#,##0.00")).

Hidden spaces are the most common cause of a "wrong" length. Wrap the cell in TRIM to ignore them: =LEN(TRIM(A2)). The TRIM function guide explains exactly which spaces it removes.

How to count characters without spaces#

Delete the spaces with SUBSTITUTE, then measure what is left:

=LEN(SUBSTITUTE(A2," ",""))

SUBSTITUTE swaps every space for nothing (""), and LEN counts the rest. For "USB-C hub, 7 ports" the result is 15 instead of 18.

How to count a specific character in a cell#

Subtract the length without the character from the full length. The difference is how many times it appears:

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

For "A5 notebook, dotted, 120 pages" the result is 2, because removing both commas makes the text 2 characters shorter.

This is useful for checking data. If every address should contain exactly two commas, a result of 1 or 3 points to a bad row. Adding 1 to the comma count also tells you how many items a comma-separated list holds.

SUBSTITUTE is case-sensitive, so =LEN(A2)-LEN(SUBSTITUTE(A2,"e","")) misses capital E. To count both, make the text lowercase first:

=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"e",""))

For "Excel" this returns 2. When the capital letters matter, compare with EXACT for case-sensitive checks.

How to count how many times a word appears#

Remove the word, measure how much shorter the text got, then divide by the word's length:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"cat","")))/LEN("cat")

For "cat dog cat", removing "cat" takes away 6 characters, and 6 ÷ 3 = 2. Put the word in a cell, such as E1, so you can change it: =(LEN(A2)-LEN(SUBSTITUTE(A2,E1,"")))/LEN(E1).

One caution: this counts the letters anywhere, so "cat" inside "category" counts too. To check whether a word appears at all, FIND and SEARCH are simpler.

How to count words in a cell#

Excel has no word count function, so you count the spaces between words and add 1:

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

How it works, step by step:

1. TRIM(A2) removes spaces at the start and end and squeezes double spaces to one, so each gap between words is exactly one space.
2. LEN(TRIM(A2)) is the length with spaces.
3. LEN(SUBSTITUTE(TRIM(A2)," ","")) is the length without spaces.
4. The difference is the number of spaces. Words are always one more than the gaps between them.

For " the quick brown fox " the result is 4. Without TRIM, the extra spaces would give 7.

A blank cell would return 1, because the formula always adds 1. Guard against that with IF:

=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)

In Microsoft 365 or Excel 2024 there is a shorter option: =COUNTA(TEXTSPLIT(TRIM(A2)," ")) splits the text at each space and counts the pieces. See our guide to splitting text with TEXTSPLIT.

How to count characters in a range#

LEN works on one cell, but SUMPRODUCT can add up the lengths of a whole range in one formula:

=SUMPRODUCT(LEN(A2:A5))

SUMPRODUCT handles the range without needing Ctrl+Shift+Enter, so this works in every version of Excel. In Microsoft 365, Excel 2021 and Excel 2024, =SUM(LEN(A2:A5)) works too.

The same idea counts a character across a range: =SUMPRODUCT(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,",",""))) returns the total number of commas in A2:A5.

How to stop entries that are too long#

LEN can also prevent the problem rather than report it. Data validation has a built-in length rule:

1. Select the cells, such as A2:A100.
2. Choose Data > Data Validation.
3. Set Allow to Text length, Data to less than or equal to, and Maximum to 40.
4. Click OK.

Excel now rejects anything longer than 40 characters as it is typed. It doesn't check values that were already there, so keep the =LEN(A2) column for existing data, or use conditional formatting with the rule =LEN($A2)>40 to colour rows that are over the limit.

Using the count with LEFT, RIGHT and MID#

Counting characters is often the first step of splitting text. When IDs have different lengths, LEN tells RIGHT how many characters to take: =RIGHT(A2,LEN(A2)-4) returns everything after a 4-character prefix like "EMP-". The LEFT function guide and our article on LEFT, RIGHT and MID cover that pattern, and splitting first and last names puts it to work on a real list.

If you'd rather see lengths as bars than numbers, REPT draws simple in-cell bar charts from any count.

Practise it#

Try the exercise on this page to measure product titles, then open the LEFT, RIGHT, MID and LEN lesson, where you use LEN to split IDs of different lengths in a live spreadsheet with instant feedback.