SUBSTITUTE

Formulas / SUBSTITUTE
Replace text based on content.
=SUBSTITUTE(text, old_text, new_text, [instance])
  • text - required, the text or reference to a cell that will be substituted
  • old_text - required, the text to replace
  • new_text - required, the text to replace old_text with
  • instance_num - [OPTIONAL], which occurrence of old_text should be replaced with new_text

Examples

  • =SUBSTITUTE("952-455-7865","-","")

    The SUBSTITUTE function can be used to replace one set of characters with another set of characters in a string. For example, using the formula above will replace the hyphen with nothing, returning "9524557865".

  • SUBSTITUTE(B5,"t","b")

    The SUBSTITUTE function can also be used to replace one character with another character in a string. For example, using the formula will replace all occurrences of the letter t with the letter b.

  • =SUBSTITUTE(B6,"t","b",1)

    The SUBSTITUTE function can also be used to replace the first occurrence of a character with another character in a string. For example, using the formula above will replace the first occurrence of the letter t with the letter b.

  • SUBSTITUTE(B7,"cat","dog")

    The SUBSTITUTE function can also be used to replace a word or phrase with another word or phrase in a string. For example, using this formula will replace cat with dog.


Summary

The SUBSTITUTE and REPLACE functions both replace text in strings. SUBSTITUTE is useful for specific text replacements, while REPLACE is useful for replacing any text in a specific location.

  • The SUBSTITUTE function is a powerful tool for replacing text in a string with another string.
  • The SUBSTITUTE function can be used to achieve the same outcome as the REPLACE, FIND, and SEARCH functions.
  • The SUBSTITUTE function can be nested in order to accomplish more complex tasks.

Frequently Asked Questions

What is the SUBSTITUTE function?
The SUBSTITUTE function is a function in Sourcetable which replaces text in a text string. It is useful for replacing specific text in a text string.
What is the REPLACE function?
The REPLACE function is a function in Sourcetable which replaces text in a text string at a specific location. It is useful for replacing any text in a text string in a specific location.
What are the differences between the SUBSTITUTE and REPLACE functions?
  • The SUBSTITUTE function replaces text in a text string.
  • The REPLACE function replaces text in a text string at a specific location.
  • The SUBSTITUTE function is useful for replacing specific text in a text string.
  • The REPLACE function is useful for replacing any text in a text string in a specific location.
What are some examples of when to use the SUBSTITUTE and REPLACE functions?
The SUBSTITUTE function can be used when you need to replace a specific word or phrase in a text string. For example, if you have a list of names and need to replace a certain name with a different one. The REPLACE function can be used when you need to replace any text in a text string in a specific location. For example, if you want to replace a certain character in a string at a certain index.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV