The SUBSTITUTE function in Excel is used to replace specified content in text, with its syntax SUBSTITUTE(text, old_text, new_text, [instance_num]), which supports replacing specific positions or all matches; for example, =SUBSTITUTE(A1, "apple", "grape", 1) can replace the first "apple" with "grape"; practical tips include nesting use to implement multi-position replacement and cleaning of unnecessary characters; the function is case-sensitive and only matches, and does not support fuzzy replacement.

In Excel, the SUBSTITUTE function is a very practical text processing tool, especially suitable for replacing specific text content. Instead of just one-time operation like "Find Replacement", it can be embedded in formulas to automatically complete tasks.

Basic usage: replace the specified text

The basic function of SUBSTITUTE is to replace a substring in a piece of text with another string. Its syntax is as follows:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

• text : original text (can be a cell reference)
• old_text : what you want to replace
• new_text : new content replaced
• instance_num (optional): only replace the content that appears the first time. If you do not fill in, replace all the contents.

For example, suppose the content of cell A1 is "apple, banana, apple, orange" , and you want to replace the first "apple" with "grape" , you can write it like this:

 =SUBSTITUTE(A1, "apple", "grape", 1)

The result will be "grape, banana, apple, orange" .

Practical Tips 1: Replace content from multiple different locations

Sometimes you may need to replace multiple different words, or replace different positions of the same word. At this time, multiple SUBSTITUTE functions can be nested and used .

For example, if you want to replace the first "red" in "red, green, red, blue" with "yellow" and the second "red" with "pink" , you can write it like this:

 =SUBSTITUTE(SUBSTITUTE(A1, "red", "yellow", 1), "red", "pink", 1)

Note: SUBSTITUTE of the outer layer here is to replace the result that the inner layer has been replaced once.

This method is suitable for situations where there are small amounts of replacement requirements, but too much nesting can complicate the formula, and it is recommended to control it within two to three layers.

Practical Tips Two: Remove excess spaces or characters

Sometimes there are unnecessary characters in the imported data, such as line breaks, tabs, and even extra spaces. These can be cleaned with SUBSTITUTE .

For example, to remove all spaces in a cell:

 =SUBSTITUTE(A1, " ", "")

If you want to remove the newline character (usually CHAR(10) in Windows systems), you can write it like this:

 =SUBSTITUTE(A1, CHAR(10), "")

If you are not sure what character it is, you can use CODE() function to view the encoding of a special character.

Notes: case sensitivity & exact match

SUBSTITUTE is case sensitive , that is, "Apple" and "apple" are considered to be two different strings.

Also, it does not perform fuzzy matching, it only replaces exactly the same part. For example:

• "apples" will not be replaced by "apple"
• "apple" in "pineapple" will not be replaced separately

If you want to implement fuzzy replacement, you need to cooperate with other functions (such as SEARCH or REPLACE ), but this is beyond the capabilities of SUBSTITUTE .

Basically that's it. By mastering these usages, you can deal with most text replacement scenarios. Not complicated, but it is really useful.

The above is the detailed content of How to use the SUBSTITUTE function in Excel. For more information, please follow other related articles on the PHP Chinese website!