Technology

Simple guide on how to use the SUBSTITUTE function in Excel with detailed examples

The SUBSTITUTE function in Excel is a powerful tool that allows you to quickly and easily replace an old text string with a new one. If you are new to this function, follow our article for a detailed understanding and real-life examples of how to use it.

Administrator

14 December, 2023

242

When working with Excel, in addition to functions like UPPER and PROPER that optimize text data processing, the SUBSTITUTE function is also another useful tool that supports users in this task. Let’s find out about the SUBSTITUTE function to learn how to use it in Excel and Google Sheets with FPT Shop!

Understanding the SUBSTITUTE function

The SUBSTITUTE function is used to replace an old text string with a new string in a flexible and convenient way. The common applications of this SUBSTITUTE function include:

Efficiently replacing old text segments.
Flexible combination with other functions to optimize work.

When using the SUBSTITUTE function, users can quickly replace old character strings with new ones without having to manually retype them. This saves a lot of time in editing text, helping users be more proactive in their work.

How to use the SUBSTITUTE function in Excel

The syntax of the SUBSTITUTE function

The syntax of the SUBSTITUTE function is as follows:

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

Where:

Text: The text to perform replacement.
Old_text: The old string you want to replace.
New_text: The new string that will replace the old string.
Instance_num: The position of the old string you want to replace (optional).

Some examples of the SUBSTITUTE function

Example 1: How to use the SUBSTITUTE function to replace the first character in the illustration table.

Step 1: Enter the formula =SUBSTITUTE(B4, C4, D4, 1) in the reference cell of the result.

Step 2: Press Enter and the result will be displayed immediately.

Example 2: Replace all old characters in the illustration table.

Step 1: Enter the formula =SUBSTITUTE(B5, C5, D5) in the reference cell of the result.

Step 2: Press Enter and the result will be displayed immediately.

Example 3: Replace the entire old text segment with a new text segment in the data table.

Step 1: Enter the formula =SUBSTITUTE(B6, “Thời tiết hôm qua”, “Khí hậu hôm nay”) in the reference cell of the result.

Step 2: Press Enter and the result will be displayed immediately.

Example 4: Applying nested SUBSTITUTE functions

In Excel, the SUBSTITUTE function cannot replace multiple different text strings simultaneously. However, you can take advantage of the nesting ability of the SUBSTITUTE function to perform this operation.

For example, with the text “tại FPT Shop có bán laptop giá cạnh tranh” in cell A3, the formula below will replace the word “laptop” with “máy tính để bàn” and “cạnh tranh” with “hấp dẫn”.

=SUBSTITUTE(SUBSTITUTE(A3,”laptop”,”máy tính để bàn”), “cạnh tranh”,”hấp dẫn”)

Points to remember when using the SUBSTITUTE function

The SUBSTITUTE function in Excel distinguishes between uppercase and lowercase letters, meaning it understands that “B” and “b” are two different characters.

For example, the formula below will replace all lowercase “b” characters with “c” in cell A2, and will not affect any uppercase “B” characters.

Conclusion

The instructions on how to use the SUBSTITUTE function in this article were made on a computer running Windows 10 operating system and using the software version of Excel 2013. You can also perform similar steps on other versions of Excel such as 2007, 2010, 2016, 2019, and on laptop lines using Windows or MacOS operating systems with similar operations.