How to Fix FIND Returning #VALUE! When Searching for a Space in WPS Spreadsheets
Question details
The user needs to resolve a #VALUE! error from the FIND function when searching for a space, where the cell either contains no standard space or contains an invisible or non-breaking space instead of a normal space.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Using the FIND function to locate a space in a cell and receiving a #VALUE! error.
- Observed behavior
- FIND returns #VALUE! because the searched text is absent or the cell contains a non-breaking space such as CHAR(160) instead of a standard space.
Open the WPS Spreadsheets workbook and select the cell containing the FIND formula. Use an empty helper column to test cleaned text without overwriting the original data.
Replace non-breaking spaces before using FIND in WPS Spreadsheets
Use this solution when the cell appears to contain a space but FIND still returns #VALUE!, usually because the cell contains CHAR(160) or another invisible character.
A standard space has the code CHAR(32), but imported or copied data often contains a non-breaking space with the code CHAR(160). FIND looks for a normal space, so it cannot match CHAR(160) and returns #VALUE!.
Click an empty helper cell next to your data, such as C2.
Type =SUBSTITUTE(B2,CHAR(160)," ") and press Enter to replace non-breaking spaces with standard spaces.
Drag the fill handle down the helper column to clean every cell referenced by your FIND formula.
Change your formula to =FIND(" ",C2) so it searches the corrected text.
Copy the helper column, right-click the original data range, and choose Paste Special > Values to replace the original values.
Verify whether a standard space actually exists in the cell
Use this approach when you are not sure if the cell contains any space at all, or when you want to avoid the #VALUE! error if no space exists.
Fix FIND #VALUE! Formula Errors in WPS Spreadsheets
Use WPS Spreadsheets to clean non-breaking spaces, inspect character codes, and update your FIND formula so it returns the correct position or a friendly message instead of #VALUE!. The built-in functions work with familiar spreadsheet formulas.
- 1. Open the workbook: In WPS Spreadsheets, open the workbook and click the cell containing the FIND formula that returns #VALUE!.
- 2. Create a cleaning helper column: Insert an empty helper column and enter =SUBSTITUTE(B2,CHAR(160)," ") in the first data row to convert non-breaking spaces to standard spaces.
- 3. Update the FIND formula: Change the formula to search the helper column, for example =FIND(" ",C2).
- 4. Handle missing spaces: If the cell may not contain a space, use =IFERROR(FIND(" ",C2),"No space found") to remove the #VALUE! error.
Frequently Asked Questions
Why does FIND return #VALUE! even though the cell looks like it has a space?
The cell likely contains a non-breaking space with the code CHAR(160) instead of a standard space with the code CHAR(32). Use =SUBSTITUTE(B2,CHAR(160)," ") to replace it, then run FIND again.
How can I check which invisible character is in a cell?
Select a helper cell and use =CODE(MID(B2,1,1)) to return the character code for the first character. Change the position number to inspect other characters in the cell.
Can I make FIND show a message instead of #VALUE! when no space exists?
Yes. Wrap the formula with IFERROR, for example =IFERROR(FIND(" ",B2),"No space found"). This returns the message when FIND cannot locate a standard space.
Does TRIM remove non-breaking spaces in WPS Spreadsheets?
No. TRIM removes standard spaces but does not remove CHAR(160). To remove non-breaking spaces, use =SUBSTITUTE(B2,CHAR(160)," ").
Will this fix also work in Microsoft Excel?
Yes. The same FIND, SUBSTITUTE, CHAR, and IFERROR formulas work in both WPS Spreadsheets and Microsoft Excel because they share common spreadsheet function behavior.
