Replace part of a text string based on its position with new text.

REPLACE(old_text, start_num, num_chars, new_text)

---

Replace part of a text string based on its position with new text. To replace one or many instances of specific text based on its content and not its position, use the SUBSTITUTE function.

REPLACE syntax requires the old text string or cell reference which contains a text string to be replaced, the starting position of the text to be replaced, the number of characters to be replaced and the new text to replace the old text string with.

---

REPLACE examples.

In the examples below, the REPLACE function is used to replace specific text from a given position.

---

Syntax

REPLACE(old_text, start_num, num_chars, new_text)

The REPLACE function consists of 4 arguments:

1. old_text. Required. The string or cell reference to which the REPLACE function is to be applied.
2. start_num. Required. The starting position of the text to be replaced.
3. num_chars. Required. The number of characters to replace.
4. new_text. Required. The replacement text or cell reference containing the replacement text.

---

Try it now!

Enter your formulas into the grey cells.

---

Scope

REPLACE can be applied to cell references, text or numbers.

Cell reference:	=REPLACE(C3,1,5,"South") || returns 'South' as replacement text for the first 5 characters in cell C3.
Text:	=REPLACE("North America",1,5,"South") || returns 'South America'.
Number:	=REPLACE(123456,1,3,789) || returns the text string 789456. Multiply the result by 1 to convert to a number.

Caveats

• REPLACE replaces the old_text with a text string. If using on a number, multiply the result by 1 to convert the replacement text string to a number.
• Text can be removed by setting new_text as an empty string "".

---

Related Functions

• REPLACEB replaces old_text based on the number of bytes (as opposed to characters) specified. REPLACEB is intended for languages that use the double-byte character set (DBCS), such as Japanese, Chinese (Simplified), Chinese (Traditional), and Korean. The equivalent syntax is REPLACEB(old_text, start_num, num_bytes, new_text).
• Use the FIND or SEARCH functions to find and replace test when the position is not known in advance.
• Use the SUBSTITUTE function to replace one or more text strings based on content only and not on position.

Functions Category

• Text