Like any other text functions, SUBSTITUTE Function is also pretty easy to use in Google Sheets. I can’t exactly say how useful this function is as it depends on the user. Anyway, I will try to provide you few real-life uses of SUBSTITUTE formula in Google Doc Spreadsheets.
The Syntax of Google Sheets SUBSTITUTE Function
The use of SUBSTITUTE function is to substitute existing text with new text in a string. So you can use SUBSTITUTE formula to clean texts or sentences.
Syntax: SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
Example:
1. Replace DOB with Date of Birth in the below text using SUBSTITUTE.
The value in Cell A1 for the example is “His DOB is 26-11-2017”.
Formula: =SUBSTITUTE(A1,”DOB”,”Date of Birth”)
Result:Â His Date of Birth is 26-11-2017
2. Remove a Text from a sentence using SUBSTITUTE.
The value in Cell A1: Our Office is Off on coming Saturday, Sunday, and Monday
Formula: =SUBSTITUTE(A1,” , and Monday”,””)
Result:Â Our Office is Off on coming Saturday, Sunday
3. Multiple SUBSTITUTE Formula or Nested SUBSTITUTE.
The value in Cell A1: Our Office is Off on coming Saturday, Sunday, and Monday
Formula:Â =SUBSTITUTE(SUBSTITUTE(A1,” , and Monday”,“”),“,”,” &”)
Result:Â Our Office is Off on coming Saturday & Sunday
4. Remove Double Quotes Using SUBSTITUTE [Remove Special Characters]
The value in Cell B2: “India is My Country”
Formula:Â =SUBSTITUTE(B2,CHAR(34),“”)
Result:Â India is My Country
Formula:Â =SUBSTITUTE(B2,CHAR(34),“”,1)
Result: India is My Country”
Formula:Â =SUBSTITUTE(B2,CHAR(34),“”,2)
Result: “India is My Country
I’ve used the CHAR function in the above example.
Now a Practical Example to the Use of Google Sheets SUBSTITUTE function.
Google Sheets SUBSTITUTE Function with ARRAYFORMULA
Few candidates are shortlisted and scheduled for a final interview. Under “Modified Status” in column D I’ve applied a formula as below.
=ArrayFormula(if(date(2017,11,15)<=today(),
SUBSTITUTE(C3:C,”Scheduled on 15/11/2017″,”Postponed to 15/12/2017″)))
I’ve used IF logical, ARRAYFORMULA and SUBSTITUTE functions in a combined form here.
What is the purpose of this lengthy formula?
The above formula checks whether today’s date is less than or equal to 15/11/2017, that is the interview scheduled date.
If it’s true, the Substitute formula replaces the scheduled date with a new postponed date.
As per this post date, the condition matches for the first row. So the formula postponed the interview date in Column D.
This way you can use SUBSTITUTE function in combination with other functions in Google Sheets.
Hi,
How do replace a value and have the replacement appear in the same box that the value initially was? I notice that Substitute always shows up in its own box. For example, I have 0 in box A1 and want to replace 0 with 1 so that box A1 becomes 1. I want to do this on a very large scale and need to do it with formulas. Do you have any suggestions?
Thanks.
Hi, Les,
You won’t be able to do that using a formula. You can use the Edit > Find and Replace command (Ctrl+H) or an Apps Script. I don’t have the script for the same.
Hi There:
I have a cell that has two texts that I need to eliminate to obtain a number; for instance, US$250.00$, I want to substitute “US$” and “$” all at once.
I have been able to do it separately using the following formulas
=SUBSTITUTE(H4,"US$","")and=SUBSTITUTE(I4,"$","").I would like to know if I can combine both formulas into one to make the work easier.
Thanks
Hi, Cruz A Villasana,
You can either nest the two Substitute formulas or use a Regexreplace formula instead.
Using Substitute:
=substitute(SUBSTITUTE(H4,"US$",""),"$","")*1Using Regexreplace:
=REGEXREPLACE(H4, "[^\d\.-]+", "")*1