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.
=REPLACE(old_text, start_num, num_chars, new_text) swaps a fixed slice of text by position. Example: =REPLACE("ABC-123-XYZ", 5, 3, "456") returns "ABC-456-XYZ". Use SUBSTITUTE when you want to swap text by value instead.
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.
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.
Source string or cell reference. Required. Works with numbers (auto-converted) and dates (use TEXT() first for predictable output).
1-based position where replacement starts. Position 1 is the first character. Must be positive. If larger than text length, new_text gets appended.
Count of characters to remove. Zero is legal (insert-only mode). Larger-than-remaining values truncate to end of string.
Replacement string. Empty string is valid for pure deletion. Length doesn't have to match num_chars — output grows or shrinks naturally.
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.
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.
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.
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.
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.
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.
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.
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("ABC-123-XYZ", 5, 3, "456")
Output: "ABC-456-XYZ"
Starts at position 5 (the "1"), removes 3 characters ("123"), inserts "456". The surrounding dashes and "ABC" / "XYZ" stay untouched because they sit outside the replacement window.
=REPLACE("helloworld", 6, 0, " ")
Output: "hello world"
num_chars is 0, so nothing gets removed. The space slides in at position 6. This is the cleanest way to insert characters at a known position without rebuilding the whole string with concatenation.
=REPLACE("prefix_data", 1, 7, "")
Output: "data"
Empty new_text. Strips the first 7 characters. Equivalent to MID(A1,8,LEN(A1)) but more readable. Use when the prefix length is known and fixed — exactly the scenario REPLACE was designed for.
=REPLACE("short", 6, 0, " version 2")
Output: "short version 2"
start_num past the end of the original string. REPLACE just appends new_text. Useful when you're not sure if a string is exactly N characters long but you want to add a suffix anyway. Won't error.
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.
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.
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.
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.
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.
Where REPLACE really earns its keep: scrubbing sensitive data for sharing. Compliance teams use it constantly. So do anyone exporting reports outside their organization.
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.
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.
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.
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.
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.
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.
REPLACEB looks identical to REPLACE syntactically: =REPLACEB(old_text, start_num, num_bytes, new_text). The difference is the third argument counts bytes instead of characters.
Single-byte characters (ASCII, most Latin alphabets) count as 1 byte each. Double-byte characters — Chinese, Japanese, Korean — count as 2 bytes each. If you're working with mixed Latin/CJK text and use REPLACE, character counts get off by one for every CJK character you skip past. REPLACEB handles it correctly. Anyone running international spreadsheets should bookmark this distinction. Quiet bug otherwise.
The VBA Replace() function takes different arguments: Replace(expression, find, replace, [start], [count], [compare]). It works by value, not position — making it closer to SUBSTITUTE than to REPLACE.
The naming overlap confuses people for years. Use VBA Replace when scripting macro-level cleanup across multiple sheets. Use worksheet REPLACE inside formulas. They don't substitute for each other, despite sharing the name.
For one-off swaps across a sheet, Ctrl+H opens the Find and Replace dialog. Wildcards work (* and ?). It edits the underlying data, not formulas.
Good for cleanup before formulas run. Bad for ongoing dynamic transformations — that's where the REPLACE formula stays valuable. The shortcut workflows in Excel are worth memorizing for fast cleanup sessions, especially when paired with named ranges and conditional formatting.
Microsoft 365 added REGEXREPLACE in 2024. Syntax: =REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]). Pattern uses standard regex syntax.
Massive upgrade for variable-length matches that REPLACE and SUBSTITUTE can't handle elegantly. Example: =REGEXREPLACE(A1, "\d{3}-\d{2}-\d{4}", "XXX-XX-XXXX") redacts any SSN-format string anywhere in the cell. For Excel versions before 365, the workaround is VBA with RegExp object.
For position-agnostic transformations, TEXTSPLIT + TEXTJOIN beats nested REPLACE chains. Split a string, modify the pieces with array operations, rejoin.
Way more readable for complex multi-token edits. Older Excel doesn't have these — but if you're on 365, they're worth learning. Combined with FILTER and SORT, you get a full text-processing toolkit that replaces most legacy nested-formula patterns.
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.
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.
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.
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.
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.
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.
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.
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.