Function Problems

How to Remove Text from an Excel Cell Using Another Cell

Huda Qurayshi Oct 1, 2026 868 views

Question details

The user needs to delete or subtract specific text substrings from one cell based on the text value stored in a different cell.

How to Remove Text from an Excel Cell Using Another Cell

Product: Excel
Device & OS: not provided
Scenario: Cleaning up or modifying cell text dynamically using references from other cells, especially when handling text strings or separated lists.
Observed behavior: Users often try to use Conditional Formatting, but it only changes the visual appearance of the cell. Actual text removal requires a dedicated formula outputting to a separate result cell.

Before you start

Ensure that the cells you are referencing do not contain unintended leading or trailing spaces, as this can prevent the formula from finding an exact text match to remove.

Solution 1Recommended

Use the Basic SUBSTITUTE Formula

This is the most straightforward method for text removal, replacing the exact text of one cell with an empty value in a new result cell.

The SUBSTITUTE function searches for specific text within a cell and replaces it with new text. By instructing it to replace the target text with nothing (an empty string), you effectively delete it from the result.

Select a result cell

Click on an empty cell (for example, D2) where you want the cleaned text to be displayed.

Enter the SUBSTITUTE formula

Type the formula `=SUBSTITUTE(C2,B2,"")` into the cell. In this example, C2 is the cell containing the original full text, and B2 is the cell containing the specific text you want to remove.

Apply and fill down

Press Enter to see the result. Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to the rest of the column.

Data Integrity: Using a formula in a new column keeps your original data perfectly intact while displaying the dynamically cleaned results next to it.

Solution 2

Use Advanced Formulas to Clean Leftover Separators

Best used when dealing with delimited lists (like 'CAT+DOG+FISH') where removing a middle word leaves ugly double separators (e.g., 'CAT++FISH').

Advanced Spreadsheet Editor

Easily Manipulate and Clean Text with WPS Spreadsheet

WPS Spreadsheet provides robust support for all standard Excel functions like SUBSTITUTE, MID, and advanced text splitting formulas, allowing you to manipulate and clean your data effortlessly.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to modify.
2. Select an empty cell: Click on a blank cell adjacent to your current data to act as your result column.
3. Input the formula: Type the standard `=SUBSTITUTE()` formula referencing your target text and the cell containing the text to remove.
4. Drag to apply: Press Enter, then use the fill handle to drag the formula down, instantly applying it to all rows.

100% compatible with Microsoft Excel formulas and functionsFree, lightweight, and incredibly fast to launchSupports advanced data cleaning tools and modern array functionsFamiliar intuitive interface with drag-and-fill capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why did conditional formatting not remove my text?

Conditional formatting is designed strictly to alter the visual styling (like font color, highlights, or borders) of a cell based on rules. It cannot delete, calculate, or alter the underlying text data.

Can I use Find and Replace instead of a formula?

Yes, using Find and Replace (Ctrl+H) works for static text removal. However, it will not dynamically update if the text in your reference cell changes later. Formulas provide a dynamic, automated solution.

How do I remove the original columns after using the formula?

If you delete the original columns, the formula will break because it relies on that data. To safely remove them, first select your new formula column, copy it (Ctrl+C), right-click the same cells, and choose 'Paste as Values'. You can then safely delete the original columns.

Excel & Data Problems