REPLACE Function in Excel: Complete Guide with Real Examples (2026)

REPLACE Function in Excel: Complete Guide with Real Examples

The REPLACE function in Excel swaps a chunk of text inside a cell based on position — not by matching content. You tell it where to start, how many characters to remove, and what to put in their place. That's it. The formula syntax is =REPLACE(old_text, start_num, num_chars, new_text), and once you've used it a few times the pattern sticks.

Most people confuse it with SUBSTITUTE. They look similar. They behave nothing alike. The function replace in excel that you reach for depends on whether you know the position of the text you want to change or the value of it. Position-based? REPLACE. Value-based? SUBSTITUTE. The rest of this guide walks through both, plus the byte-aware cousin REPLACEB and the VBA Replace() method.

If you're new to Excel formulas, a quick glance at the excel reference shortlist will help. The replace function shows up constantly in cleanup work — fixing imports, masking sensitive data, swapping prefixes, and reformatting dates. Anyone working with messy text data uses it weekly. Maybe daily.

Here's what's covered: the exact syntax with worked examples, the REPLACE vs SUBSTITUTE breakdown, real-world masking patterns (phone numbers, SSNs, file extensions), the double-byte version REPLACEB, the VBA equivalent, regex alternatives in Excel 365, and the two error codes you'll trip over. Nothing fluffy. Just what works. Each section builds on the last, so reading top to bottom takes about fifteen minutes and leaves you with formulas you can paste straight into your own sheet.

Syntax and Arguments — Every Field Explained

The REPLACE function takes four arguments. All four are required. Skip one and Excel throws a #VALUE! immediately.

old_text — the original text or a cell reference. Can be a literal string in quotes like "Hello World" or a reference like A1. Numbers work too but get converted to text in the output.

start_num — the position where replacement begins. Counting starts at 1, not 0. So in the string "ABCDE", the letter C sits at position 3. If start_num is larger than the length of old_text, Excel just appends new_text to the end. Negative or zero values throw an error.

num_chars — how many characters to remove starting from start_num. Zero is valid and means "insert new_text without removing anything." That's how you use REPLACE to insert text mid-string. If num_chars exceeds the remaining length, REPLACE strips everything from start_num to the end.

Worked Example: Phone Number Reformatting

Say cell A1 contains 5551234567. You want (555) 123-4567. Three REPLACE calls nested do it: =REPLACE(REPLACE(REPLACE(A1,7,0,"-"),4,0,") "),1,0,"("). Each inner call inserts a character at a known position. Notice num_chars is zero in each — pure insertion, no deletion. That trick alone saves hours on dirty data imports.

Walking through it: the innermost call inserts a dash before the last four digits. The next layer wraps that result and adds the closing paren plus space after the area code. The outermost layer adds the opening paren at the very start. Reading nested REPLACE formulas takes practice — work from the inside out and the logic becomes clear.

new_text — what to put in. Can be empty string "" to effectively delete a chunk. Can be longer or shorter than num_chars — Excel doesn't care. The output expands or shrinks to fit. Quick test: =REPLACE("hello world",7,5,"") returns "hello ". Five characters removed, none added. The trailing space stays because position 6 (the space) wasn't part of the replacement window.

One subtle gotcha: if old_text is a number, Excel implicitly converts it to text before applying REPLACE. The result is always a text string, even when the operation looked numeric. Wrap with VALUE() if you need the output back as a number for further math. Otherwise sorting and SUMIF behavior gets weird because text-numbers don't compare to real numbers correctly.

Real Examples You'll Actually Use

Theory is fine. Examples land harder. Here are eight patterns that solve genuine spreadsheet problems — the ones that show up in real work, not contrived demos.

Mask the middle of a phone number

Privacy-sensitive reports often need partial masking. Format: keep area code and last four, hide the middle. =REPLACE(A1,4,4,"****") on "5551234567" returns "555****567". Want spacing? Wrap it: =REPLACE(A1,4,4,"-XXXX-") gives "555-XXXX-567". Same idea works for credit card numbers, account IDs, anything with a fixed format.

Strip a fixed-length prefix

Files exported with an ID prefix like "INV-2026-00451". You only want the invoice number. =REPLACE(A1,1,9,"") removes the first nine characters. Result: "00451". The prefix length is known and consistent — that's when REPLACE beats SUBSTITUTE. SUBSTITUTE would need the exact prefix text; REPLACE just counts.

Change a file extension

A column of filenames like "report.csv" needs to become "report.xlsx". Position-counting from the end gets awkward, so combine with FIND: =REPLACE(A1, FIND(".",A1)+1, 10, "xlsx"). FIND locates the dot. start_num is one past it. num_chars of 10 covers any reasonable extension length. You can take this further with the countifs excel approach if you need to count how many files matched a pattern before renaming.

Redact a Social Security Number

SSNs follow XXX-XX-XXXX. Compliance often demands the first five digits hidden: =REPLACE(A1,1,6,"XXX-XX-"). The dash stays in place because num_chars=6 covers "123-45" but new_text rebuilds the prefix as masked characters. Output: "XXX-XX-6789". Quick. Clean. Auditable.

Insert text at a position

Need a separator between two concatenated values? =REPLACE("ABCDEF",4,0," — ") returns "ABC — DEF". num_chars=0 is the insert mode mentioned earlier. Hugely useful for adding hyphens, spaces, or unit suffixes after the fact. This becomes critical when post-processing data dumps where formatting got stripped.

Add a country code prefix to phone numbers

Marketing list of US numbers without country codes. You want +1 at the start of each. =REPLACE(A1, 1, 0, "+1 "). start_num=1 means insert before the first character. num_chars=0 keeps everything. Three characters get prepended. Works on any cell with any starting content. Drag down the column and you're done. Same approach handles adding currency symbols (=REPLACE(A1,1,0,"$")) or unit suffixes via the end position.

Reformat date strings from YYYYMMDD to ISO

Common import format: "20260315". Need ISO 8601 with dashes: "2026-03-15". Two nested inserts: =REPLACE(REPLACE(A1,5,0,"-"),8,0,"-"). The inner call inserts a dash at position 5 (between year and month). The outer call inserts another at position 8 of the already-modified eight-character string (between month and day). Don't forget — once the inner REPLACE runs, the string is 9 characters long, so position 8 hits the right spot. Off-by-one errors are the killer here.

REPLACE vs SUBSTITUTE — When to Use Which

This is where people lose the most time. Both replace text. They take different inputs. Picking the wrong one means rewriting your formula from scratch.

The Core Difference

REPLACE works by position. You tell it where in the string to start cutting and how many characters to cut. It doesn't care what's there. SUBSTITUTE works by matching. You give it the text to find and the text to swap in. It hunts through the string for matches and replaces them.

Example: cell A1 contains "apple-apple-banana". =REPLACE(A1, 7, 5, "grape") returns "apple-grape-banana" — position-based, kills the second "apple" because that's what sits at positions 7-11. =SUBSTITUTE(A1, "apple", "grape") returns "grape-grape-banana" — value-based, swaps every occurrence. =SUBSTITUTE(A1, "apple", "grape", 2) with the instance argument returns "apple-grape-banana" — same as the REPLACE output but reached by matching.

When REPLACE Wins

Fixed-position edits where the text content varies but the position is reliable. Masking phone numbers, hiding middle digits, stripping known-length prefixes, formatting fixed-width records, inserting characters at known positions. If you could describe the operation as "cut characters 5-8 and replace with X" — that's REPLACE.

When SUBSTITUTE Wins

Replacing all occurrences of a specific value, regardless of where they appear. Cleaning up extra spaces (=SUBSTITUTE(A1, " ", "") removes all spaces), swapping characters globally, fixing systematic typos. The instance argument lets you target a specific occurrence too. The syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]). The separate first and last name in excel guide uses SUBSTITUTE heavily — that's a value-based problem.

Combining Both

Nothing stops you nesting them. =REPLACE(SUBSTITUTE(A1," ",""), 1, 3, "XXX") first strips spaces, then masks the first three characters. Real cleanup pipelines look like this constantly.

Masking, Redacting, and Compliance Patterns

Where REPLACE really earns its keep: scrubbing sensitive data for sharing. Compliance teams use it constantly. So do anyone exporting reports outside their organization.

Credit Card Numbers — PCI-Friendly Display

Standard PCI DSS guidance shows only the last four digits. A 16-digit card number like "4532123456789876" becomes "************9876". The formula: =REPLACE(A1, 1, 12, "************"). Twelve characters of asterisks replacing the first twelve digits. Last four stay readable for receipts and verification.

SSN Last Four Only

HR and benefits exports often need just the last four of an SSN. Input "123-45-6789". =REPLACE(A1, 1, 7, "") strips the first seven characters (including the second dash). Output: "6789". Pair with concatenation if you want to keep formatting: ="XXX-XX-" & RIGHT(A1, 4) is cleaner here, but REPLACE works.

Email Domain Swap

Migrating users from @oldcompany.com to @newcompany.com? =REPLACE(A1, FIND("@", A1)+1, 99, "newcompany.com"). FIND locates the @, +1 moves past it, num_chars=99 covers any reasonable domain length, new_text is the target. Bulk migration in one column drag.

Bulk Date Format Conversion

Imports sometimes deliver dates as text in "20260315" format. You need "2026-03-15". Two REPLACE calls: =REPLACE(REPLACE(A1,5,0,"-"),8,0,"-"). The inner call inserts a dash at position 5. The outer one inserts another at position 8 of the already-modified string. Result is a clean ISO date. Then wrap in DATEVALUE if you need a real date type. Quick reference for related formatting is on the how to indent in excel page — text-handling tricks overlap a lot.

Bank Account Masking

Statements often show account numbers like "XXXX-XXXX-1234". From a raw 12-digit number "123456781234": =REPLACE(A1, 1, 8, "XXXX-XXXX-"). Strip first eight digits, replace with masked prefix and separator. Done in one formula across the whole column.

Why Position-Based Beats Pattern-Based Here

You might wonder why not just use SUBSTITUTE or REGEXREPLACE for all these masking tasks. Speed. REPLACE doesn't search — it acts. On a dataset of 100,000 rows, REPLACE finishes in under a second. SUBSTITUTE with a complex pattern can crawl. REGEXREPLACE is even slower because the regex engine has overhead. When the format is fixed and consistent, REPLACE is the right tool. Reach for pattern-based functions only when the format actually varies between rows.

Common Errors and How to Fix Them

Two errors hit nearly every REPLACE user at some point. Both have predictable causes and quick fixes. Worth memorizing them before you hit a deadline. Especially when you're knee-deep in a data cleanup with a column of 50,000 rows and the formula starts spitting errors halfway down.

#VALUE! Error — The Position Problem

This shows up when start_num is zero, negative, or text instead of a number. Excel can't begin replacement at position 0 because positions are 1-based. Same for negative numbers. =REPLACE("hello", 0, 2, "X") throws #VALUE!. Fix: ensure start_num is at least 1. If you're computing start_num dynamically (with FIND or SEARCH), wrap in IFERROR: =IFERROR(REPLACE(A1, FIND("@",A1)+1, 99, "new.com"), A1). That way if FIND fails because there's no @ in the string, the formula returns the original value instead of erroring out. Saves the whole column from showing red.

#VALUE! — num_chars Negative

Same error code, different cause. num_chars must be zero or positive. A formula that computes FIND("end",A1) - FIND("start",A1) can produce a negative number if "end" appears before "start" in the string. Validate first. =IF(start_pos < end_pos, REPLACE(...), A1) protects against the negative-num_chars trap. Habit worth building.

#N/A — Array Context Issues

If you wrap REPLACE in an array formula or use it with dynamic arrays and mismatch the dimensions, #N/A appears. The function itself doesn't return #N/A — but the surrounding context might. Cure: use IFNA to catch it, or restructure the formula to operate on a single value at a time. Dynamic arrays in Excel 365 changed how this propagates, so test on a single cell before spilling across a range.

Empty Cells and Blank Sources

REPLACE on a blank cell returns the new_text with surrounding nothing — sometimes that's fine, sometimes it's wrong. Test: =REPLACE("", 1, 0, "X") returns "X". If you'd rather return empty when the source is empty, wrap with IF: =IF(A1="","",REPLACE(A1,1,3,"NEW")). Real-world data has gaps. Plan for them.

Trailing Spaces and Hidden Characters

Imported data often has trailing spaces or non-breaking spaces (CHAR(160)). Your REPLACE formula counts them. Sudden off-by-one in positions usually traces back to invisible characters. Pre-clean with =TRIM(CLEAN(A1)) before applying REPLACE. TRIM strips standard spaces; CLEAN strips non-printable characters. Both matter when data comes from PDFs, web scrapes, or legacy systems. For row-level cleanup beyond text fixes, the keyboard shortcut to delete row in excel guide covers fast manual deletions that pair well with formula-based cleanup.

Mixed Data Types in a Column

Worse than empty cells: a column where some rows hold text, some hold numbers, some hold errors from earlier formulas. REPLACE doesn't crash on most of these but the output is unpredictable. Pre-filter with =IF(ISTEXT(A1), REPLACE(A1,...), A1) to skip non-text rows. Or convert with =TEXT(A1, "@") first. Either approach beats wondering why row 4,729 in a 10,000-row sheet looks weird.

Wrap-Up: When to Reach for REPLACE

Use REPLACE when you know exactly where in the string the change needs to happen. Use SUBSTITUTE when you know what text to find. Use REGEXREPLACE for variable patterns. Use Find & Replace (Ctrl+H) for one-off cleanup. Use REPLACEB for double-byte text. That covers 99% of text replacement work in Excel. The function isn't glamorous, but the people who master it ship cleaner data faster than everyone else. And clean data is what separates a polished report from one that gets sent back for corrections.