How to Use SUBSTITUTE Function in Google Sheets and Its Practical Use

0
319
How to Use SUBSTITUTE Function in Google Sheets

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 the user. Anyway, I will try to provide you few real life use of SUBSTITUTE formula in Google Doc Spreadsheets.

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.

Value in Cell A1: 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.

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.

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]

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 CHAR function above.

Now a Practical Example to the Use of Google Sheets SUBSTITUTE function.

Google Sheets SUBSTITUTE Function with ARRAYFORMULA

Here in the below example, you can see that few candidates are shortlisted and scheduled for a final interview. Under “Modified Status” in column D we 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, it 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.

Practical Example to the Use of Google Sheets SUBSTITUTE function

This way you can use SUBSTITUTE functions with other functions in Google Sheets.

LEAVE A REPLY

Please enter your comment!
Please enter your name here