Quick Navigation
REPLACE & REPLACEB Functions
Summary
The REPLACE function substitutes specific characters within a text string based on character position, while REPLACEB does the same using byte counts for double-byte character sets. REPLACE is the modern standard; REPLACEB is deprecated.
Syntax
REPLACE(old_text, start_num, num_chars, new_text)
REPLACEB(old_text, start_num, num_bytes, new_text)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| old_text | Text |
Yes | The original text containing characters to replace |
| start_num | Number |
Yes | Position to start replacement (1 = first character) |
| num_chars | Number |
Yes | Count of characters (REPLACE) or bytes (REPLACEB) to replace |
| new_text | Text |
Yes | New text to insert in place of removed characters |
Using the REPLACE, REPLACEB Function
REPLACE and REPLACEB excel at precise text editing by position rather than content search. Use REPLACE for standard Unicode text, REPLACEB only for legacy double-byte systems. Ideal for standardizing data formats, correcting known-position errors, or reformatting fixed-width data.
Common REPLACE, REPLACEB Examples
Basic Character Replacement
=REPLACE("abcdefghijk",6,5,"*")Replaces 5 characters starting at position 6 (fghij) with *: abcde*k
Year Update
=REPLACE("2009",3,2,"10")Changes 2009 to 2010 by replacing last two digits: 2010
Prefix Removal
=REPLACE("123456",1,3,"@")Replaces first 3 digits with @: @456
Phone Number Formatting
=REPLACE("5551234567",4,0,"-555-")Inserts dashes at specific positions: 555-555-1234567
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: start_num or num_chars are not numbers
Solution: Ensure position arguments are numeric
Nothing replaced
Cause: start_num exceeds text length
Solution: Verify start_num ≤ LEN(old_text)
Wrong position replaced
Cause: Using 0-based instead of 1-based indexing
Solution: Positions start at 1, not 0
Notes
- REPLACE counts Unicode characters properly in Compatibility Version 2+
- REPLACEB counts bytes (legacy DBCS support)
- num_chars=0 inserts without deleting
- Case-sensitive operation
- Supports cell references and formulas
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in: Excel 2003 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+