Find and Replace Within Selected Data in OpenOffice Calc

A global replacement can change a status column, a note and a formula in one pass. Restrict the selection first, search it, and only then make a replacement whose complete before-and-after result you can inspect.

Scope and fixture

Apache OpenOffice Calc 4.1 desktop, English Windows/Linux menus; documentation checked against the official 4.1 Calc Guide and versioned 4.1.6 Help on 19 October 2026. No native application run or save/reopen test was performed for this exercise.

Use a blank spreadsheet and enter each value in its stated cell. Do not paste the Markdown table itself into Calc. A blank entry below means leave the cell empty.

Cell	Enter	Purpose
A1	Ticket	Heading
B1	Status	Heading
A2 / B2	501 / Open	Exact match
A3 / B3	502 / Reopened	Substring trap
A4 / B4	503 / Open	Second exact match
A5 / B5	504 / Closed	Nonmatch
A6 / B6	505 / Hold	Nonmatch
A7 / B7	506 / blank	Missing status
A8 / B8	Formula control / ="Open"	Outside target selection
D1 / D2	Outside control / Open	Another untouched cell
D4	=20+4	Numeric-result search test

Before proceeding, verify B2:B6 reads Open, Reopened, Open, Closed, Hold exactly. This fixture does not use case-only alternatives that AutoInput could normalize during entry. Save find-before.ods.

Search the intended cells before replacing

1. Select B2:B7 explicitly. Do not select the whole B column.
2. Choose Edit → Find & Replace, then expand More Options.
3. Set Search for to Open and Search in to Formulas. This mode includes fixed cell text as well as formula expressions.
4. Enable Current selection only, Match case and Entire cells. Clear Regular expressions, Similarity search and Search in all sheets. Clear any prior formatting criteria with No Format if present.
5. Choose Find All, then close the dialog so you can inspect the selected cells.

The expected matches are B2 and B4 only. B3 contains a longer word, B7 is empty, and B8/D2 are outside the selected area. If any other cell matches, stop before replacing and recheck the scope and search options.

The AOO Find & Replace Help distinguishes Formulas from Values: Formulas searches formula expressions and fixed values, while Values searches displayed calculation results. For editing fixed status text, Formulas avoids treating a formula’s result as writable status data.

Replace after restoring the full selection

Find All changes the active selection. Do not assume the original rectangle is still selected for your next operation.

1. Select B2:B7 again using the Name Box.
2. Reopen Find & Replace and explicitly restore the settings above.
3. Enter Queued in Replace with.
4. Choose Replace All once, acknowledge its result and close the dialog.
5. Audit every target cell plus both outside controls.

Cells	Expected after replacement
B2	Queued
B3	Reopened
B4	Queued
B5	Closed
B6	Hold
B7	Empty
B8	Formula ="Open", displaying Open
D2	Open

There are two intended changes. A replacement-count message is useful but does not replace this cell-by-cell audit. Keep A2:A7 unchanged, including the IDs. If Calc offers to continue searching beyond the selection, decline; that is outside this exercise’s scope.

Search formulas and results without editing them

Select D4 alone and open Find & Replace. Keep the search on the selected cell, turn Entire cells off for this diagnostic, and search for 24.

• In Formulas, the expression =20+4 contains no 24, so no match is expected.
• In Values, the calculated result is 24, so D4 is expected to match.

Now use Formulas to search for 20; D4 is expected to match the formula expression. This is a read-only demonstration. Do not replace 24 in a computed result as a way of modifying the model. Change the input or formula intentionally instead. Close the dialog after this diagnostic. The next exercise explicitly resets the selection and search options; do not assume any previous defaults or selection survived.

Literal characters and a small regular-expression variation

Enter A.1 in F2 and AX1 in F3. Select F2:F3 and reopen Find & Replace. Set Search in to Formulas; enable Current selection only, Match case and Entire cells. Clear Search in all sheets, Similarity search and Search for Styles, and clear any formatting criteria with No Format if present. Set Regular expressions off and Search for to A.1. Find All should select F2 only. The dot is literal.

Before each new search, close the dialog, reselect F2:F3, reopen it and verify Formulas, Current selection only, Match case and Entire cells remain selected, with all-sheets, similarity, style and formatting criteria off. Set Regular expressions on and search for ^A.1$. This pattern is expected to match F2 and F3, because the dot represents one character and the anchors constrain the entire value. Repeat the same selection and option checks, keeping Regular expressions on, then search for ^A[.]1$ to match only F2. Keep these variations read-only; clear Regular expressions afterward.

For a controlled regex replacement, close the dialog and explicitly select B2:B7 again. Reopen Find & Replace, choose Formulas, and enable Current selection only, Match case and Entire cells. Leave Search in all sheets, Similarity search and Search for Styles off, and clear any formatting criteria with No Format if present. Enable Regular expressions, set Search for to ^Queued$ and Replace with to Open, then use Replace All once. The expected reverse changes are B2 and B4 only. Clear Regular expressions after the reverse replacement and close the dialog. Because this reverse operation is safe only for the tiny known fixture, do not generalize it to production data that may already contain legitimate Queued statuses.

Recovery and trouble checks

Save the final reversed state as find-result.ods. B2 and B4 should both contain Open, not Queued. B3/B5/B6/B7 should contain Reopened / Closed / Hold / empty respectively. B8 must still contain the formula ="Open", displaying Open, and D2 must still contain the fixed text Open. D4 remains =20+4, and F2/F3 remain A.1 / AX1 because those tests only searched. Close and reopen the result, then check this exact state and all original ticket IDs. No author native save/reopen result is claimed.

If too many cells changed, use Edit → Undo immediately, verify the original values, and reselect the bounded range. After the session has closed, recover from find-before.ods. Replacing the new word back globally cannot reliably distinguish original values from edited values.

• Reopened changed: Entire cells was off or a broad regular expression was used.
• D2 changed: Current selection only was not in force or the selection was broader than B2:B7.
• No match despite visible text: inspect Search in, lingering format/style filters, case, trailing spaces and the selected range.
• A formula was damaged: restore the before copy, then separate fixed text from formula cells. Searching Values is not a safe way to overwrite formula outputs.
• Only one previous match is searched: reselect the full rectangle after Find/Find All, then reopen the dialog.

See formula text troubleshooting if an equals-sign entry is stored as text rather than a formula. The Calc Guide, chapter 2, covers Find & Replace and warns against uncontrolled Replace All. This tutorial’s matches are independent string/formula expectations, not recorded native results.