Text
REPLACE Function in Excel
Replaces part of a text string with a different text string, based on the number of characters you specify.
Syntax
- =REPLACE(old_text, start_num, num_chars, new_text)
Arguments
- old_text (required): Text in which you want to replace some characters
- start_num (required): Position of the first character to replace (1-based)
- num_chars (required): Number of characters to replace
- new_text (required): Text that will replace characters in old_text
Examples
- =REPLACE("2024-06-09", 1, 4, "2025") - Change year in ISO date - Result: 2025-06-09
- =REPLACE("Hello", 6, 0, " World") - Insert text at position - Result: Hello World
- =REPLACE(A1, 5, 3, "XXX") - Replace 3 chars at position 5 - Result: Modified text
Excel REPLACE function documentation
- REPLACE is a Text function that swaps a fixed number of characters at a known position — unlike [SUBSTITUTE](/functions/substitute/), which searches for specific text anywhere in the string.
- Syntax: =REPLACE(old_text, start_num, num_chars, new_text). Available in all modern Excel versions.
- Use REPLACE when the edit location is defined by character index (positions 1-based), such as ISO date strings, fixed-width codes, or legacy exports.
- Pair with [LEFT](/functions/left/), [MID](/functions/mid/), [RIGHT](/functions/right/), and [FIND](/functions/find/) when building hybrid text parsers.
REPLACE syntax and arguments explained
- old_text (required): the source string or cell reference to modify.
- start_num (required): 1-based position where replacement begins — 1 is the first character.
- num_chars (required): how many characters to remove starting at start_num.
- new_text (required): text to insert in place of the removed segment (can be empty "" to delete).
- Example: =REPLACE("2024-06-09", 1, 4, "2025") returns "2025-06-09" by swapping the year at position 1 for 4 characters.
REPLACE vs SUBSTITUTE decision guide
- REPLACE — you know the start position and length (fixed-width files, yyyy-mm-dd segments).
- SUBSTITUTE — you know the exact substring to find (product codes, hyphens, domain names).
- REPLACE can delete characters with new_text="" — e.g. =REPLACE(A1,5,1,"") removes the 5th character.
- SUBSTITUTE replaces all occurrences by default; REPLACE always targets one contiguous span.
Worked examples to copy
- Swap year in ISO date: =REPLACE(A1, 1, 4, "2026") when A1 is "2024-03-15".
- Mask middle of ID: =LEFT(A1,3)&"***"&RIGHT(A1,4) often pairs with REPLACE for fixed layouts.
- Insert text mid-string: =REPLACE("Hello", 6, 0, " World") inserts at end when num_chars is 0.
- Phone format tweak: =REPLACE("5551234567", 4, 0, "-") inserts hyphen after area code when positions are predictable.
People also ask
- What is the REPLACE function in Excel? — It replaces num_chars characters starting at start_num with new_text.
- REPLACE vs SUBSTITUTE? — REPLACE is position-based; SUBSTITUTE is search-based and case-sensitive.
- Can REPLACE remove characters? — Yes, set new_text to an empty string "".
- Why #VALUE! on REPLACE? — start_num or num_chars invalid, or old_text is not text.
Common errors
- #VALUE! if start_num or num_chars invalid
- Position is 1-based, not 0-based
Use cases
- Fixed-width data editing
- Date string updates
- Insert or delete characters at known positions
Frequently asked questions
- What is the difference between REPLACE and SUBSTITUTE? REPLACE changes characters at a specific position and length. SUBSTITUTE finds and replaces exact text anywhere in the string. Use REPLACE for fixed positions; SUBSTITUTE for known substrings.
- How do I delete characters with REPLACE? Set new_text to an empty string: =REPLACE(A1, 5, 2, "") removes 2 characters starting at position 5.
- Is REPLACE position 0-based or 1-based? 1-based. start_num=1 refers to the first character in old_text, matching Excel's MID and FIND conventions.
- Can REPLACE insert text without removing characters? Yes. Set num_chars to 0: =REPLACE("abc", 2, 0, "-") returns "a-bc" by inserting at position 2.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use REPLACE
- Fixed-width data editing — common Text scenario for REPLACE.
- Date string updates — common Text scenario for REPLACE.
- Insert or delete characters at known positions — common Text scenario for REPLACE.
REPLACE in the Text category
- Browse all Text functions at /categories/text/ for related formulas.
- REPLACE syntax: =REPLACE(old_text, start_num, num_chars, new_text)
- old_text (required): Text in which you want to replace some characters
- start_num (required): Position of the first character to replace (1-based)
- num_chars (required): Number of characters to replace
- new_text (required): Text that will replace characters in old_text
- Confirm REPLACE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm REPLACE arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- SUBSTITUTE (/functions/substitute/): Replaces occurrences of old text with new text in a string.
- MID (/functions/mid/): Returns a specific number of characters from a text string, starting at a specified position.
- LEFT (/functions/left/): Returns the specified number of characters from the start of a text string.
- RIGHT (/functions/right/): Returns the specified number of characters from the end of a text string.
- FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).
Errors to watch for
- #VALUE! if start_num or num_chars invalid — review causes on linked error pages in the directory.
- Position is 1-based, not 0-based — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter REPLACE on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
REPLACE worked examples to copy
- =REPLACE("2024-06-09", 1, 4, "2025") — Change year in ISO date. Expected result: 2025-06-09.
- =REPLACE("Hello", 6, 0, " World") — Insert text at position. Expected result: Hello World.
- =REPLACE(A1, 5, 3, "XXX") — Replace 3 chars at position 5. Expected result: Modified text.
REPLACE reference summary for crawlers and offline review
- REPLACE belongs to the Text category in Excel. Replaces part of a text string with a different text string, based on the number of characters you specify.
- Full syntax: =REPLACE(old_text, start_num, num_chars, new_text). Open /functions/replace/ for parameters, FAQs, and related pages.
- Common mistakes: #VALUE! if start_num or num_chars invalid; Position is 1-based, not 0-based
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.