Function Problems

How to Remove TEXT Functions from Excel Formulas

Rana GarciaRana Garcia Nov 10, 2026 870 views

Question details

The user needs to remove repeated TEXT functions from lookup formulas without breaking the original formula structure or unintentionally modifying other workbook data.

How to Remove TEXT Functions from Excel Formulas
Product
Excel
Device & OS
not provided
Scenario
Editing and cleaning up complex legacy lookup formulas that contain unnecessary TEXT function wrappers.
Observed behavior
Lookup formulas are cluttered with TEXT functions, overcomplicating the structure and requiring a safe and efficient bulk removal method.
Before you start

Press Ctrl + ` (grave accent) to display all formulas in your worksheet, ensuring the Find and Replace tool targets the formula strings rather than the calculated values.

Solution 1Recommended

Remove TEXT Functions Using Find and Replace

Use the Find and Replace feature on displayed formulas to systematically extract and remove the TEXT function segments.

When dealing with complex lookup formulas, manually deleting TEXT wrappers can be error-prone. By targeting specific formula text strings, you can safely remove them in bulk without breaking the rest of the formula.

1
Show Formulas

Go to the 'Formulas' tab on the ribbon and click 'Show Formulas', or press the keyboard shortcut Ctrl + ` to reveal the underlying formulas in all cells.

2
Open Find and Replace

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

3
Replace the Opening Function

In the 'Find what' field, type `,MATCH(TEXT(`. In the 'Replace with' field, type `,MATCH(`. Click 'Replace All'.

4
Replace the Closing Function

Next, in the 'Find what' field, type the exact closing string (e.g., `,"0"),`). In the 'Replace with' field, type `,`. Click 'Replace All'.

Remove TEXT Functions Using Find and Replace
Verify Unique Strings: Always verify that your search strings are unique to the targeted formulas to prevent accidental changes to other functions in the workbook.

Simplify Your Formulas with WPS Spreadsheet

WPS Spreadsheet offers powerful Find and Replace capabilities and fully supports modern functions like XLOOKUP. This makes cleaning up complex formulas and organizing your data faster and more efficient.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the formulas you want to edit.
  2. 2. Display Formulas: Navigate to the Formulas tab and click 'Show Formulas' to make formula strings visible.
  3. 3. Replace Formula Text: Press Ctrl + H, enter the specific TEXT function segments you wish to remove, and click 'Replace All'.
Fully compatible with Microsoft Excel formulas, including INDEX, MATCH, and XLOOKUP.Robust Find and Replace tools to edit formulas in bulk securely.Lightweight, fast, and completely free to use for everyday tasks.Familiar user interface requiring no steep learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Find and Replace change cell values instead of formulas?

If 'Show Formulas' is not enabled, the Find and Replace tool might target the calculated results or visible text instead of the formula itself. Press Ctrl + ` to display the underlying formulas before performing the replacement.

Can I undo a Find and Replace operation if it breaks my formulas?

Yes, you can press Ctrl + Z immediately after the replacement to revert the changes. It is highly recommended to use 'Find All' first to verify the targets before clicking 'Replace All'.

Why were TEXT functions used inside the MATCH formula originally?

The TEXT function is often used to ensure data types match between the lookup value and the lookup array (for example, converting a number to a text format). Without matching data types, the MATCH function may fail and return an error.