REPLACE
Replaces part of a text string with a different text string, based on position.
REPLACE(old_text, start_num, num_chars, new_text)Arguments
old_textThe original textstart_numPosition to start replacingnum_charsNumber of characters to replacenew_textThe replacement text
=REPLACE("Hello",1,1,"J")Replaces first character
=REPLACE(A1,5,3,"XXX")Replaces 3 chars starting at position 5
=REPLACE("2023-01",1,4,"2024")Updates year in date string
- •Position starts at 1, not 0
- •Use SUBSTITUTE to replace by content instead of position
- •Can insert text by setting num_chars to 0
- •Confusing REPLACE with SUBSTITUTE - REPLACE swaps text based on a fixed starting position and character count, while SUBSTITUTE matches by content, and using the wrong one leads to replacing the wrong characters entirely
- •Miscounting the start_num or num_chars arguments, especially after the source text changes length, silently replacing the wrong slice of the string
- •Forgetting REPLACE works on a fixed character count, not a delimiter or pattern - it can't dynamically find where a piece of text ends the way SUBSTITUTE or FIND-based logic can
SUBSTITUTEReplaces text by matching content rather than by position, the more common choice when you don't know the exact character positions.MIDExtracts a substring by position, useful for verifying start_num and num_chars visually before using REPLACE.FINDCan supply the starting position dynamically to REPLACE when the exact location of text to replace isn't fixed.What's the difference between REPLACE and SUBSTITUTE?
REPLACE swaps text based on a starting position and number of characters you specify, while SUBSTITUTE finds and replaces text by matching its actual content - use REPLACE when you know exactly where the text sits, SUBSTITUTE when you know what the text says.
Why did REPLACE remove the wrong characters?
The start_num or num_chars arguments likely don't match the current text - this often happens after the source text's length changes and the fixed position no longer lines up.
Can REPLACE find text automatically instead of using a fixed position?
Not on its own, but combining it with FIND lets you calculate the starting position dynamically instead of hardcoding it.
Need to translate a formula using REPLACE?
Use our translator to convert your complete formula