Find and Replace Data Safely in Gnumeric

Replace two region labels without editing notes or formula text. A safe replacement has a known range, known cell types and a before/after checklist; searching the whole workbook is not a substitute for those decisions.

Prepare a copy and a deliberate trap

Create this synthetic fixture in A1:C5 on Sheet1. Type each formula exactly; adapt argument separators to the installed locale if needed.

Region	Note	Formula in column C
North	North depot	=IF(A2="North",10,0)
Northwest	Northwest depot	=IF(A3="North",10,0)
North	Return to North	=IF(A4="North",10,0)
South	South depot	=IF(A5="North",10,0)

Enter =SUM(C2:C5) in C7. Expected C2:C5 values are 10, 0, 10, 0, total 20. The required edit is North → Central in A2 and A4 only. A3’s Northwest, all notes, and all four formula expressions must remain unchanged.

Save a native checkpoint, then Save As a separate practice copy before replacing. Local Undo is helpful but not a substitute for an unchanged checkpoint.

Preview the exact scope

1. Select A2:A5 and open Edit → Search.
2. Search for North. Choose Range and explicitly check the range field is Sheet1!A2:A5; do not rely on a remembered setting from a previous search.
3. Use Plain text, with Ignore case off and Match whole words only on.
4. In the advanced search controls, include Strings only. Exclude Other values, Comments, Expressions and Results.
5. Run Search and inspect Matches. Expect exactly A2 and A4. If Northwest appears, the matching options are wrong. If a B or C cell appears, the scope or content types are wrong.

Search has a Results option for calculated formula values; Search & Replace’s change categories are not identical. This lesson searches and changes stored strings, not formula results.

Replace one cell first

Open Edit → Search & Replace. Enter Search for North and Replace by Central. Recheck Range Sheet1!A2:A5, Plain text, case sensitivity and whole-word matching. Under Change Cells Containing, select Strings and clear Other values, Expressions and Comments. Enable Keep strings as strings and Query so Gnumeric asks before each replacement. Leave “Save the current settings as default settings” off.

Start the operation. At the A2 prompt, inspect the location, old value and replacement before choosing Yes. At A4, choose No for this first pass. If an unexpected cell appears, cancel rather than approving it.

Inspect the sheet: only A2 should now say Central. A4 remains North. C2 should recalculate to 0, C4 remains 10, and the total becomes 10. The expressions themselves must still contain the original comparison text "North".

This is an important distinction: excluding Expressions prevents direct edits to formula text, but formulas can still recalculate when their input labels change. Whether the business rule should also change is a separate decision, not permission for a broad replacement.

Finish and verify the changed-cell count

Run the same scoped operation again with Query enabled. There should now be just one eligible replacement, A4. Approve it. Expect:

• A2 and A4: Central; exactly two changed input cells across both passes
• A3: Northwest; A5: South
• B2:B5: all four original notes, character for character
• C2:C5: all four original formula expressions, now displaying 0, 0, 0, 0
• C7: 0

Repeat the Search preview for North in A2:A5 with the original options: expect zero matches. Search for Central with the same scope: expect two. Record the observed cells yourself; do not depend on a dialog’s replacement count alone.

Advanced case: preserve text identifiers

In a separate blank area, set E2 to Text before entering 00123. Select only E2 and replace plain text 00123 with 00456, using Strings only, Query and Keep strings as strings. Expect the visible code 00456 and =LEN(E2) equal to 5. Inspect that it is still a text identifier rather than a numeric value displayed with a mask.

This setting matters when replacement text resembles a number or formula. It is not a repair for digits that were already lost before replacement. Avoid regex for the basic region task: whole-word matching suffices for this fixture. Whole words also does not mean whole-cell equality; a cell containing North zone could match, so inspect the preview whenever the real data differ.

Recovery and saved-file checks

For exact rollback, close the practice copy and reopen the unchanged checkpoint. Expect the original labels and total 20. Avoid a global reverse replacement of Central with North: real data might already contain legitimate Central entries that your original operation never changed.

If keeping the changed copy, save and reopen it. Reinspect A2/A4, an untouched Northwest cell, one note and all four expressions. Save/reopen behavior and confirmation prompts are checks to perform; they were not exercised natively for this article.

Evidence and related tasks

Reviewed 2026-10-19: Gnumeric menu documentation, plus search.ui, search-replace.ui and their controllers from the official Gnumeric 1.12.62 source distribution. Range, content-type, Query and Keep strings controls are source-backed. Formula results and match counts were independently checked. No native GUI or screenshot is claimed; wording may differ in another installed version.

Related: IF, LEN and split text into columns, which addresses a different cleanup operation.