REPLACE

What does the REPLACE function do?

REPLACE is a text function that allows you to replace part of a string with different text at a specified position. This is especially useful when you need to change a specific number of characters starting from a particular position, for example, to mask phone numbers, edit templates, or remove unwanted characters.

You need to specify where the replacement should start, how many characters to remove, and what new replacement text should be inserted.

If the “number of characters” parameter is zero, text insertion is performed.

In this case, the text is inserted after the previous position.

If the position exceeds the number of characters in the source text, the replacement text is appended to the current text.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text) 

• old_text — original string where replacement will occur
• start_num — starting position (counting from 1) where replacement begins
• num_chars — number of characters to be replaced
• new_text — text that will replace the removed characters

Formatting

Logical values are converted to text while preserving case.

Dates are converted to their corresponding numbers in text format.

To solve this issue, you may need the TEXT function.

Formula examples with REPLACE function

Let’s look at several Excel formula examples using the REPLACE function.

Replace first 3 characters in text with “###”:

=REPLACE("89101234567", 1, 3, "###") 

Multiply number at the end of a text cell:

The formula replaces the last 3 characters with their numeric value increased by 10%. If replacement isn’t possible, it leaves the text as is.

=IFERROR(REPLACE(A1, LEN(A1)-2, 3, TEXT(VALUE(RIGHT(A1, 3))*1.1, "0")), A1)

How the formula works

• RIGHT(A1,3) — takes the last 3 characters from cell A1
• VALUE(…) — attempts to convert these characters to a number
• *1.1 — increases the number by 10%
• TEXT(…;”0″) — converts the result back to text (without decimal part)
• REPLACE(…) — substitutes the obtained value instead of the last 3 characters
• IFERROR(…;A1) — if the last 3 characters aren’t a number (e.g., “XYZ”), returns the original value A1

Other Text functions in Excel

SUBSTITUTE, FIND, SEARCH, LEFT, RIGHT, MID, LEN, TEXT, VALUE

This post is also available in RU.