Excel REPLACE Function

REPLACE swaps out a specific number of characters in a text string, starting at whatever position you tell it. It works by position, not by matching text, so it doesn't care what the original characters actually are. One thing that catches beginners off guard: REPLACE always returns a text value, even when you're replacing characters inside a number.

Available in: all versions of Excel, including Excel 365, Excel 2021, 2019, 2016, and earlier. REPLACE and REPLACEB are original text functions and have worked the same way since Excel first shipped them, unlike REGEXREPLACE, which requires Excel 365.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Parameter	Required	Description
old_text	Yes	The original text string, or a cell reference to it. Works on numbers too, since REPLACE treats everything as a string of characters.
start_num	Yes	The character position where the replacement begins, counting from 1 for the leftmost character.
num_chars	Yes	How many characters to replace, counted forward from start_num. Set this to 0 to insert text without deleting anything.
new_text	Yes	The text that replaces the specified characters. Use "" to delete characters instead of replacing them.

Basic Example

You're building a customer service dashboard and need to mask most of a customer's account number before it displays on screen, showing only the last four digits.

=REPLACE(A2, 1, 12, "************")

// A2 = the account number, "4000123456789012"
// 1  = start replacing at the first character
// 12 = replace 12 characters
// "************" = the masking text (12 asterisks)

REPLACE counts from the first character of A2, swaps the first 12 characters for 12 asterisks, and leaves the last 4 characters untouched. The formula returns ************9012. It looks like the original account number with the middle masked out, but it's now text, not a number.

How REPLACE Works

It works by position, not by content

REPLACE doesn't search a string for anything. It replaces whatever sits at the position you specify, regardless of what that content is. If the string shifts, even by one character, REPLACE will confidently replace the wrong thing without complaint.

It always returns text

Feed REPLACE a number and it hands back text, even if every character in the result is a digit. You'll notice this in the worksheet: the result left-aligns in the cell instead of right-aligning like a normal number. Wrap the whole formula in VALUE() if you need the output back in a calculation.

num_chars can be zero, to insert without deleting

Set num_chars to 0 and REPLACE inserts new_text right before start_num, without removing anything.

=REPLACE("SKU1234", 4, 0, "-")   // returns "SKU-1234"

Position and length don't have to fit the string exactly

If start_num lands past the end of old_text, REPLACE just appends new_text to the end. If num_chars runs past the end of the string, REPLACE stops at the last character and moves on, no error, no warning.

REPLACE vs. REPLACEB

REPLACEB works identically to REPLACE, except it counts bytes instead of characters. That distinction only matters in double-byte character languages like Japanese, Chinese, and Korean, where a single displayed character can take up two bytes. For English text, the two functions behave the same, so most users never touch REPLACEB.

Common Use Cases

Masking part of a loyalty card or account number

You want to print a receipt that shows a customer's card number with the middle digits hidden.

=REPLACE(B2, 5, 8, "********")   // masks 8 digits starting at position 5, keeping the first 4 and last 4 visible

Standardizing a fixed-length SKU prefix

Your warehouse renamed every "OLD-" prefixed SKU to "NEW-" and you need to update thousands of product codes at once.

=REPLACE(C2, 1, 4, "NEW-")

// C2 = the original SKU, "OLD-88213"
// 1  = start at the first character
// 4  = replace exactly 4 characters ("OLD-")
// "NEW-" = the new prefix

Replacing a variable-length area code with FIND

Phone numbers in your list aren't formatted consistently, so the area code isn't always at the same position. Combine FIND with REPLACE so the formula still works no matter where the closing parenthesis falls.

=REPLACE(D2, 1, FIND(")", D2), "(212) ")

// D2 = the phone number, "(415) 555-0199"
// FIND(")", D2) = locates the closing parenthesis dynamically
// "(212) " = the new area code, including the space after it

Handling Errors

REPLACE throws a #VALUE! error whenever start_num or num_chars isn't a valid positive number.

Common causes of #VALUE!:

• start_num is negative, zero, or contains text instead of a number
• num_chars is negative or non-numeric
• A cell reference feeding start_num or num_chars is blank or holds text
• start_num or num_chars points to a cell that itself holds an error, like #REF! or #NAME?, which propagates straight into REPLACE

=IFERROR(REPLACE(A2, B2, C2, "NEW"), "Check start/length values")

Notes & Gotchas

What is the REPLACE function in Excel?

REPLACE is a text function that swaps a specific number of characters in a string, starting at a position you choose, with new text you specify. Unlike functions that search for content, REPLACE only cares about position and character count. It works on any text string, including numbers, once you understand it always hands back a text result.

What is the difference between REPLACE and SUBSTITUTE functions in Excel?

REPLACE swaps characters based on position. SUBSTITUTE swaps characters based on matching content. REPLACE needs a start position and character count; SUBSTITUTE needs the exact text you want to find, wherever it appears. Use REPLACE when you know exactly where the change happens in every row, and use SUBSTITUTE when the text might show up anywhere, or more than once, in the string.

How do you use the REPLACE function in Excel?

Give it four things: the original text, the position to start at, how many characters to replace, and the replacement text. Excel counts positions from the left, starting at 1, then counts num_chars forward from there. Reference cells for each argument instead of typing values directly, so the formula updates automatically as your source data changes.

What is the syntax for the REPLACE function?

The syntax is =REPLACE(old_text, start_num, num_chars, new_text). All four arguments are required. Leave one out and REPLACE returns a formula error rather than running with a built-in default, which is different from many other Excel functions.

How do you replace multiple characters or text strings in Excel?

For multiple replacements at different positions in the same string, nest REPLACE inside another REPLACE, working from the rightmost position first so earlier position numbers don't shift once the string changes length. If you're replacing several different pieces of content rather than fixed positions, SUBSTITUTE (nested, since it only handles one replacement per call) or REGEXREPLACE in Excel 365 usually fit better. Nested REPLACE works best on structured strings, like fixed-length codes, where every piece sits at a known, unchanging position.

Does REPLACE work with dates?

Not safely. Dates are stored as serial numbers, so a formula like =REPLACE(A2, 4, 3, "Nov") aimed at a date displayed as "01-Oct-2026" edits the underlying serial number, not the text you see, and returns a scrambled result. Convert the date to text first with =TEXT(A2, "dd-mmm-yyyy"), then run REPLACE on that.

What happens if num_chars extends past the end of old_text?

Nothing breaks. REPLACE replaces every character from start_num through the end of the string and stops there, silently ignoring the extra count. That's convenient when you don't know the exact string length, but it also means a typo in num_chars, like 200 instead of 20, won't throw an error to flag it.

Related Functions

Function	Use this when...
FIND	You need to locate a dynamic character position first, then feed it into REPLACE as start_num or num_chars.