Excel REPLACE Function and Formula with Examples

How to Use the REPLACE Function and Formula in Excel

In this tutorial, you'll learn how to use the REPLACE function and formula in Excel. REPLACE() is a function to replace part of a text string with a new text string based on the number of characters that you specify.

Excel REPLACE Syntax

The syntax of the REPLACE function is as follows:

=REPLACE(old_text, start_num, num_chars, new_text)

Arguments

  • Old_text: The text in which you want to replace some new characters
  • Start_num: The position of the character in old_text that you want to replace with new_text
  • Num_chars: The number of characters in old_text that you want to replace with new_text
  • New_text: The text that will replace characters in old_text

Remark

  • All arguments in the REPLACE() function are required.

REPLACE Functions Examples

Example 1: Replace a Single Word in the Phrase

Let's say you have the following text string:

This tutorial teaches you how to use the Excel Text functions.

And let's say you want to replace the old_text tutorial with a new_text guide. To do so, you need to know the position of old_text tutorial in which the text string you want to replace and the number of characters of the old_text tutorial. In this case, the old_text tutorial located in the 11th position and contains eight characters. The REPLACE() function will look like this:

=REPLACE("This tutorial teaches you how to use the Excel Text functions.", 6, 8, "guide")

If you run the above formula, you'll get the result as follows:

This guide teaches you how to use the Excel Text functions.

Example 2: Replace 2 Occurrences of Words in the Phrase

Let's say you the following phrase and want to replace 2 occurrences of old_text tutorial:

This is a tutorial on how to use the Excel Text functions. We also have another tutorial that helps you master the Excel Text Function.

To achieve it, you can use the following REPLACE() formula:

=REPLACE(REPLACE("This is a tutorial on how to use the Excel Text functions. We also have another tutorial that helps you master the Excel Text Function.", 11, 8, "guide"),78,8,"guide")

If you run the formula, you'll get the result as follows:

This is a guide on how to use the Excel Text functions. We also have another guide that helps you master the Excel Text Function.

However, as you can see, to replace two occurrences of tutorials, you need to write 2 REPLACE() formulas and need to know the starting position and the number of characters of the old_text tutorial

To replace all occurrences of a word using the REPLACE() function is not a good idea. Instead, you should use the Excel SUBSTITUTE function. 

To get the position of the substring in a given text string, you can use either SEARCH() or FIND() function. Use FIND() if you want to do a case-sensitive search, otherwise use SEARCH().

In this tutorial, you've learned how to use the REPLACE function and formula in Excel. REPLACE() is a function to replace part of a text or string with a new text or string based on the number of characters that you specify.


See also:
Excel ROUNDUP Function with Examples
Excel MIN Function with Examples
Excel COUNTA Function – Count Non-Blank Cells in Excel
Excel COUNTBLANK Function – Count Blank Cells in Excel
Excel SUMIFS Function – Multiple Criteria

Leave a Comment