REPLACE Function

Excel Functions › Text

All Excel versions Text

The Excel REPLACE function overwrites text by position: point at a starting character, say how many characters to overwrite, and hand it the new text. It doesn’t care what is there — only where — which makes it perfect for fixed-width codes, masking digits, and inserting separators. Its value-based twin is SUBSTITUTE.

Quick answer: to overwrite the first 3 characters of A2 with a new area code:
=REPLACE(A2, 1, 3, "469")
Start at character 1, overwrite 3 characters, write "469" in their place. Set num_chars to 0 and REPLACE inserts instead of overwriting.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)
ArgumentDescription
old_textRequiredThe text (or cell reference) to work on. The original cell is never changed.
start_numRequiredPosition of the first character to overwrite. The first character is 1.
num_charsRequiredHow many characters to overwrite. 0 overwrites nothing — the new text is inserted.
new_textRequiredThe text to drop in. It can be longer or shorter than the stretch it replaces.

REPLACE vs SUBSTITUTE: REPLACE works by position (“overwrite characters 1–3”); SUBSTITUTE works by value (“swap every hyphen”). If you know where the change goes, use REPLACE; if you know what to change, use SUBSTITUTE.

Overwrite by position

Every number in A2:A5 starts with the old 214 area code. REPLACE doesn’t need to know it’s “214” — it just overwrites characters 1 through 3:

AB
1Old numberNew area code
2214-555-0147469-555-0147
3214-555-0198469-555-0198
4214-555-0023469-555-0023
5214-555-0061469-555-0061
=REPLACE(A2, 1, 3, "469") // overwrite the first 3 characters

Because new_text can be any length, REPLACE also shortens or stretches text. Masking is the classic use — hide the middle digits of an ID:

=REPLACE(A2, 5, 4, "****") // 214-***-0147 style masking

Try it: interactive REPLACE demo

Live demo

Drag the sliders to pick a position and length — the highlighted stretch is what gets overwritten.

Insert text, build dates, find the position with FIND

The insert trick. With num_chars set to 0, nothing is overwritten — the new text is wedged in at start_num. Turn a raw date stamp into a readable one with two nested inserts:

=REPLACE(REPLACE("20260611", 7, 0, "-"), 5, 0, "-") // → 2026-06-11

Add a separator to fixed-width product codes the same way:

=REPLACE(A2, 4, 0, "-") // ABC123 → ABC-123

Dynamic positions. Hard-coded positions break on variable-length text — let FIND locate the spot. Swap out everything after the @ in an email:

=REPLACE(A2, FIND("@", A2) + 1, LEN(A2), "dfwexcel.com") // new domain, any name length

num_chars can safely overshoot — LEN(A2) just means “to the end.” And to delete a stretch, replace it with nothing:

=REPLACE(A2, 1, 4, "") // drop a fixed 4-character prefix

Errors & common pitfalls

#VALUE! — bad position or length. start_num must be 1 or greater and num_chars must be 0 or greater. Zero or negative start_num, or a negative num_chars, returns #VALUE!.

Pitfall: using REPLACE when you mean SUBSTITUTE. =REPLACE(A2,"a","b",...) fails — REPLACE’s second argument is a number, not search text. To find-and-swap by value, you want SUBSTITUTE.

Pitfall: dates turn into serial numbers. Point REPLACE at a real date and it sees the underlying value — =REPLACE(A2,1,2,"30") on 11-Jun-2026 works on “46184”, not the display. Convert first with TEXT: =REPLACE(TEXT(A2,"yyyy-mm-dd"),...).

Pitfall: the result is text. Even when it looks numeric, REPLACE returns a text string. =REPLACE("1099",1,2,"20") gives the text “2099” — wrap it in VALUE() or prefix -- before doing math.

Pitfall: start_num past the end appends. If start_num is bigger than the text length, nothing is overwritten and new_text is simply tacked onto the end. No error, occasionally a surprise.

Practice workbook

📊
Download the free REPLACE practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What's the difference between REPLACE and SUBSTITUTE?
REPLACE overwrites by position — start here, overwrite this many characters. SUBSTITUTE swaps by value — find this string, put that one in its place. "Overwrite characters 1–3" is REPLACE; "change every dash to a dot" is SUBSTITUTE.
How do I insert text without deleting anything?
Set num_chars to 0: =REPLACE(A2, 4, 0, "-") wedges a hyphen in front of character 4 and keeps every existing character. Two nested zero-length REPLACEs turn 20260611 into 2026-06-11.
Can REPLACE find the text to replace by itself?
No — REPLACE only understands positions. Pair it with FIND to locate the spot first: =REPLACE(A2, FIND("@",A2)+1, LEN(A2), "newdomain.com"). If you find yourself doing this often, SUBSTITUTE is usually the simpler tool.
How do I mask part of a number, like a credit card or SSN?
Overwrite the sensitive stretch with asterisks: =REPLACE(A2, 1, 5, "***-**") turns 123-45-6789 into ***-**-6789. Remember the result is text, and the original cell still holds the full value.
Why does REPLACE on a date give me a weird number?
Dates are stored as serial numbers, and REPLACE sees that underlying value - 11-Jun-2026 is really 46184. Convert the date to text first: =REPLACE(TEXT(A2,"mm/dd/yyyy"), 1, 2, "07").
Is REPLACE case-sensitive?
Case never comes into it - REPLACE doesn't search for anything. It overwrites whatever sits at the positions you give it, letters, digits, or spaces alike. Case-sensitivity only matters for value-based tools like SUBSTITUTE and FIND.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: SUBSTITUTE · FIND · LEN · LEFT · TRIM