REPLACE Function in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> REPLACE Function in Excel



In this tutorial, you will learn how to use the REPLACE formula in excel. REPLACE function in excel is useful to change one or more characters in a row on data of ours.


Why do We Need to Learn About the REPLACE Formula in Excel?


Data is usually many in its amount in a spreadsheet, In processing it, one thing that can be done is changing one or more characters in it. This is usually needed when there is an error in the data and we want to fix it or there is an update need that needs to be done in the part of it.

For example, let’s say we do update on parts of the codes that are given to each of our company’s products. This code consists of product kind, production information, sales region, and other things to make the summarization and the showing of the information detail from each product easier. The process of replacing parts in them is needed so the information on each code can be always updated and adapted to the latest change from the company to each product’s detail.

If the process only needs to be done for a few codes, then maybe it still makes sense if we do it manually. But if the codes are many, then it will take a lot of time. Not to mention the risk of error and inaccuracy in the manual replacement process that can have the effect in the codes are not updated correctly.

To help make the changing process easier, then you can use the REPLACE formula in excel. REPLACE function in excel can help to change a row of characters in data with the position and the amount as you desired. It is very useful to be learned and understood if you often do updates periodically on parts of many data in a spreadsheet.


What is REPLACE Function in Excel?


REPLACE function in excel is a function with the usage objective to change one or more characters in a row on data. If you want to change one or more characters in that data with separate position for each, then you can use another formula which is the SUBSTITUTE formula.

Generally, the input needs of REPLACE formula in excel can be explained as follows:

=REPLACE(old_text, start_num, num_chars, new_text)


Notes:
  • old_text = the data that you want to do the replacement of the characters on
  • start_num = the starting position of the row to be replaced in the data
  • num_chars = the number of characters on the row that you want to do the replacement of
  • new_text = the updated part that you want to put in the data. It does not have to be in the same amount of characters as the old one



How to Use REPLACE Formula in Excel?


The following will explain how to write REPLACE function in excel. This formula needs four required inputs: data which part want to be changed, the starting position of the change process, the number of characters to be changed, and the update that you want to put in the part for the data.


Using REPLACE Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result in

    REPLACE Function in Excel - Screenshot of Step 1

  2. Type REPLACE (can be with large and small letters) and open bracket sign after =

    REPLACE Function in Excel - Screenshot of Step 2

  3. Input the data or cell coordinate where the data to be changed its part is in after open bracket sign then type a comma sign ( , )

    REPLACE Function in Excel - Screenshot of Step 3

  4. Input the number that represents the position where the replacement process in the data will take place or cell coordinate where that number is in then type a comma sign

    REPLACE Function in Excel - Screenshot of Step 4

  5. Input the number of characters that want to be replaced or cell coordinate where that number is in then type a comma sign

    REPLACE Function in Excel - Screenshot of Step 5

  6. Input the updated part of the data or cell coordinate where it is in

    REPLACE Function in Excel - Screenshot of Step 6

  7. Type close bracket sign

    REPLACE Function in Excel - Screenshot of Step 7

  8. Press Enter
  9. The process of REPLACE function in excel is done!

    REPLACE Function in Excel - Screenshot of Step 9



Exercise


After you have done learning how to use REPLACE formula in excel, now you can sharpen your understanding by doing the exercise of REPLACE formula in excel below!

Download the exercise file and do the instructions. Download the answer key file to check your answers after you have done getting them or you can see the answer key file if you are confused about how to do the instructions!

Link for the exercise file of REPLACE formula in excel:
Download here

Instructions

  1. Change the three characters before the first dash in the left code with “XYZ”!
  2. Change one character after the first dash and before the second dash in the left code with “5”!
  3. Change the eight characters after the second dash in the left code with “08092020”!

Link to download the answer key file:
Download here


Additional Note


If you don’t know exactly where the starting position for the replacement process in the data is when you write the REPLACE function in excel, then you can use the help of the FIND formula to find the character that you want to set as the starting position in the data.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert