Formula Errors

Replace Part of a Formula in an Excel Worksheet

Steve KSteve K Nov 12, 2026 868 views

Question details

The user needs to replace a repeated portion of formulas, such as changing *6) to *1), throughout a selected Excel worksheet range without editing each formula separately.

Replace Repeated Text in Excel Formulas Across a Worksheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
The same calculation text appears in multiple formulas and must be updated consistently in one operation.
Observed behavior
The user wants Excel to find a literal multiplication asterisk in formulas rather than interpret it as a wildcard.
Before you start

Select only the cells containing formulas that should be changed. Check one or two formulas first so you can enter the exact text to find, including any closing parentheses or other characters.

Solution 1Recommended

Use Find and Replace to update the formula text in a selected range

Use this method when the same text appears in multiple formulas and every matching occurrence in the selected range should be changed.

Excel treats an asterisk (*) as a wildcard in the Find what field. Add a tilde (~) before the asterisk to search for a literal multiplication operator.

1
Select the formula range

Highlight the cells that contain the formulas you want to update. Do not select unrelated cells if the same text may appear elsewhere.

2
Open Find and Replace

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

3
Enter the literal formula text

In Find what, enter ~*6) to find the literal text *6). In Replace with, enter *1). Adjust the numbers and punctuation to match the exact formula text you need to replace.

4
Replace the matches

Select Replace All to change every matching formula segment in the currently selected range. Review the replacement count, then inspect a changed formula in the formula bar.

Use Find and Replace to update the formula text in a selected range
Why use the tilde?: The tilde escapes the asterisk in the Find what box. Without ~, Excel may treat * as a wildcard and match more formula text than intended.
Edit formulas efficiently in WPS Spreadsheets

Replace repeated formula text with WPS Spreadsheets

WPS Spreadsheets provides Find and Replace tools for updating repeated formula content across a selected cell range. Use a tilde before an asterisk when searching for a literal multiplication operator, then review or replace all matching formulas.

  1. 1. Open the workbook in WPS Spreadsheets: Open your Excel .xlsx or .xls file in WPS Office and select the worksheet containing the formulas.
  2. 2. Select the formula cells: Drag to select the range where the repeated formula text should be changed.
  3. 3. Open Find and Replace: Press Ctrl+H, or go to Home and select Find and Replace.
  4. 4. Enter the search and replacement text: Type ~*6) in Find what and *1) in Replace with, changing the values to match your own formula text.
  5. 5. Apply and verify the replacement: Choose Replace All, then click a changed cell and check its formula in the formula bar.
Find and replace repeated text in selected spreadsheet formulasUse wildcard escaping to search for literal asterisks in formulasOpen and edit Microsoft Excel .xlsx and .xls files with format compatibilityWork in a lightweight spreadsheet interface with familiar keyboard shortcuts
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel use a tilde before the asterisk in Find what?

Excel treats * as a wildcard in searches. Enter ~* before the number, such as ~*6), to search for a literal multiplication asterisk in the formula.

Will Replace All change formulas outside my selected cells?

If you select a cell range before pressing Ctrl+H, Excel applies the replacement within that selection. Confirm the selected range contains only formulas you intend to change.

Can I replace only part of a formula without changing the result values?

Yes. Find and Replace edits the formula text itself. For example, replacing *6) with *1) updates the calculation in every matching selected formula, and Excel recalculates the displayed values.

How can I avoid changing similar formula text by mistake?

Use a longer, more specific Find what phrase that includes nearby formula characters, or use Find Next and Replace to review each match individually.