How to Use SUBSTITUTE & REPLACE Functions to Change Patterns in Your Cells Automatically

Due to the special functions in Excel, more and more people rely on it to finish their work. Hence today, we will demonstrate on using SUBSTITUTE & REPLACE function to change patterns of contents in cells.

SUBSTITUTE and REPLACE function are very practical in dealing with statistics. And the feature of this function can perform very well in certain condition. For instance, in this image below, there are many student numbers.

Those numbers are for students of the last year. This year, there will be new students in this major. Therefore, you need to update the years of the numbers for the new students. But if you change the numbers one by one, you will spend a lot of time on it. And here the SUBSTITUTE & REPLACE function can have an effect.

Steps of Using SUBSTITUTE Function

1. Click the cells next to the first student number.
2. Input the SUBSTITUTE function: =SUBSTITUTE(A1,”5″,”6″,1) . “5” is the old text, “6” is the new text. And you need to replace the first “5” in the number, so the last element would be “1”.
3. Press the key “Enter”. And then the new number will show up.
4. Put your cursor on the bottom right of the corner and double click.

And then you will see that all the new student number is in the column.

This is the formula of SUBSTITUTE function, and the next is the application of REPLACE function.

Steps of Using REPLACE Function

1. Click the cells next to the first student number.
2. Input the SUBSTITUTE function: =REPLACE(A1,4,1,”6″) . “4” is the location of the target element. “1” represents that only one digit need to replace. And “6” is the new text.

The next steps are the same as the operation in the previous part. And you can get the same result.

And the major difference of the two functions lies in the formula. And they can both come to the same result. Therefore, you can choose either of the SUBSTITUTE & REPLACE function in your actual Excel files.

How to Respond to Damaged Excel Files

If your work involves extensive use of Excel, you will certainly meet with Excel corruption. Once such accident happens, your work mush come to a halt. And the result can be imagined. Thus, you need to fix the corrupt Excel files as soon as possible. You may also take the help from a third party tool to recover Excel. And then you can retrieve almost all your important information stored in Excel.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com