WPS Spreadsheets: Clean Nonbreaking Spaces in Imported Excel Text
Question details
Remove nonstandard spaces and hidden characters from imported text such as serial numbers so the values can be consistently searched, matched, and processed.
- Product
- WPS Spreadsheets and Excel-compatible worksheets
- Device & OS
- not provided
- Scenario
- Imported text contains visible spaces that may be ordinary spaces, nonbreaking spaces, or other nonprinting characters.
- Observed behavior
- Text values that look identical may contain different character codes, causing formulas, searches, and comparisons to return unexpected results.
Select a blank helper column before applying a cleanup formula so you can compare the original and normalized text. A normal space is CHAR(32), while a nonbreaking space is CHAR(160); codes such as 65, 78, and 80 are letters, not spaces.
Use a formula to normalize nonbreaking spaces and hidden characters
Use this method when imported text needs consistent cleanup across many rows before searching, matching, or exporting the data.
The SUBSTITUTE function converts nonbreaking spaces to ordinary spaces, CLEAN removes nonprinting characters, and TRIM removes extra ordinary spaces. This combination is especially useful for imported serial numbers and identifiers.
In WPS Spreadsheets, click the empty cell next to the first imported value, such as B1 when the original text is in A1.
Type =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) and press Enter. If the text only contains ordinary and nonbreaking spaces, use =TRIM(SUBSTITUTE(A1,CHAR(160)," ")).
Select the formula cell and drag the fill handle down, or double-click the fill handle, to apply the formula to the remaining imported rows.
Copy the cleaned results, select the original destination cells, then choose Home > Paste > Paste Special > Values to keep the normalized text without formulas.
Replace a specific unwanted character with Find and Replace
Use this method when you know the exact character to remove or replace throughout a worksheet.
Clean Imported Text Quickly with WPS Spreadsheets
WPS Spreadsheets provides Excel-compatible text formulas and practical replacement tools to remove nonbreaking spaces, hidden characters, and extra spacing from imported values. You can clean a helper column, convert the results to values, and continue working in a familiar spreadsheet interface.
- 1. Open the imported workbook: Launch WPS Spreadsheets and open the XLSX, XLS, or CSV file containing the imported text.
- 2. Add the cleanup formula: In a helper column, enter =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) for the first text value.
- 3. Apply the formula to all records: Use the fill handle to copy the formula down through every imported row.
- 4. Paste cleaned results as values: Copy the helper column, then use Home > Paste > Paste Special > Values to replace or save the cleaned text.
Frequently Asked Questions
What is the difference between CHAR(32) and CHAR(160) in spreadsheet text?
CHAR(32) is a normal space, while CHAR(160) is a nonbreaking space. Nonbreaking spaces often arrive through web pages, exports, or copied data and may not be removed by TRIM alone.
Why does TRIM not remove the visible spaces in my imported serial numbers?
TRIM removes extra ordinary spaces but does not directly convert nonbreaking spaces. Use =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) to convert CHAR(160) to a normal space before trimming.
How do I remove hidden nonprinting characters from imported text?
Use =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))). CLEAN removes many nonprinting characters, SUBSTITUTE handles nonbreaking spaces, and TRIM removes extra normal spaces.
How can I keep the cleaned text without keeping formulas?
Select and copy the formula results, choose the target cells, then use Home > Paste > Paste Special > Values in WPS Spreadsheets. This replaces formulas with their displayed text results.
Are character codes 65, 78, and 80 types of spaces?
No. Character code 65 represents A, 78 represents N, and 80 represents P. The common space codes relevant to cleanup are 32 for a normal space and 160 for a nonbreaking space.
