Find and Replace Within Selected Cells in LibreOffice Calc

To rename a status safely, select its input cells before opening Edit → Find & Replace, then explicitly enable Current selection only. Check the matches before replacing. The active cell alone is not a reliable boundary for a bulk change.

Prepare a small sheet with deliberate traps

Use desktop Calc, the standard menu bar and a disposable ODS workbook. The linked first-party Help (labeled 26.8 on direct retrieval) and Calc Guide 25.2 were checked on October 19, 2026. This exercise supplies expected results; its GUI workflow and save/reopen behavior have not been run on a native installation.

Before entering the practice data, note the current Tools → AutoInput setting and turn it off. AutoInput Help explains completion from earlier entries in the same column and its effect on case variants. Disabling it avoids completing the deliberate lowercase pending as an earlier status.

Name the first sheet Orders. Enter this table in A1:D6. Enter the last column as formulas, not quoted text:

ID	Status	Note	Echo formula
O-01	Pending	Pending review	=B2
O-02	Paid	Pending	=B3
O-03	Pending review	Waiting	=B4
O-04	pending	Lowercase input	=B5
O-05	Pending	Pending	=B6

Before saving, select B5 and inspect the input line: it must contain exactly lowercase pending, while B2 and B6 contain exactly Pending. Correct B5 if necessary while AutoInput is off. Also verify B4 is the complete phrase Pending review.

On another sheet named Archive, enter Pending in A1. Save status-before.ods, then work in a separate status-working.ods copy. The intended operation is precise: change only Orders.B2 and Orders.B6 to Queued. Keep B4’s longer phrase and B5’s lowercase spelling.

The Echo column is a useful distinction: D2 and D6 will display a different result after their inputs change, but their formula text must remain =B2 and =B6. A visible change is not always a replaced cell.

1. Find the intended cells first

1. Select B2:B6 on Orders using the name box to the left of the formula bar.
2. Open Edit → Find & Replace. Set Find to Pending and Replace to Queued.
3. Expand Other options. Enable Current selection only, Match case, and Entire cells. Disable All sheets, Regular expressions, Wildcards, similarity searching and style searching if present. Remove any retained format criterion with No Format.
4. Set Search in to Formulas. Despite its name, this mode also searches fixed entries, including the literal statuses in B2:B6.
5. Use Find All and inspect the selected addresses. Expect exactly B2 and B6. If a longer phrase or lowercase input appears, stop and correct the options.

The Find & Replace reference documents selection scope, whole-cell matches and the distinction between formula text and calculated values.

2. Replace and reconcile the result

Close the dialog after inspecting matches. Reselect B2:B6 so the replacement scope is explicit, reopen the dialog and recheck the same settings. Choose Replace All, read the reported count, then close the dialog. Expect two replacements.

Cell	Expected stored entry afterward
B2	Queued
B3	Paid
B4	Pending review
B5	pending
B6	Queued
C3 and C6	Pending
Archive.A1	Pending

Select D2 and D6 and inspect the input line: they still contain their original references. Their displayed results are now Queued. D4 still displays Pending review; D5 still displays pending. Save, close and reopen the working ODS, then check these same cells. Do not overwrite the baseline until the results agree.

3. Prove that the scope setting matters

Open the untouched status-before.ods and immediately save a new disposable copy as status-scope-test.ods. Do not rely on Undo from the earlier exercise after closing and reopening the workbook. Verify the fresh copy has B2 and B6 set to Pending, B5 set to lowercase pending, and C3 and C6 set to Pending. Select B2:C6, reopen Find & Replace and keep exact case and entire-cell matching. Find All should now identify four literal cells: B2, B6, C3 and C6.

For this controlled failure test only, reselect B2:C6 and replace all four. The notes in C3 and C6 now incorrectly say Queued. Close the dialog, choose Edit → Undo, and verify all four return to Pending. Finally rerun the correct B2:B6 operation in this disposable copy. Restore your original Tools → AutoInput setting when the practice exercise is finished. Recovery is verified by the affected cells, not merely by clicking Undo once. If other edits intervened, reopen the untouched baseline instead of reversing an uncertain sequence.

Formula results, punctuation and patterns

Use Values when you want to locate displayed results; use Formulas when looking for expression text such as a function name. Do not treat a result match as permission to rewrite the formula that produced it. For this task, change the status input and let the dependent formula recalculate.

A literal dot, asterisk or question mark has a different role when pattern modes are active. Leave those modes off for an ordinary status rename. A deliberate pattern exercise is safer in a helper column: see extracting numbers with REGEX. Global punctuation replacement is especially dangerous inside formulas; Calc formula separators and syntax explains why.

Troubleshooting a surprising match count

• Zero matches: check sheet, selection, case, whole-cell matching, retained formatting criteria and trailing spaces. Pending is not the same whole-cell text as Pending.
• Too many: close the dialog, reselect the intended range and inspect Current selection only and All sheets. Check whether formulas were included accidentally.
• The displayed text changed outside the selection: trace references first. The Echo results are expected dependencies, whereas rewritten note text is a scope failure.
• Only some cells can change: check sheet protection and merged cells before retrying. Do not unprotect or broaden the selection just to force a replacement count.

A filter is not the boundary used in this exercise. If a production table has hidden rows, move the intended inputs to a clean test copy or establish the precise selected cells before changing anything. The Calc data-editing guide provides additional search examples, but your acceptance test remains the exact stored entries above.