REPLACE function

REPLACE returns a new text string where a specified number of characters in the original text are substituted with new characters.

=REPLACE(old_text, start_num, num_chars, new_text)

Generate a REPLACE formula

Describe what you need. The generator will reach for REPLACE where REPLACE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How REPLACE reads its arguments
old_textrequiredstart_numrequirednum_charsrequirednew_textrequiredREPLACE →
ArgumentRequiredDescription
old_textRequiredText (or a reference to a cell containing text) to be altered; non-text values are coerced to text, and errors propagate.
start_numRequiredA positive integer indicating the position of the first character to replace; values less than 1 trigger #VALUE!.
num_charsRequiredNumber of characters to replace; must be zero or a positive integer, otherwise #VALUE! is returned.
new_textRequiredThe replacement text; can be empty to delete characters, and non-text inputs are coerced to text.

Returns

It returns a single text string.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Mask the first three digits of a ticket ID

Ticket IDPriorityOpenedClosedAgentCSAT
1001High2023-07-01 08:152023-07-01 10:45Alice9
1002Low2023-07-02 09:302023-07-02 12:00Bob7
=REPLACE(A2,1,3,"XXX")

Result: XXX1

The ticket ID in A2 is "1001". REPLACE starts at character 1 and overwrites the next three characters with "XXX", producing "XXX1". The function leaves any remaining characters (the final "1") untouched.

2. Upgrade a priority label from "High" to "Urgent"

Ticket IDPriorityOpenedClosedAgentCSAT
1001High2023-07-01 08:152023-07-01 10:45Alice9
1002Low2023-07-02 09:302023-07-02 12:00Bob7
=REPLACE(B2,1,4,"Urgent")

Result: Urgent

The priority cell B2 contains "High". By replacing the first four characters (the entire word) with "Urgent", REPLACE returns the new label. Because the new text is longer than the old, the result expands to the full word.

3. Append a role note to an agent's name

Ticket IDPriorityOpenedClosedAgentCSAT
1001High2023-07-01 08:152023-07-01 10:45Alice9
1002Low2023-07-02 09:302023-07-02 12:00Bob7
=REPLACE(E2, LEN(E2)+1, 0, " (Team Lead)")

Result: Alice (Team Lead)

LEN(E2) returns the length of "Alice" (5). Adding 1 positions the start after the last character. Replacing zero characters at that position inserts the note, yielding "Alice (Team Lead)".

Common errors

Which REPLACE error are you seeing?
REPLACE returned an error#VALUE!
Change start_num to a value of 1 or greater, such as =REPLACE(A2,1,3,"XXX").
#VALUE!
Use 0 or a positive integer for num_chars; for insertion without deletion, set num_chars to 0.
#REF!
Verify that all cell references used for numeric arguments exist and are not removed, e.g., replace a broken reference with a literal number.
ErrorWhy it happensHow to fix it
#VALUE!The start_num argument is less than 1 (e.g., 0 or a negative number). REPLACE requires a 1-based index.Change start_num to a value of 1 or greater, such as =REPLACE(A2,1,3,"XXX").
#VALUE!The num_chars argument is negative. REPLACE cannot replace a negative count of characters.Use 0 or a positive integer for num_chars; for insertion without deletion, set num_chars to 0.
#REF!The start_num or num_chars reference points to a cell that has been deleted or is otherwise invalid.Verify that all cell references used for numeric arguments exist and are not removed, e.g., replace a broken reference with a literal number.

Tips and when to use something else

  • Use LEN to calculate dynamic start positions, especially when you need to append text at the end of a string.
  • If you need to replace every occurrence of a substring, SUBSTITUTE is usually a better choice than REPLACE.
  • Wrap numeric values in TEXT before using REPLACE, otherwise numbers are coerced automatically but may lose formatting.
  • Remember that REPLACE works on text only; to modify dates or times, convert them to text first with TEXT.

Related functions

LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.MIDMID extracts a specified number of characters from a text string, starting at a given position, and returns the resulting substring.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.TEXTTEXT converts a numeric, date, or time value into a formatted text string based on a custom format pattern you supply.

Frequently asked questions

How can I replace characters at the very end of a string?
Calculate the string length with LEN, add 1 to get the position after the last character, and use REPLACE with num_chars set to 0. This inserts new text without deleting anything, effectively appending it.
Can REPLACE be used directly on numbers?
REPLACE treats its first argument as text, so numeric values are coerced automatically. However, if you need specific number formatting, wrap the number in TEXT before applying REPLACE to preserve leading zeros or custom patterns.
Why does REPLACE sometimes return the original text unchanged?
When start_num is greater than the length of old_text, there are no characters to replace. In that case REPLACE simply returns the original string unchanged, which is useful for conditional logic.
What is a good way to mask part of a ticket ID while keeping the rest visible?
Use REPLACE to substitute the sensitive portion with asterisks or X's. For example, =REPLACE(A2,2,2,"XX") turns "1001" into "1XX1", hiding the middle characters while leaving the first and last digits readable.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator