Document Editing Problems

How to Find and Replace Excel Cell Values with Blanks

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to remove pasted pricing values from a specific column using the Find and Replace tool, but the tool fails to find the values.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Attempting to clear specific cell contents in column E by finding the values and replacing them with nothing.
Observed behavior: The Find and Replace dialog only shows 'Formulas' in the 'Look in' dropdown, and the tool cannot locate the target cells, likely due to hidden characters or formatting issues.

Before you start

Highlight only the specific column or range you want to modify before opening the Find and Replace dialog to prevent accidental data deletion in other areas of your spreadsheet.

Solution 1Recommended

Copy Exact Cell Contents to Bypass Hidden Characters

This method ensures you capture any hidden spaces or non-breaking characters that prevent the Find and Replace tool from making a successful match.

When data is pasted from external sources like websites or other documents, it often carries invisible characters such as non-breaking spaces. Manually typing the value into the Find box will fail if these hidden characters are present.

Select a target cell

Click on a representative cell in your column that contains the pricing value you want to remove.

Enter cell editing mode

Press F2 on your keyboard to edit the cell directly. Highlight the exact and entire contents of the cell, then press Ctrl + C to copy it.

Open Find and Replace

Press Ctrl + H to open the Find and Replace dialog box.

Paste and clear

Paste the copied content (Ctrl + V) into the 'Find what' box. Ensure the 'Replace with' box is completely empty.

Execute replacement

Click the 'Replace All' button to change all matching cell values to blanks.

Handling Formula Restrictions: If the cells are generated by formulas and the 'Look in' option is locked, you must first convert the formulas to static text. Copy the column, right-click, and select 'Paste as Values' before attempting to replace.

Efficient Data Management

Quickly Find and Replace Values in WPS Spreadsheet

WPS Spreadsheet provides a robust Find and Replace tool equipped with advanced options to easily distinguish between formulas and static values, helping you clean your data effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you want to clear.
2. Select the target range: Highlight the specific column or cells where the unwanted values are located.
3. Launch Find and Replace: Press Ctrl + H to bring up the Find and Replace dialog box.
4. Adjust advanced options: Click 'Options' to expand the menu, and change the 'Look in' dropdown to 'Values' if necessary.
5. Replace with blanks: Enter your target text in 'Find what', leave 'Replace with' empty, and click 'Replace All'.

Advanced Find and Replace options to accurately target values, formulas, or formatting.Seamlessly handles trailing spaces and invisible characters during data cleaning.Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Lightweight, fast, and completely free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Find and Replace say it cannot find my data?

Your cells might contain hidden formatting, trailing spaces, or non-breaking spaces. To fix this, edit the cell (press F2), copy its exact contents, and paste that directly into the 'Find what' box.

How do I change the 'Look in' option from Formulas to Values?

In the Find and Replace dialog box, click on the 'Options' button to expand the advanced settings. This will reveal the 'Look in' dropdown where you can switch the selection from 'Formulas' to 'Values'.

Can I replace specific cell formatting with a blank cell?

Yes. In the expanded Find and Replace dialog, click the 'Format' dropdown next to the 'Find what' box to specify the formatting you want to locate. Leave the 'Replace with' box empty to clear the contents of cells matching that format.

How do I find and replace a line break to make the cell blank?

Click inside the 'Find what' box and press Ctrl + J to insert a line break character (it will look like a tiny blinking dot). Leave the 'Replace with' box empty and click 'Replace All'.

Document & File Issues