CLEAN Function (OpenOffice Calc)
Learn the CLEAN function in Apache OpenOffice Calc: Removes all nonprintable characters from text.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in OpenOffice Calc. How formula examples are checked
Quick answer
Use CLEAN to remove nonprinting characters accidentally imported into a Calc cell. It is different from TRIM: CLEAN removes controls; TRIM handles surplus spaces.
Exact syntax and argument definitions
=CLEAN(text)
text (required) — the text string, expression or reference to clean. The function returns text with nonprinting characters removed. It does not remove ordinary spaces, which the official page calls out explicitly.
Direct reference: Apache’s dedicated CLEAN page, last revised in 2024; accessed 2026-10-11. The examples are English Calc syntax and include semicolons where relevant.
Version, locale and source boundary
This is a reference-checked, not native-executed Apache OpenOffice Calc guide. The cited Apache Wiki function article (edited in February 2024) establishes the documented syntax and examples; the documentation is not a fresh instrumented test of OpenOffice 4.1.16. Apache announced 4.1.16 on 10 November 2025 for Windows, macOS and Linux, and described 4.1.17 as in preparation on 26 September 2026. No different function behavior is claimed for a particular 4.1.x point release or operating system.
Examples use the English function name and the semicolon (;) argument separator shown in Apache’s official formulas. Language or locale preferences can affect separators, function display names and case handling. Where formulas appear to fail on entry, check the Calc Function Wizard or the application’s formula settings. Unquoted cell references such as A2 refer to cells, while quoted values such as "North" are text.
Worked examples and expected results
Reproducible cells
Enter these in a new Calc sheet (the formula bar, not the visual display, shows the control-character inputs):
On a narrow screen, scroll this table sideways to see every column.
| Cell | Value or formula entered | Explanation |
|---|---|---|
A2 |
=CHAR(7)&"cat"&CHAR(8) |
Surrounds cat with two control characters. |
A3 |
=CHAR(9)&" North "&CHAR(10) |
A tab, two spaces at each end and a line feed. |
A4 |
North Depot |
Two ordinary spaces between words. |
On a narrow screen, scroll this table sideways to see every column.
| Formula entered in any blank cell | Expected result | Independent explanation |
|---|---|---|
=CLEAN(A2) |
cat |
The controls on each side are removed. This matches Apache's published example. |
=LEN(CLEAN(A2)) |
3 |
Three visible letters remain. |
=CLEAN(A3) |
North (two leading and two trailing spaces) |
The tab and line feed are removed; ordinary spaces remain. |
=CLEAN(A4) |
North Depot |
Ordinary double spaces are not removed. |
Note: The expected values are documentation-derived and independently checked character by character; the formulas were not executed in OpenOffice.
Advanced example: clean then normalize pasted labels
For the A3 control-and-space example, use:
=TRIM(CLEAN(A3))
Expected result: North. CLEAN removes tab and newline; TRIM then removes surrounding ordinary spaces. For A4, =TRIM(CLEAN(A4)) is expected to yield North Depot, with one space between the words. This is a two-step cleaning workflow, not proof that CLEAN alone removes multiple spaces.
Retain the original text in a separate column until you check data quality. Do not rely on this to remove every Unicode invisible character or HTML entity: the official example establishes behavior for the tested control characters, not all encodings.
Errors, limits and troubleshooting
- It seems nothing changed: the input may contain ordinary spaces. Apache explicitly says CLEAN does not remove them; try
TRIM(CLEAN(A2))if whitespace normalization is the goal. - An imported label still fails to match: inspect the underlying characters, including nonbreaking spaces or non-ASCII controls. The cited function page does not promise that every invisible Unicode code point is removed.
- The cleaning formula does not parse: check spelling (
CLEAN), quotes, parentheses and the correct argument separator for your installation. - A source cell already contains an error: diagnose the source before treating the output as successfully cleaned; no special error recovery is promised for CLEAN.
- Unexpected numeric or empty-cell behavior: the cited Apache page does not document all coercion and blank-cell cases; mark those tests pending instead of inventing a particular error code.
Related OpenOffice guides
TRIM · LEN · SUBSTITUTE · FIND — these Markdown paths exist in the 2026-10-10 source archive; live HTTP verification remains pending.
Cross-software compatibility
Microsoft Excel, Google Sheets, and LibreOffice Calc offer similar text-transformation features, but this tutorial documents Apache OpenOffice Calc. Differences in localized formula separators, Unicode case mapping, array/range handling and text coercion have not been cross-tested. Importing a spreadsheet is not proof of identical outcomes for every language or edge case.
Official sources
-
Apache Wiki: CLEAN, revision 260098 — syntax, ordinary-spaces limitation, CHAR(7)/CHAR(8) example, and LEN result; retrieved 11 October 2026.
-
Apache Wiki: TRIM — separate space normalization; retrieved 11 October 2026.
-
Apache OpenOffice 4.1.16 release announcement — version/platform context, 10 November 2025; retrieved 11 October 2026.
-
Apache development update, 26 September 2026 — 4.1.17 still in preparation; retrieved 11 October 2026.
-
Original alphabetical Calc reference — site metadata link retained.
How this guide was checked
The syntax and behaviour are based on the Apache OpenOffice Calc references cited above. The sample calculations were independently checked; they are expected results, not outputs observed in Apache OpenOffice Calc. Test locale-sensitive text conversion and imported files in your own installation. View the example-check record.