How to change text in an Excel file

The SUBSTITUTE function in Excel locates a string in a cell and substitutes a different string for it. The SUBSTITUTE function searches for repetitions of the string in the cell and is case-sensitive and helps to change text in an Excel file.

The SUBSTITUTE function lets you specify whether it should apply to just one occurrence of the string or to all instances. This substitutes the new string for all instances of the old one.

Change text in an Excel file using Substitute Function Syntax:

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

Text: The string itself or the cell you want the function to look at in the target array.

The string you wish to replace is called old_text.

The new string that will replace the previous one is called new_text.

The instance number of the outdated string you wish to change is [instance_num]. If you wish all instances to be replaced, leave the field empty.

Although the function may be used on both integers and symbols, Excel's standard syntax refers to a string as a text. You may easily replace a character you want to remove with a blank string.

The SUBSTITUTE Function:

How to Use It to change text in an Excel file:

Nothing demonstrates the SUBSTITUTE function better than an example. In this instance, the phone number contains the incorrect country code (+98). The SUBSTITUTE function will be used to replace this area code to the proper one (+1).

An illustration of Excel's replace function

Choose the cell in which the replacement string should appear. In this case, A2 would be that.

Enter the following formula into the formula bar and hit Enter:

=SUBSTITUTE (A2, 92, 1, 1)

This will search for the string 92 in cell A2 and replace it with 1 after finding it. The final section of the formula specifies that just the first 92 should be changed by using the instance number of 1.

The country code for the phone number will now be changed by Excel from the first 92 to a 1. You may delete the instance number and watch the change in the number when each 92 is replaced with a 1.

Nested Substitutions to change text in an Excel file:

Multiple strings cannot be replaced using the SUBSTITUTE function in a single cell. On the other hand, you are permitted to conduct three different replacements within the same cell. Nesting the functions inside of one another is a practical method for doing this.

Excel's layered SUBSTITUTE function in action to change text in an Excel file:

In this illustration, there are three abbreviations in a cell. The intention is to change them out for the entire names they stand for.

Choose the cell in which the output should display. In this instance, C2.

Type the formula shown below:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, M, Merry), R, Roy), K, Kim)

The two other functions will also examine inside cell A2 since the core function does so.

Enter the key. The complete names will now be used in place of the acronym in Excel.

Did you learn about how to change text in an Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience