How to Fix Excel SEARCH Cannot Find Two Consecutive Spaces
Question details
The user is attempting to find the position of two consecutive spaces in a text string using the SEARCH function, but the formula returns a #VALUE! error despite the spaces visually appearing to exist.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Locating a specific double-space delimiter within a text string (e.g., 'LcMon 03/21, R Thu 03/17 6,4') to extract or manipulate the surrounding data.
- Observed behavior
- The formula =SEARCH(" ",A7) returns a #VALUE! error because the spaces in the cell are non-breaking spaces (CHAR(160)) or other hidden whitespace characters rather than standard spaces (CHAR(32)).
Verify the exact characters in your cell by temporarily using the CODE function on the suspected space character, as visual inspections cannot distinguish between regular and non-breaking spaces.
Use the SUBSTITUTE Function to Replace Non-Breaking Spaces
Convert any hidden non-breaking spaces (CHAR(160)) into standard spaces (CHAR(32)) dynamically before running the SEARCH function.
Data imported from the web or other software often contains non-breaking spaces to preserve formatting. The standard Excel SEARCH function looks for a regular space (character code 32) and will fail to match a non-breaking space (character code 160), resulting in a #VALUE! error.
Click on an empty cell where you want the search result to appear.
Type the formula: =SEARCH(" ",SUBSTITUTE(A7,CHAR(160),CHAR(32))) and ensure you reference the correct target cell instead of A7 if necessary.
Press Enter. The SUBSTITUTE function will first replace all non-breaking spaces with standard spaces, allowing the SEARCH function to successfully find the double spaces.

Clean Data Using Find and Replace
Manually replace non-breaking spaces with regular spaces across your entire dataset to make standard SEARCH formulas work.
Effortlessly Clean and Process Data in WPS Spreadsheet
WPS Spreadsheet provides robust text functions and data cleaning tools to seamlessly handle imported data, non-breaking spaces, and complex text extraction tasks without returning frustrating errors.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your existing Excel file containing the messy data.
- 2. Apply standard formulas seamlessly: Use the exact same =SEARCH(" ",SUBSTITUTE(A7,CHAR(160),CHAR(32))) formula, as WPS supports all standard Excel functions natively.
- 3. Utilize alternative data tools: Alternatively, navigate to the 'Data' tab and use the 'Text to Columns' tool to visually split your text using custom delimiters without writing complex formulas.

Frequently Asked Questions
Why does the SEARCH function return #VALUE! when the text clearly contains the characters?
The #VALUE! error occurs in SEARCH when the specific substring cannot be found. This often happens with spaces because visual spaces might actually be non-breaking spaces (CHAR(160)), tabs, or other non-standard whitespace characters that standard space (CHAR(32)) criteria cannot match.
How can I identify exactly what invisible character is in my cell?
You can use the MID and CODE functions together to inspect characters. For example, if you suspect the 15th character is a non-standard space, using the formula =CODE(MID(A7,15,1)) will return 160 for a non-breaking space or 32 for a standard space.
Does the CLEAN function remove non-breaking spaces?
No, the standard Excel CLEAN function removes non-printable characters (characters 0-31) but does not remove non-breaking spaces (character 160). You must use the SUBSTITUTE function or Find and Replace to handle CHAR(160) effectively.




