Function Problems

Replace Text in One Excel Cell with Data from Another

Ayan MasoodAyan Masood Nov 10, 2026 870 views

Question details

The user needs to dynamically replace specific text placeholders within a cell using data extracted from an adjacent cell.

How to Replace Text in One Excel Cell with Data from Another
Product
Microsoft Excel
Device & OS
not provided
Scenario
Updating thousands of names or descriptions in a spreadsheet that contain repeated placeholder markers (like 'XXX') by swapping them with actual values from other columns.
Observed behavior
Instead of manual entry, the user wants a formula-based approach to extract a substring from a reference cell and insert it into a specific occurrence of a placeholder in the target cell.
Before you start

Verify the exact delimiter or placeholder text you want to replace (e.g., 'XXX') and ensure the data you are extracting follows a consistent pattern across your rows.

Solution 1Recommended

Use the SUBSTITUTE Function for a Single Target Replacement

Extract specific characters from a reference cell and use the SUBSTITUTE function to replace a targeted occurrence of a placeholder.

The SUBSTITUTE function allows you to specify exactly which instance of a word or character you want to replace by using the optional instance_num argument.

1
Select a blank cell

Click on an empty cell in a new column where you want the updated text to be generated.

2
Enter the formula

Type `=SUBSTITUTE(B2, "XXX", LEFT(TEXTAFTER(L2, "."), 3), 2)`. This specific formula extracts the first three characters after a decimal point in cell L2 and replaces the second instance of 'XXX' in cell B2.

3
Apply to the column

Press Enter to see the result. Click the bottom-right corner of the cell and drag the fill handle down to apply this formula to the rest of your dataset.

Use the SUBSTITUTE Function for a Single Target Replacement
Understanding TEXTAFTER: The TEXTAFTER function is available in newer versions of Excel. If you are using an older version, you can achieve the same extraction using a combination of MID and FIND functions.
Easy Data Manipulation with WPS Office

Replace Cell Text Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful text manipulation functions, making it incredibly easy to replace, extract, and clean your data without extra formatting hassles.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the data you need to update.
  2. 2. Start the SUBSTITUTE function: Click on a blank cell next to your data and type `=SUBSTITUTE(` to begin the formula.
  3. 3. Select your parameters: Follow the on-screen syntax prompts to select your target cell, define the old text in quotation marks, and select the cell containing the new text.
  4. 4. Apply across rows: Press Enter to execute the replacement, then double-click the fill handle to instantly apply the formula to the entire column.
Fully compatible with Microsoft Excel formulas (.xlsx)User-friendly interface with helpful syntax tooltips for building complex nested functionsFast and lightweight processing for large datasets with thousands of rows
QA img-9

Frequently Asked Questions

How do I replace text in the same cell without creating a new column?

Formulas cannot overwrite the cell they are actively referencing. To replace text based on another cell's data, you must use a formula in a new column. Once the new text is generated, you can copy the new column and paste it as 'Values' over the original column.

Why is the SUBSTITUTE function not replacing my text?

The SUBSTITUTE function is strictly case-sensitive. Ensure that the text you are trying to replace (e.g., 'xxx' vs 'XXX') matches the exact casing of the text inside your cell.

Can I replace only the second or third occurrence of a word?

Yes. The SUBSTITUTE function includes an optional fourth argument called 'instance_num'. By adding a number at the end of your formula, like `=SUBSTITUTE(A1, "text", "new", 2)`, Excel will only replace the second occurrence.

What if the TEXTAFTER function returns an error?

TEXTAFTER is only available in Microsoft 365 and newer versions of Excel. If it returns a #NAME? error, you are likely using an older version. You will need to use alternative formulas like `MID(A1, FIND(".", A1) + 1, 3)` to extract text after a specific character.