Others

How to Fix Excel Find and Replace Cannot Find Periods

Amos Gikunda Sep 27, 2026 869 views

Question details

The user is unable to find and replace visible periods representing missing values in an Excel dataset using the built-in Find and Replace tool.

How to Fix Excel Find and Replace Cannot Find Periods in a Dataset

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up or formatting a dataset where periods are used to represent missing data points.
Observed behavior: The Find and Replace function fails to detect the visible periods, resulting in no values being found or replaced.

Before you start

Always test replacements on a copy of your workbook first, or replace the periods with a distinct value like '999' to ensure you don't accidentally overwrite decimal points in your dataset.

Solution 1Recommended

Adjust the Data Range and Search Scope

Ensure that Excel is looking in the correct area of your worksheet and that your selection isn't limiting the search.

Find and Replace behavior changes depending on what is selected. If multiple cells are highlighted, Excel will only search within that specific range. Additionally, the tool's internal options might be set to search the wrong worksheet or workbook.

Select the intended data range

Click and drag to highlight the specific cells, columns, or rows containing the periods you want to replace. Alternatively, click a single cell to search the entire worksheet.

Open the Find and Replace dialog

Press the keyboard shortcut Ctrl + H to instantly bring up the Replace tab.

Adjust the search options

Click on the 'Options' button within the dialog box. Ensure the 'Within' dropdown is set to 'Sheet' (or 'Workbook' if you need to search across multiple tabs).

Execute the replacement

Enter a period (.) in the 'Find what' field, type your desired replacement (like '999' or leave it blank) in the 'Replace with' field, and click 'Replace All'.

Solution 2

Verify Standard Periods and Remove Hidden Characters

Sometimes visible periods are actually special characters, or they are accompanied by invisible spaces that prevent a precise match.

Data Cleaning with WPS

Easily Find and Replace Values in Your Dataset with WPS Spreadsheet

WPS Spreadsheet offers a highly compatible and intuitive Find and Replace tool, making it easy to clean your data, manage missing values, and handle special characters effortlessly.

1. Open your dataset: Launch WPS Office and open your workbook containing the missing values.
2. Select the data range: Highlight the specific cells or columns where you need to replace the periods.
3. Access Find and Replace: Navigate to the 'Home' tab and click on 'Find and Replace', or simply press Ctrl + H.
4. Configure search settings: Click 'Options' to customize your search scope, ensuring it targets the correct worksheet.
5. Replace values: Enter the period in 'Find what', input your replacement value, and hit 'Replace All'.

Accurate Find and Replace tool for managing missing values and periods.Fully compatible with Microsoft Excel (.xlsx, .xls) formats without breaking formulas.Free, lightweight, and incredibly fast for processing large datasets.Familiar user interface for a seamless transition from other office software.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel Find and Replace ignore my periods?

This often happens if you have 'Match entire cell contents' checked while the cell actually contains hidden spaces alongside the period, or if your search scope is restricted to an unintended selected range.

Can I replace periods with completely blank cells?

Yes. Simply enter a period in the 'Find what' box and leave the 'Replace with' box entirely empty, then click 'Replace All'.

Does custom cell formatting prevent Find and Replace from working?

Yes. Sometimes, custom cell formatting or accounting formats can make a cell visually appear to contain a period or dash when it actually holds a zero. Always click the cell and check the Formula Bar to see the true underlying value.

How do I ensure I don't accidentally replace decimal points in my data?

Check the 'Match entire cell contents' box in the Find and Replace options. This ensures that only cells containing exactly a single period (and nothing else) are replaced, leaving decimal numbers intact.

Office Quick Solutions