OmniDocExcel Formula for Counting Words in a Cell
Use a simple Excel formula with LEN, TRIM and SUBSTITUTE to count words in a cell, handle empty cells, and check results for punctuation and line breaks.
If you searched for an excel formula for no of words, you probably have a column of text, such as survey answers, product descriptions or abstracts, and need a word count for each cell. Excel has no built-in function that counts words, so you have to build one from text functions. The result is a formula you can copy down a column in a few minutes.
This guide shows you that formula, explains how each part works, and covers the cases that throw off the count: empty cells, extra spaces, line breaks and punctuation. You will also learn how to total the counts for a whole list. The method uses only standard spreadsheet functions, so it works in any common spreadsheet program, and you do not need to install anything.
If your text starts as a PDF table, you can move it into a spreadsheet first and then apply the formula. The steps below cover that as well.
How do you count words in an Excel cell? The short answer
Count the spaces in the cell, subtract that from the length of the trimmed text, and add 1 for the last word. Wrap the formula in an IF so that empty cells return 0.
| Your situation | What to do | Why |
|---|---|---|
| Text is in cell A1 with normal single spaces | Use =IF(TRIM(A1)="",0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1) | The number of spaces plus one equals the number of words |
| Some cells are empty | Keep the IF wrapper | Without it, an empty cell shows 1 |
| Cells contain line breaks | Replace line breaks with spaces inside the formula | Excel does not treat a line break as a space |
| Text has irregular spacing | Keep TRIM in the formula | TRIM removes extra spaces so they are not counted as words |
| You need one total for a list | Fill the formula down, then use SUM | SUM adds the per-cell counts into one number |
Before you start
Check these items before you copy the formula into your sheet. Each one changes the result.
- Confirm the text is in one cell per item. The formula counts words in a single cell. If a paragraph is split across several cells, the count will be too low for each one.
- Choose an empty column for the formula. Put the formula in a column next to your text, such as B if the text is in column A. Writing over the text would destroy it.
- Note how words are separated. The formula counts spaces. Words separated by a tab, a comma with no space or a line break will not be counted correctly unless you adjust the formula.
- Decide whether you need a total or a count per cell. Per-cell counts need one formula per row. A total needs those counts plus a SUM formula.
- Get the text into a spreadsheet if it lives in a PDF. Copying a table from a PDF can leave words run together or split oddly. Check a few cells against the original before you trust the counts.
Counting words in a cell with a formula, step by step
- Put your text in a cell. For this example, type or paste the text into cell A1. The formula will count words in that cell only.
- Click an empty cell next to it. Select B1, the cell where the word count will appear.
- Enter the base formula. Type
=IF(TRIM(A1)="",0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)and press Enter. The result is the number of words in A1. - Read how the formula works. TRIM removes leading, trailing and doubled spaces. LEN counts the characters in the trimmed text. SUBSTITUTE removes every space, and LEN counts what is left. The difference between the two lengths is the number of spaces, and adding 1 gives the number of words.
- Handle line breaks if your text has them. Excel stores a line break as the character code 10, which the formula does not see as a space. Replace A1 inside the formula with
SUBSTITUTE(A1,CHAR(10)," ")so the line breaks become spaces before counting. The full version reads=IF(TRIM(SUBSTITUTE(A1,CHAR(10)," "))="",0,LEN(TRIM(SUBSTITUTE(A1,CHAR(10)," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,CHAR(10)," "))," ",""))+1). - Fill the formula down the column. Drag the fill handle at the bottom right of B1 down to the last row of your text. Each row now counts its own cell.
- Add a total. In a cell below the list, type
=SUM(B1:B100), adjusting the range to match your rows. This gives the total number of words across all the cells. - Check a few rows by hand. Count the words in three or four cells yourself and compare them with column B. If they match, the formula is working on your data.
Check the result
Once the counts appear, compare them against the text. Use this table to find and fix the usual problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Count matches a manual count | Count three or four cells by eye and compare | Look for the cause in the rows below |
| Empty cells show 0 | Find a blank row in your text column | If it shows 1, the IF wrapper is missing; re-enter the formula |
| Line breaks are counted | Check a cell that spans several lines | If the count is too low, add the CHAR(10) replacement |
| Hyphenated words | Find a cell with a word like "well-known" | It counts as one word, which is usually the intended result. If you need hyphenated parts counted separately, you need a different method |
| Non-breaking spaces | Look for cells where the count is too low and words appear to run together | Text copied from web pages or PDFs often contains non-breaking spaces; replace CHAR(160) with a regular space inside the formula |
| Total is right | Compare SUM with the sum of the rows you expect | Check that the SUM range covers every row of counts |
Check any formula result carefully when the count matters, such as for a word limit in a submission. The formula counts spaces, so it cannot tell whether a string of characters is a real word, a code or a typo.
If your text came from a PDF, the export can add or remove spaces between words. Compare a few cells against the original PDF before you rely on the totals. The PDF editor in the OmniDoc desktop client can export a PDF to Excel, which gives you a spreadsheet to work in, but the export still depends on how the original PDF was built.
❓ FAQ
1. Does the formula count numbers as words?
Yes. Any group of characters separated by spaces counts as one word, including numbers, codes and symbols that stand alone. If you need to exclude them, you would need a more advanced formula or a filter on the text first.
2. What if I want to count characters instead of words?
Use LEN on its own. =LEN(A1) returns the number of characters, including spaces. Use =LEN(TRIM(A1)) if you want to ignore leading and trailing spaces.
3. Why did my count change after I edited the text?
The formula updates automatically when the cell changes. If the count seems stale, check that the calculation mode is set to automatic and that the formula still points to the correct cell.
4. Can I count words across a whole range in one formula?
Not with the single-cell formula above. Fill it down the column to get a count per row, then use SUM to get the total, as described in the steps.
5. Does the formula work for text in languages other than English?
It works for any language that separates words with spaces. Languages that do not use spaces between words, such as Chinese or Japanese, cannot be counted this way, because the formula only sees spaces.
📝 Summary
A word count in Excel comes from counting spaces, using LEN, TRIM and SUBSTITUTE, then adding 1 for the last word. Wrap the formula in IF to handle empty cells, replace line breaks with spaces when your text contains them, and use SUM for a total. Compare a few results with a manual count before you rely on the numbers.
