Define the unit before the formula
For short descriptions, one unit per space can be useful if applied consistently. Declare that numbers and isolated symbols count under this rule; it does not parse grammar. If an assignment mandates another counter, use that method for acceptance. A word counter or Writer may be simpler for one prose extract, while Calc helps compare many independent records and identify unusual rows.
Prepare a normalized helper column
Keep the source in A2 and enter =TRIM(A2) in B2. The documented function removes edge spaces and leaves single spaces between words. Inspect B2 while building the calculation; this prepared text must support the one-separator assumption. Do not overwrite A2. Technical spacing may be meaningful, so measurement preparation need not mean changing the text intended for publication.
Count separators and handle empty text
In C2 use =IF(B2="";0;LEN(B2)-LEN(SUBSTITUTE(B2;" ";""))+1). Removing ordinary spaces gives a length difference equal to the separators; a nonempty sequence has one more unit than separators. The first branch prevents an empty string from incorrectly returning one. Reference B2, not the unprepared original. These examples use English names and semicolons; check the function-language and separator settings accepted by your file.
Test predictable edge cases
The literal Italian Avena e miele has 13 characters and 11 without its two spaces: 13-11+1=3. Repeated ordinary spaces should normalize to that same sample. Empty prepared text returns 0; dolce-salato gives 1; uno + due gives 3, including the isolated plus. If the requirement excludes symbols, this rule needs modification or a different documented counter, not an unannounced subtraction.
Keep line breaks from joining words
A cell can contain several lines. If a break separates Latte and avena, replacing it with a space preserves Latte avena; deleting it produces Latteavena. Before trimming, replace the actual separators according to the intended rule using the version’s documented text/character functions. No universal invisible-character cleanup is implied. Test a real break and tab when present, inspecting the helper text first.
Sum comparable records only
If C2:C30 holds per-record values, =SUM(C2:C30) totals that convention. Exclude headings, instructions, control samples and summaries unless wanted. Check the real final row and formula coverage after additions. Reconstruct text first if import split a word across records. Preserve identifiers beside results and sort whole records together; detached counts cannot reliably identify which descriptions need revision.
Compare with Writer without forced corrections
When Writer gives another total, first align copied content, including titles or notes. Then isolate hyphens, slashes, numbers and symbols using the Writer discrepancy method. Do not apply a fixed correction derived from one document: a new text can contain different ambiguous cases. Calc remains useful for row-level revision even when the final acceptance count must be produced in Writer.
Keep a small control area
Keep a control area separate from operational totals, with original, prepared text and expected result. Repeat empty, repeated-space and symbol cases after formula or source changes. These are reproducible arithmetic checks, not claimed app tests. If the actual limit is characters, use LEN-based character measurement. Record final version, unit and scope so another person knows when the total applies and when it does not.
The space rule is visible at its boundaries
| Sample | Preparation | Result |
|---|---|---|
| Avena e miele | 13 characters; 11 without spaces | 2 separators + 1 = 3 |
| Repeated ordinary spaces | One separator per boundary | Same normalized total |
| Empty string | Empty | 0 through the condition |
| dolce-salato | No space | 1 unit |
| uno + due | Two separators | 3 units, including the symbol |
Sources and checks
- LibreOffice: TRIM and text functions · Normalize ordinary spaces, count separators with an empty-string guard, and test symbols, hyphens and line breaks before summing rows.
- LibreOffice: TRIM and text functions · Normalize ordinary spaces, count separators with an empty-string guard, and test symbols, hyphens and line breaks before summing rows.