SheetFX
≡
<< Go back to all functions

SUBSTITUTE

The SUBSTITUTE function in Google Sheets is a powerful tool to replace existing text with new text in a string. Whether you're correcting data, cleaning up entries, or manipulating text, the SUBSTITUTE function simplifies the task. Dive into our comprehensive guide to master its application.

Function Syntax and Parameters

Syntax: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

Parameters:

  • text_to_search: The text or cell reference containing the text to be replaced.
  • search_for: The substring you want to replace.
  • replace_with: The new text that will replace the old text.
  • occurrence_number (optional): [Optional] The number of times the replacement should occur. If omitted, all occurrences of search_for will be replaced.

Step-by-Step Tutorial

  1. Replacing a specific occurrence of text:

    • Example: =SUBSTITUTE("Hello world, hello universe!", "hello", "hi", 2)
    • Result: "Hello world, hi universe!"

  2. Replacing all occurrences of text:

    • Example: =SUBSTITUTE("She sells seashells by the seashore", "s", "p")
    • Result: "She pellpe peaphep by the peapore"

Use Cases and Scenarios

  1. Data Cleansing: Replace incorrect or misspelled data entries.
  2. Text Manipulation: Modify specific parts of a text string dynamically.
  3. Report Generation: Customize and standardize text in reports or documents.

Related Functions

  • REPLACE: Replaces part of a text string based on the number of characters you specify.
  • REGEXREPLACE: Replaces part of a text string with a different specified text pattern using regular expressions.

Related Articles