SUBSTITUTE - Substituting for Specified Text

Last update： 2023-07-25

Overview

Grammar	SUBSTITUTE(text, old_text, new_text, instance_num)	Substitutes new_text for old_text in a text string.
Parameter 1	text	Text string
Parameter 2	old_text	Old text string you want to replace
Parameter 3	new_text	New text string with which you want to replace old_text
Parameter 4	instance_num	Specifies which instance of old_text you want to replace.

The SUBSTITUTE function is used to replace specific text in a text string.
The REPLACE function is used to replace any text in a specific location in a text string.

Notes

Parameters 1, 2, and 3 can be of any type, and Parameter 4 is of the numeric type (that can be defaulted).
Note:
Direct connect data does not support this function.

Example

For example, if you want to replace Cold in the Product name column with Flu, enter the formula SUBSTITUTE(Product name, "Cold", "Flu"), as shown in the following figure.

More examples:

Formula	Result	Notes
SUBSTITUTE("data base","base","model")	data model
SUBSTITUTE("July 28, 2000","2","1",1)	July 18, 2000
SUBSTITUTE("July 28, 2000","2","1")	July 18, 1000
SUBSTITUTE("July 28, 2000","2","1",2)	July 28, 1000

Previous：LEFT – Returning First Character(s) of Required Numbers

Next：MID - Returning a Specified Number of Characters from a Specified Position of a

Helpful
Not helpful
Only read

中文（简体）中文（繁體）日本語

English

滑鼠選中內容，快速回饋問題

滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。

不再提示

10s後關閉

Get
Help

*Problem Type

Incomprehensible Content Incorrect Content Incomplete Procedure Other Content Problems Doc Page Function Feedback

Cannot be empty

Problem Description

0/1000

Cannot be empty

Submitted successfully

Network busy