If you want to replace text in a string, the SUBSTITUTE function is a great formula to learn.
This formula is very easy to use and will certainly come in handy if you are a regular spreadsheet user.
In this tutorial, I will show you how to use the SUBSTITUTE function in Google Sheets.
The SUBSTITUTE function will take an existing text string and replace part of the text with new text that you specify in the formula.
Here is the syntax of SUBSTITUTE:
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
- text_to_search – this is the full text that will be searched and part of which will be replaced
- search_for – this is the string to search for inside the text_to_search
- replace_with – this is the string that will replace search_for
- occurrence_number – this is an optional argument that indicates the instance of the search_for text to replace. By default, every instance of the search_for text will be replaced, but if you wish to only replace a specific number, you can indicate that here
Important notes about the SUBSTITUTE function:
- This function will replace match and replace part of words. If it searches for “Matt” it will replace text within “Matthew”
- It is case-sensitive. It will not replace if the capitalization isn’t the same in your text_to_search and search_for arguments
- The function returns text
To get a better idea of how this formula works, let’s take a look at a few examples:
You can see that SUBSTITUTE will simply replace the matching text with my replace_with text and output a new text string.
Look at how in the third row my formula is replacing part of a word. My search_for text matches part of the text_to_search, so that part is replaced.
Pay attention to the bottom two examples so you can understand how the occurence_number argument works. In the fifth row, my occurence_number is 1, so it replaces the first instance of my search_for text only.
In the sixth row, my occurence_number is 2, so it replaces the second instance of my search_for text only.
The SUBSTITUTE function is a pretty easy function to learn and understand.
If you are regularly using spreadsheet programs like Google Sheets, this function will definitely come in handy as you do your work.
The ability to quickly replace part of a string with new values makes learning this function worth it.