How to Remove TEXT Functions from Excel Formulas
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.

- 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.
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.
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.
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.
Press Ctrl + H to open the Find and Replace dialog box.
In the 'Find what' field, type `,MATCH(TEXT(`. In the 'Replace with' field, type `,MATCH(`. Click 'Replace All'.
Next, in the 'Find what' field, type the exact closing string (e.g., `,"0"),`). In the 'Replace with' field, type `,`. Click 'Replace All'.

Simplify Lookup Formulas Using XLOOKUP
Upgrade legacy INDEX/MATCH formulas containing TEXT functions to the more modern and cleaner XLOOKUP function.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing the formulas you want to edit.
- 2. Display Formulas: Navigate to the Formulas tab and click 'Show Formulas' to make formula strings visible.
- 3. Replace Formula Text: Press Ctrl + H, enter the specific TEXT function segments you wish to remove, and click 'Replace All'.

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.




