## What Is REPLACE Function In Excel?

The

REPLACE function in Excelreplaces a sequence of characters in a string with another set of characters. TheREPLACE functionis an inbuilt function that is similar to the substitute function. TheREPLACE function in Excelis categorized under Excel TEXT functions.For example, consider the below table with values in cell A2. We need to use the below steps to obtain the result using REPLACE Function.

The steps used to replace values in Excel are as follows:

- Select the cell to display the result. In this example, we have selected cell B2.
- Enter the values in cell B2. The complete formula is,
**=REPLACE(A2,6,6,“Methew”)**. - Press
**Enter**key. - The output is returned as ‘John Methew’ in cell B2.

Likewise, we can replace the values using Excel REPLACE Function.

##### Table of contents

###### Key Takeaways

- Excel REPLACE Function is used to replace the text string from a given data,
- The excel REPLACE formula is
**=REPLACE(old_text,start_num,num_chars,new_text)**, where all the arguments are mandatory. - To select the cells that we want to convert to text, we must not have more than one column.
**The “#VALUE” error**occurs if the**start_num argument**or**num_chars**argument is a non-numeric or negative value.- In the REPLACE function, the
*start argument*cannot have a non-numeric or a negative value and the*number_of_chars*cannot have a non-numeric or a negative value.

### REPLACE() Excel Formula

The syntax of the **Excel REPLACE formula **is:

where,

: It denotes the text to be replaced*old_text*: It indicates the position, within old_text argument. It is the first character we want to replace.*start_num*: It shows the number of characters we want to replace.*num_chars*: It is the text which we want to replace in place of the selected text.*new_text*

Unlike other functions, all the arguments in this function are mandatory.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How To Use REPLACE Excel Function?

**1. Access from the Excel ribbon**

- Choose the empty cell which will contain the result.
- Go to the
**Formulas**tab. - Select the
**Text**option from the menu. - Select
**REPLACE**from the drop-down menu. - The window called
**Function Arguments**appears. - As the number of arguments, enter the value in the
**Old_text, Start_num, Num_chars,**and**new_text**. - Select
**OK**.

**2. Enter the worksheet manually**

- Select an empty cell for the output.
- Type
**=REPLACE(**in the selected cell. - Alternatively, type
**=R**and double-click on the**REPLACE function**from the list of suggestions shown by Excel. - Enter the arguments as excel cell references or direct values.
- Close the parenthesis and press
**Enter**key.

Let us take an example to understand this function. The below table shows text string in A2. We need to use the below steps to replace the text using Excel REPLACE Function.

The steps to replace the strings by **Excel REPLACE Function **are as follows:

**Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.****Enter the excel REPLACE formula in cell B2.**

The entered value in the**old_text**argument is cell A2.**The entered value of the start_num argument is 8.****The entered value of the num_chars argument is 4.****The entered value of the new_text argument is early.****The complete formula is =REPLACE(A2,8,4,“early”).**

The function has returned the result as ‘Better early than never’ in cell B2, as shown in the following image.

Similarly, we can use Excel REPLACE Function to replace the text string.

### Examples

#### Example #1 – Replace A String

The following table shows text string in cell A2. We need to use the below steps to replace the text using Excel REPLACE Function.

The steps to replace the strings using **REPLACE function **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the REPLACE formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 7.

**Step 4:** The entered value of the **num_chars **argument is 3.

**Step 5:** The entered value of the **new_text **argument is Tart.

**Step 6:** The complete formula is **=REPLACE(A2,7,3,“Tart”)**.

The function has returned the result in cell B2, as shown in the following image.

Likewise, we can use Excel REPLACE Function to replace text.

#### Example #2 – Replace A Substring

The below table shows text string in cell A2. We need to use the below steps to replace using Excel REPLACE Function.

The steps to replace the strings using** Excel** **REPLACE Function **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the REPLACE formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 5.

**Step 4:** The entered value of the **num_chars **argument is 6.

**Step 5:** The entered value of the **new_text **argument is Facebook.

**Step 6:** The entered complete formula is **=REPLACE(A2,5,6,“facebook”)**.

The function has returned the result in cell B2, as shown in the following image.

Similarly, we can use Excel REPLACE Function to replace text.

#### Example #3 – Replace A Single Character

The below table shows text string in cell A2. We need to use the below steps to replace the text using Excel REPLACE Function.

The steps to replace the strings by the **REPLACE in Excel **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the REPLACE formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 5.

**Step 4:** The entered value of the **num_chars **argument is 1.

**Step 5:** The entered value of the **new_text **argument is u.

**Step 6:** The complete formula is **=REPLACE(A2,5,1,“u”)**.

The function has returned the result in cell B2, as shown in the following image.

Similarly, we can use REPLACE Function to replace text.

#### Example #4 – Replace Numbers

The below table shows text string in cell A2. We need to use the below steps to replace the text using Excel REPLACE Function.

The steps to replace the strings by the **REPLACE in Excel **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the REPLACE formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 1.

**Step 4:** The entered value of the **num_chars **argument is 1.

**Step 5:** The entered value of the **new_text **argument is 1.

**Step 6:** The entered complete formula is **=REPLACE(A2,1,1,“1”)**.

The function has returned the result in cell B2, as shown in the following image.

Similarly, we can use REPLACE Function to replace text.

#### Example #5 – Remove A String

The below table shows text string in cell A2. We need to use the below steps to remove the string using Excel REPLACE Function.

The steps to remove the string using Excel **REPLACE Function **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the REPLACE formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 1.

**Step 4:** The entered value of the **num_chars **argument is 14.

**Step 5:** The entered value of the **new_text **argument is “ ”.

**Step 6:** The complete formula is **=REPLACE(A2,1,14,“ ”)***.*

The function has removed the text string in cell B2, as shown in the following image.

Similarly, we can use Excel REPLACE Function to remove text.

### Common Errors With REPLACE Function

The below table shows text string in cell A2. We need to use the below steps to detect the **Common Error with Excel REPLACE function**.

The steps to detect the error using **Excel REPLACE function **are as follows:

**Step 1:** Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.

**Step 2:** Enter the excel REPLACE function formula in cell B2. The entered value of the **old_text **argument is cell A2.

**Step 3:** The entered value of the **start_num **argument is 0.

**Step 4:** The entered value of the **num_chars **argument is 1.

**Step 5:** The entered value of the **new_text **argument is “ ”.

**Step 6:** The complete formula is ** =REPLACE(A2,0,1,“ ”)**.

The function has returned **#VALUE!** error in cell B2, as shown in the following image.

It is because, the value of **start_num** should be greater than 0.

Similarly, we can use Excel REPLACE Function to replace text.

### Frequently Asked Questions (FAQs)

**1. What does the REPLACE Function do in Excel?**

The Excel REPLACE function is designed to replace the text strings and returns a text string. The **REPLACE function **allows swapping one or several characters in a text string with another character or a set of characters.

The excel REPLACE formula is**=REPLACE(old_text,start_num,num_chars,new_text)**.

For example, consider the below table with a text string in cell A2. We need to apply the REPLACE function to replace the text.

The steps used to replace values in Excel are as follows:**Step 1:** Select the cell to display the result. In this example, we have selected cell B2.**Step 2:** Enter the values in cell B2.

The complete formula is **=REPLACE(A2,1,6,“Center ”)**.

The output is returned as ‘Center Table’ in cell B2.

Similarly, we can use Excel REPLACE Function to replace the text.

**2. Why is the REPLACE Function not working in Excel?**

The REPLACE function can be made to work in Excel with the following suggestions;**·** Ensure the correct cell is selected.**· **Unmark match entire cell content.**·** Remove Filter.**· **Delete unwanted spaces.**·** Unprotect the sheet.**· **Repair corrupted Excel sheet.

**3. Where is the REPLACE function in Excel?**

We can insert Excel **REPLACE function **using the following steps:

1. Choose the empty cell which will contain the result.

2. Go to the **Formulas **tab.

3. Select the **Text **group.

4. Select **REPLACE** from the drop-down menu.

5. A window called **Function Arguments **appears.

6. As the number of arguments, enter the value in the **Old_text, Start_num, Num_chars,** and **New_text.**

7. Select **OK.**

### Download Template

This article must help understand **Excel REPLACE Function **with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Excel REPLACE Function. Here we discuss how to use REPLACE() formula with examples and downloadable excel template. You can learn more from the following articles –

## Leave a Reply