FORMULA & SQL / TEXT
Count the words in a cell.
Tested, not guessed.
The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗
The example rows
| Row | A: Region | B: Rep | C: Amount | D: Order Date | E: Status | F: Customer |
|---|---|---|---|---|---|---|
| 2 | North | Ana | 1,200 | 2026-03-01 | Paid | Jo Smith |
| 3 | South | Ben | 450 | 2026-03-15 | Open | Raj Patel |
| 4 | North | Cy | 300 | 2026-04-02 | Paid | Lee Wong |
| 5 | East | Ana | 980.50 | 2026-04-20 | Paid | Mia Ross |
| 6 | North | Ben | 75 | 2026-05-05 | Open | Jo Smith |
| 7 | West | Dee | 1,640 | 2026-05-18 | Paid | Sam Okoro |
The formula
Excel
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)
If Region (A2) with extra spaces removed equals blank, then 0, otherwise the length of Region (A2) with extra spaces removed minus the length of Region (A2) with extra spaces removed with " " replaced by blank plus 1.
Run on the example rows:
| Row | Result |
|---|---|
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
The SQL
PostgreSQL
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LENGTH(TRIM(region)) - LENGTH(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
Run on the example rows:
| region | rep | amount | order_date | status | customer | word_count |
|---|---|---|---|---|---|---|
| North | Ana | 1200 | 2026-03-01 | Paid | Jo Smith | 1 |
| South | Ben | 450 | 2026-03-15 | Open | Raj Patel | 1 |
| North | Cy | 300 | 2026-04-02 | Paid | Lee Wong | 1 |
| East | Ana | 980.5 | 2026-04-20 | Paid | Mia Ross | 1 |
| North | Ben | 75 | 2026-05-05 | Open | Jo Smith | 1 |
| West | Dee | 1640 | 2026-05-18 | Paid | Sam Okoro | 1 |
Other databases
MySQL
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LENGTH(TRIM(region)) - LENGTH(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
SQL Server
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LEN(TRIM(region)) - LEN(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
SQLite
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LENGTH(TRIM(region)) - LENGTH(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
BigQuery
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LENGTH(TRIM(region)) - LENGTH(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
Snowflake
SELECT *, CASE WHEN TRIM(region) = '' THEN 0 ELSE LENGTH(TRIM(region)) - LENGTH(REPLACE(TRIM(region), ' ', '')) + 1 END AS word_count FROM sales
Your own data
Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗