EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips REPLACE Formula in Excel
 

REPLACE Formula in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 21, 2023

replace formula in excel

 

 

Introduction to REPLACE Formula in Excel

In this article, we will learn about REPLACE Formula in Excel. Normally while working with Excel, we may use the wrong word and want to replace it with the correct word, or sometimes similar pattern data like a month, year, etc., need to update with the new data.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

E.g., Where ever Jan is available, we need to replace it with Feb.

The function that helps us to replace the old text with new text without erasing the existing text is called the REPLACE Function. The text may be a single character or a set of characters.

Replace can be performed in two ways:

  • With the help of Find and Replace.
  • With the use of the Replace Function.

If we press Ctrl + F, we will get the below pop-up.

REPLACE formula 1

In “Find what,” we need to give the existing text in “Find what,” and in “Replace with,” we need to give the new text. We can replace it in one single cell or all the selected cells in a single click.

Replacement using REPLACE Function:

Another way of replacing is by using the “Replace” function. Replace is a worksheet function that can be used in VBA too.

Syntax:

REPLACE formula syntax

Before seeing a few examples of REPLACE function, we will first understand the 4 arguments that should be input while using the REPLACE function.

An argument in REPLACE Formula:

  • Old_text: This argument represents the existing old text.
  • Start_num: This argument represents the starting character number from where we want to replace it.
  • Num_chars: This argument represents the number of characters we want to replace.
  • New_text: This argument represents the new text we want to replace in the old existing text.

How to use Excel REPLACE Formula in Excel?

Excel REPLACE Formula is very simple and easy.

Let’s see how to use the Excel REPLACE Formula with a few examples.

You can download this REPLACE Formula Excel Template here – REPLACE Formula Excel Template

Example #1 – Replace the Text or String

Consider a text string like the one below.

replace formula example 1-1

Now we need to replace June with May by using the REPLACE function. Follow the below steps to REPLACE.

First, select the cell where we want to place the revised text, start the formula, and select the cell with old data. Here A2 is the cell address of existing or old data; hence we selected cell A2.

replace formula example 1-2

We need to give the start number from which character we want to replace. Here we want to replace E, which is in position number 9. So, give a comma after A2 and input 9.

replace formula example 1-3

Here “June” occupies 4 characters which we want to replace with new text. How many characters do we want to replace. Input 4 in num_chars position.

replace formula example 1-4

The last step is for new text. Input the new text in double quotes, as shown in the below picture.

replace formula example 1-5

Press Enter Key.

replace formula example 1-6

Example #2 – Replace Date with the help of REPLACE Formula

Now we will apply the Replace function to replace a month in date format. Consider a date in the below format.

replace formula example 2-1

Now we want to replace 2019 with 2020 using REPLACE. Apply the formula we already applied earlier.

replace formula example 2-2

But we got some strange results here. Why? Because the date will save in number format hence, it added 2020 to that number. What is the solution, then?

First, we need to convert the data into text format using the TEXT function, and the format is “dd-mmm-yyyy” Then, we will replace 2019 with 2020.

In “Old text”, use the TEXT function to convert the date into the required date format as below.

replace formula example 2-3

With the above step, we converted the date in cell G11 into text format “dd-mmm-yyy”, and now we can input the rest of the arguments as usual.

We want to replace 2019 with 2020 hence input 8 in “start_numb” as the 2 is in the 8th position of the string.

replace formula example 2-4

Input the number of characters as 4 because “2019” occupies 4 characters.

replace formula example 2-5

Finally, input the new text “2020” in double quotes.

replace formula example 2-6

Press Enter Key.

replace formula example 2-7

Example #3 – Insert Characters and NESTED REPLACE

In this example, we will see how to input additional characters or symbols without replacing any text and how to use multiple replace conditions.

Consider a string “son-in-law” without a symbol “-“as below.

replace example 3-1

As usual, start the formula REPLACE but don’t select the cell address immediately; instead, input REPLACE function one more time as below.

replace example 3-2

Select the cell address.

replace example 3-3

In the start number, input 4 because we need a hyphen character in the fourth position.

replace example 3-4

Input zero instead of the number of characters; we do not want to replace any characters here.

replace example 3-5

Input Hyphen Symbol (-) instead of new text with double quotes.

replace example 3-6

With this, we can input the first hyphen symbol between “son” and “in”, and the result of this replace function will be input for the outer replace function.

Again, input the start number as 7, the number of characters as 0, and the new text as a hyphen as below.

replace example 3-7

The result will be as below.

replace example 3-8

In the above example, we did not remove any characters from the existing or old text but added an additional symbol. Not only symbols but we can also add the characters. Here we used two REPLACE functions; however, we can add multiple REPLACE functions per our requirement.

Things to Remember about REPLACE Formula in Excel

  • It is used to replace the old text with new text when we know the position of the text.
  • If you do not know the position of old text, you better avoid using it or use it with the FIND function. In the start_num position, use the FIND function.
  • If you do not want to remove any character, input zero in the number of characters.
  • While working with Dates, ensure you convert it into text and later replace it with the required text. If the result requires in Date format, not in text format, then input the DATEVALUE function to the result of REPLACE function.

Recommended Articles

This has been a guide to REPLACE Formula in Excel. Here we discussed how to use the REPLACE formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles to learn more –

  1. Logical Functions in Excel
  2. Find and Replace in Excel
  3. How to Find Mean in Excel
  4. VBA Replace

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download REPLACE Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download REPLACE Formula Excel Template

EDUCBA

डाउनलोड REPLACE Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW