Formula Errors

How to Fix Excel SEARCH Cannot Find Two Consecutive Spaces

Huda QurayshiHuda Qurayshi Oct 29, 2026 871 views

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.

How to Fix Excel SEARCH Cannot Find Two Consecutive Spaces
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)).
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on an empty cell where you want the search result to appear.

2
Enter the nested formula

Type the formula: =SEARCH(" ",SUBSTITUTE(A7,CHAR(160),CHAR(32))) and ensure you reference the correct target cell instead of A7 if necessary.

3
Apply the formula

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.

Use the SUBSTITUTE Function to Replace Non-Breaking Spaces
Formula Breakdown: CHAR(160) represents the non-breaking space, while CHAR(32) represents a regular space. Nesting them ensures your SEARCH function works flawlessly on dirty data.
WPS Office Spreadsheet Solution

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. 1. Open your dataset in WPS: Launch WPS Spreadsheet and open your existing Excel file containing the messy data.
  2. 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. 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.
100% compatible with Microsoft Excel formulas, functions, and file formatsAdvanced text-to-columns and built-in data cleaning featuresLightweight application that processes large datasets quickly and efficiently
microsoft office alternative - wps office

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.