How to Use the SUBSTITUTE Function in WPS on Windows

Last updated:July 23, 2026

This how-to guide includes 3 task topics: entering a basic SUBSTITUTE formula, replacing a specified occurrence, and combining SUBSTITUTE with other formulas.

How-to Index

No.TopicMembership Requirement
11. Enter a basic SUBSTITUTE formulaFree
22. Replace text by occurrence numberFree
33. Combine SUBSTITUTE with other formulasFree

How-to Guide

1. Enter a basic SUBSTITUTE formula

Entry point: Select the target cell, then type the formula in the formula bar or directly in the cell.

  1. Open WPS Spreadsheet and go to the cell where you want the result to appear.
  2. Enter =SUBSTITUTE(original_text, old_text, new_text).
  3. Press Enter to confirm the formula.
  4. Check whether the returned result has replaced the target text with the new text.

Success Criteria

  • The cell returns the updated text result.
  • The full SUBSTITUTE formula is visible in the formula bar.

Tips While Using

  • Text arguments usually need to be enclosed in double quotation marks.
  • If the source text is already in a cell, use the cell reference instead of typing the full text manually.

2. Replace text by occurrence number

Entry point: Add the fourth argument to the SUBSTITUTE formula.

  1. Select the result cell and start entering the formula.
  2. Use the format =SUBSTITUTE(original_text, old_text, new_text, occurrence_number) and add the fourth argument.
  3. Press Enter to run the formula.
  4. Check whether only the specified occurrence has been replaced.

Success Criteria

  • Only the target occurrence is replaced.
  • Other matching text remains unchanged.

Tips While Using

  • The fourth argument means which occurrence to replace, not how many replacements to make.
  • If the specified occurrence number is greater than the number of actual matches, the result usually stays unchanged.

3. Combine SUBSTITUTE with other formulas

Entry point: Enter a nested formula in the formula bar.

  1. Decide whether text replacement should happen before or after the other text operation.
  2. Enter a combined formula that includes SUBSTITUTE in the target cell.
  3. Press Enter to calculate the result.
  4. Fill down or copy the formula to other cells as needed.

Success Criteria

  • The result reflects both the text replacement and the additional formula logic.
  • After filling down, different rows calculate automatically based on their references.

Tips While Using

  • If the combined formula is long, test the SUBSTITUTE part first.
  • Before copying the formula, check whether relative and absolute references are set as expected.