Author: Oscar Cronquist Article last updated on June 23, 2022

How to use the REPLACE function

The REPLACE function substitutes a part of a text string based on the number of characters and length with a text string you provide.

1. REPLACE Function Syntax

REPLACE(old_text, start_num, num_chars, new_text)

Back to top

2. REPLACE Function Arguments

old_text Required. The source value you want to change.
start_num Required. A number representing the character's position you want to replace.
num_chars Required. The number of characters you want to replace starting from the start_num argument.
new_text Required. The new value you want to insert.

Back to top

3. REPLACE Function Example

How to use the REPLACE function

This example shown in the image above demonstrates the REPLACE function in cell C4. It replaces one character at position 11 with the string "and" based on the value in cell B4.

The character is the ampersand character, however, the REPLACE function matches no string like the SUBSTITUTE function but uses only a number representing the character's position and a number for the number of characters to replace.

Formula in cell F3:

=REPLACE(B4, 11, 1, "and")

Back to top

Get Excel file

Back to top