EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel REPLACE Function in Excel
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

REPLACE Function in Excel

By Pradeep SPradeep S

REPLACE in Excel

Excel REPLACE Function (Table of Contents)

  • REPLACE in Excel
  • How to Use REPLACE Function in Excel?

REPLACE in Excel

Replace function in excel by which we can replace any portion of a cell content by selecting the start and last word till we want to replace it with the word in the same syntax. This is as easy as using Find and Replace operational function.

REPLACE Formula in Excel

Below is the REPLACE Formula in Excel:

Start Your Free Excel Course

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

REPLACE Formula in Excel

The REPLACE function in Excel has the below arguments:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,596 ratings)
  • Old_text (Compulsory or required parameter): The cell reference contains the text you want to replace. (It may contain text or numeric data)
  • Start_Num (Compulsory or required parameter): It is the starting position from where the search should begin, i.e. From the left side of the character in the old_text argument
  • Num_chars (Compulsory or required parameter): It is the number of characters you want to replace.
  • new_text (Compulsory or required parameter): It is the new text that you’d like to replace the old_text
Note: the new_text argument does not need to be similar or the same length as num_chars.

How to use REPLACE Function in Excel?

REPLACE Function is very simple to use. Let us now see how to use REPLACE function in Excel with the help of some examples.

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

Example #1 – REPLACE Function for Name Change

The below-mentioned example, Cell “B8”, it contains the name “Andrew Edward”. Here I need to REPLACE that name with the correct name, i.e. John Edward, with the help of REPLACE function.

Example 1

Let’s apply to REPLACE function in cell “C8”. Select the cell “C8” where REPLACE function needs to be applied.

REPLACE Function Example 1-2

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in the select a function box. Double click on REPLACE function.

REPLACE Function Example 1-3

A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.

REPLACE Function Example 1-4

=REPLACE(old_text, start_num, num_chars, new_text)

  • Old_text: is the cell reference containing the text which you want to replace. i.e. “B8” or “Andrew Edward.”
  • Start_Num: It is the starting position from where the search should begin, i.e. From the left side of the character in the old_text argument, i.e. 1
  • Num_chars or Number_of_chars: It is the number of characters you want to replace. i.e. The word ANDREW contains 6 letters which I need to replace; therefore, it is 6
  • new_text: The new text you’d like to replace the old_text, here “John” is a new string & “Andrew” is the old string. i.e. Here, we have to enter a new string, i.e. “John.”

Click ok after entering all the replace function arguments.

REPLACE Function Example 1-5

=REPLACE(B8,1,6,”John”)

 It replaces text in a specified position of a given or supplied string, i.e. John Edward in cell C8

Result of Example 1

Example #2 – Addition Of Missing Word In A Text

In the below-mentioned example, In the cell “B12”, it contains the word “News”. Here I need to add the missing word, i.e. “Paper”. With the help of REPLACE function.

Example 2

Let’s apply to REPLACE function in cell “C12”. Select the cell “C12” where REPLACE function needs to be applied,

REPLACE Function Example 2-2

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in the select a function box. Double click on REPLACE function.

REPLACE Function Example 2-3

A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.

REPLACE Function Example 2-4

=REPLACE(old_text, start_num, num_chars, new_text)

  • Old_text: is the cell reference containing the text which you want to replace. i.e. “B12” or “News.”
  • Start_Num: From the left side of the character in old_text argument (News), i.e. 5, from 5th position, add a New_text
  • Num_chars or Number_of_chars: Here, it is left blank. i.e. We are not replacing anything here, as we are adding a missing word to old_­text.
  • New_text: We are not replacing anything here; here, “ Paper” is a new string. Therefore, we have to enter a new string that has to be added to that old text. i.e. “ Paper”
Note: We had to add one space before the word “paper.”

Click ok after entering all the replace function arguments.

REPLACE Function Example 2-5

=REPLACE(B12,5,,” Paper”)

It adds the new text in a specified position, i.e. “News Paper” in cell C12.

Result of Example 2

Example #3 – Addition Of Hyphen In Phone Number

In the below-mentioned example, Cell “G8” contains a contact number with state code “0224038991”. Here I need to add a hyphen after state code with the help of replace function. i.e. “022-4038991.”

Example 3

Let’s apply to REPLACE function in cell “H8”. Select the cell “H8” where REPLACE function needs to be applied.

REPLACE Function Example 3-2

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “REPLACE” in the search for a function box, REPLACE function will appear in the select a function box. Double click on REPLACE function.

REPLACE Function Example 3-3

A dialog box appears where arguments for REPLACE function needs to be filled or entered i.e.

REPLACE Function Example 3-4

=REPLACE(old_text, start_num, num_chars, new_text)

  • Old_text: is the cell reference containing the text which you want to replace. i.e. “G8” or “0224038991.”
  • Start_Num: From the left side of the character in the old_text argument, i.e. 4. At 4th position, add hyphen “-”
  • Num_chars or Number_of_chars: Here, it is left blank. i.e. We are not replacing anything here, as we are adding Hyphen in between contact number
  • New_text: We are not replacing anything here, “ – ” i.e. Hyphen is a special character or new string Which has to be added in between an old string
Note: We had to add one space before & after the hyphen “ – ” to get the desired result

Click ok, after entering all the arguments in replace function.

REPLACE Function Example 3-5

=REPLACE(“0224038991″,4,,” – “)

It replaces the hyphen “-” in a specified position, i.e. “022-4038991” in cell C12.

Result of Example 3

Things to Remember

#VALUE error occurs if start_num or num_chars argument is a non-numeric or negative value.

REPLACE Example 1-1

It will throw a Value Error.

Value Error

#NAME error Occurs if the Old_text argument is not enclosed in double quotation marks

REPLACE Example 1-3

It will throw a Name Error.

Name Error

Recommended Articles

This has been a guide to REPLACE in Excel. Here we discuss the REPLACE Formula and how to use REPLACE function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel DAY Function
  2. YEAR Function in Excel
  3. Find and Replace in Excel
  4. REPLACE Formula in Excel
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download REPLACE Function Excel Template

EDUCBA

Download REPLACE Function Excel Template

EDUCBA

डाउनलोड REPLACE Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more