CLEAN in WPS Spreadsheet: Remove Control Characters While Preserving Meaning
Use CLEAN in WPS Spreadsheet with reproducible control-character examples, length checks and careful text-cleanup boundaries.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: CLEAN removes nonprinting control characters from text. Use it when imported content contains low-level characters that should not be part of the field. It is not a universal “fix every invisible character” command.
Availability and version history
- Introduced: documented by 2019-06-19 in the official WPS lesson; original introduction is not established.
- Availability: WPS Office for Windows Personal; numeric release not identified in the lesson. This is documentation evidence, not an installed-build guarantee.
- Scope: the exact supported build range is unverified. The cited Windows lesson does not establish equivalent availability in other WPS clients.
- Checked: October 14, 2026 against the function-specific lesson and vendor catalogue. Examples were independently calculated; no new native WPS test was performed.
Syntax and scope
=CLEAN(text)
text is the string or reference to clean. The official WPS Windows Personal lesson describes removing nonprinting characters from imported text, including low-level codes at the beginning or end of a data value.
The reproducible examples below use the control character generated by CHAR(5), rather than relying on an invisible pasted character that a webpage or clipboard might alter. The outputs are independently derived expected results, not observations from a native WPS test.
The reference does not enumerate every Unicode character that CLEAN handles. Do not assume that all spacing characters, zero-width characters or characters missing from a particular font will be removed.
Example 1: construct and clean a controlled input
On a blank sheet, put this formula in A2:
=CHAR(5)&"INV-204"&CHAR(5)
The text has one control character at each end. Depending on display behavior, those characters may not look like ordinary visible symbols. In B2 enter:
=CLEAN(A2)
Expected result: INV-204. The seven visible characters remain, and the two control characters are removed.
Use LEN for a before-and-after check:
=LEN(A2)
=LEN(B2)
Expected lengths: 9 before cleaning and 7 afterward. Comparing lengths makes the change observable without depending on how the control character is rendered.
Example 2: preserve ordinary spaces
Put this in D2, with two ordinary spaces around the words and two between them:
=CHAR(5)&" North Depot "&CHAR(5)
Then enter:
=CLEAN(D2)
Expected result: North Depot , preserving the ordinary spaces. Removing control characters is a separate operation from trimming printable spaces.
If the field’s rules also allow extra ordinary spaces to be collapsed, use:
=TRIM(CLEAN(D2))
Expected result: North Depot. The inner CLEAN removes the control characters; the outer TRIM applies the spacing rule. Do not combine these automatically when fixed-width spacing has meaning.
Example 3: notice when removal joins words
Put the following constructed input in F2:
="North"&CHAR(5)&"Depot"
=CLEAN(F2) is expected to return NorthDepot, without a space. CLEAN removes the character; it does not infer that the character was a word separator and substitute a suitable boundary.
If code 5 is known to represent a separator in this specific import format, use an explicit replacement first:
=CLEAN(SUBSTITUTE(F2,CHAR(5)," "))
Expected result: North Depot. SUBSTITUTE replaces the known separator with an ordinary space. The rule is justified by the source format, not by a general belief that every control character should become a space.
Advanced example: flag records changed by cleaning
Create A5:A7 with these inputs:
| Cell | Input formula or text |
|---|---|
| A5 | =CHAR(5)&"AB-7" |
| A6 | Plain text AB-7 |
| A7 | ="AB"&CHAR(5)&"-7" |
In B5 enter =CLEAN(A5) and fill down. Expected B5:B7: AB-7, AB-7, AB-7.
In C5 enter and fill down:
=IF(LEN(A5)=LEN(B5),"No removal","Review change")
Expected C5:C7: Review change, No removal, Review change. This flags length changes for the removal-only transformation shown here. It does not prove that every remaining character is valid, nor that two source records should be merged simply because their cleaned outputs match.
Troubleshooting and safe cleanup
If an unwanted character survives, identify it before adding more cleanup. An ordinary space or an unsupported Unicode character can require a different rule. Avoid deleting every unusual character: punctuation or non-Latin letters may be legitimate data.
Keep the raw import and cleaned output side by side. Record which characters the source format permits and which it treats as separators. Test a clean record, a prefix/suffix control character and a control character between words. Review collisions where several raw strings produce the same cleaned value.
CLEAN returns a derived value; it does not sanitize every workbook location or erase the source. If the goal is a clean export, check the exported fields as well as the formula results, and retain an appropriate internal source copy for troubleshooting.
References and verification
- Official WPS CLEAN lesson, published 2019-06-19 (Chinese; Windows Personal scope).
- WPS CLEAN documentation catalogue.
The lesson establishes the function-specific behavior discussed here. The catalogue confirms its listed identity, not an exact current build range. Expected outputs use original examples checked independently; native execution and broader client compatibility remain unverified.