Replace Text in One Excel Cell with Data from Another
Question details
The user needs to dynamically replace specific text placeholders within a cell using data extracted from an adjacent cell.

- 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.
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.
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.
Click on an empty cell in a new column where you want the updated text to be generated.
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.
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.

Nest SUBSTITUTE Functions for Multiple Replacements
Use nested functions to replace several different placeholders with data extracted from multiple cells at the same time.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing the data you need to update.
- 2. Start the SUBSTITUTE function: Click on a blank cell next to your data and type `=SUBSTITUTE(` to begin the formula.
- 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. Apply across rows: Press Enter to execute the replacement, then double-click the fill handle to instantly apply the formula to the entire column.

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.




