Text & Data Cleaning
Count Total Words Across a Multi-Cell Range Safely (Blank & Space Guards)
Count total space-delimited words across an entire column or block of notes—without inflating counts on blank cells, double spaces, or space-only entries—using TRIM, TEXTJOIN, LEN, and SUBSTITUTE.
Why naive LEN - LEN(SUBSTITUTE) + 1 overcounts blank rows
The classic single-cell word formula LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1 adds +1 to every row—meaning 10 blank or space-only cells add 10 phantom words unless explicitly guarded!
Joining the range first with TRIM(TEXTJOIN(" ", TRUE, A2:A6)) solves blank cells, leading/trailing spaces, and consecutive spaces in one pass: empty cells are skipped, extra spaces collapse to single spaces, and testing LEN(joined) = 0 returns 0 when an entire range is empty. In Arte Sheets, you can also attach .txt or .csv notes (up to 2 MB) as safe non-executing excerpts in the AI Assistant to preview row structure before pasting into your sheet.
| Row Input | Trimmed String | Guarded Row Word Count |
|---|---|---|
| " Quarterly revenue audit " | "Quarterly revenue audit" | 3 |
| "Approved" | "Approved" | 1 |
| " " (spaces only) | "" | 0 |
| Blank cell | "" | 0 |
| "Ready for board sign-off" | "Ready for board sign-off" | 4 (Range Total = 8) |
Single-Cell Range Word Count via TEXTJOIN + TRIM + LET
Joins all non-empty cells in A2:A6, normalizes internal whitespace with TRIM, and returns 0 cleanly when the entire block holds no words.
=LET(joined, TRIM(TEXTJOIN(" ", TRUE, A2:A6)), IF(LEN(joined) = 0, 0, LEN(joined) - LEN(SUBSTITUTE(joined, " ", "")) + 1))Blank-Safe Per-Row Word Count Helper
Guards both ISBLANK(A2) and LEN(TRIM(A2)) = 0 so empty and space-only cells evaluate strictly to 0.
=IF(ISBLANK(A2), 0, IF(LEN(TRIM(A2)) = 0, 0, LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1))Try the example
Use the original values or change them to test your understanding.