Excel offers two main formulas for replacing text: REPLACE changes characters at a specified position, while SUBSTITUTE swaps matching text wherever it appears.
The examples below show how to choose between them and handle common cases such as repeated matches, case sensitivity, and unwanted spaces.
Choose REPLACE or SUBSTITUTE
Both functions return a modified text value without changing the original cell.
Use REPLACE when you know where the characters to change begin.
Use SUBSTITUTE when you know the text to find, regardless of its position.
REPLACEworks by character position.
It is useful for changing a fixed section of an ID, phone number, or code.SUBSTITUTEworks by matching text.
It is useful for changing a word, delimiter, or character such as a hyphen.
For example, if A2 contains AB-1234, a position-based replacement can change the first two characters, while a text-based replacement can remove the hyphen wherever it occurs.
REPLACE formula syntax
The syntax is REPLACE(old_text, start_num, num_chars, new_text). old_text is the source text, start_num is the character position to begin at, num_chars is the number of characters to replace, and new_text is what to insert.
Positions start at 1.
Suppose A2 contains AB-1234.
To replace the first two characters with XY, enter:
=REPLACE(A2,1,2,"XY")The result is XY-1234.
To change the four digits, starting at character 4, to 5678, use =REPLACE(A2,4,4,"5678"), which returns AB-5678.
To insert text without removing existing characters, set num_chars to 0.
For example, =REPLACE(A2,4,0,"NEW-") returns AB-NEW-1234.
This is handy when the insertion point is fixed.
SUBSTITUTE formula syntax
The syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]).
The first three arguments identify the source, text to find, and replacement.
The optional instance_num limits the change to a particular occurrence.
If omitted, every matching occurrence is replaced.
If A2 contains red-blue-red, this formula replaces every instance of red:
=SUBSTITUTE(A2,"red","green")The result is green-blue-green.
To replace only the second occurrence, use =SUBSTITUTE(A2,"red","green",2).
The result is red-blue-green.
To remove a character, use an empty string as the replacement.
For example, =SUBSTITUTE(A2,"-","") removes every hyphen from AB-1234, returning AB1234.
Replace text in a range with a formula
To update values in a column while preserving the originals, put the formula in an adjacent column and fill it down.
If A2:A100 contains product codes and you need to change each hyphen to a slash, enter this in B2:
=SUBSTITUTE(A2,"-","/")Copy or fill the formula through B100.
Each row refers to its corresponding cell in column A.
If you later need the results without formulas, copy the output range and use Paste Special, then Values.
Keep the original column until you have checked the replacements.
Replace text using a value from another cell
Place the text to find in D2 and its replacement in E2.
Then use =SUBSTITUTE(A2,D2,E2).
This separates the replacement terms from the formula, making them easier to change or reuse.
For a positional replacement, you can also put the starting position, character count, or replacement text in cells and reference those cells in REPLACE.
Cell references are especially useful for larger lists of substitutions.
For several different search terms, a single SUBSTITUTE can be nested inside another, such as =SUBSTITUTE(SUBSTITUTE(A2,"old","new"),"draft","final").
The formula applies the inner replacement first, then the outer one.
For long mapping tables or many replacement rules, Power Query or a lookup-driven approach may be easier to maintain.
Case sensitivity and matching behavior
SUBSTITUTE is case-sensitive.
If A2 contains Excel excel EXCEL, =SUBSTITUTE(A2,"excel","Sheets") changes only the lowercase match.
It does not replace Excel or EXCEL.
If capitalization varies, normalize the source with LOWER or UPPER before replacing, but note that this also changes the case of the entire result.
Neither function treats wildcard characters as search patterns.
In SUBSTITUTE, an asterisk is treated as a literal character, not “any text.” These formulas also work with text values; if a cell contains a number, Excel may convert it to text for the replacement.
Formatting such as leading zeros may not be preserved as part of the underlying value, so codes with leading zeros are best stored as text.
Excel and Google Sheets compatibility
REPLACE and SUBSTITUTE are available in current desktop versions of Excel, Excel for the web, and Google Sheets.
Their core syntax is the same across these spreadsheet apps.
Both return text, so a result that looks numeric may need conversion if you intend to use it in arithmetic.
For example, wrap a suitable result in VALUE when it contains only a number in a format your locale recognizes.
Common errors and edge cases
- Wrong character position:
REPLACEcounts from 1, not 0.
Check the text carefully, especially when spaces or punctuation are present. - Unexpected repeated changes:
SUBSTITUTEreplaces all matches unless you supplyinstance_num. - Text not found: If
SUBSTITUTEcannot findold_text, it returns the original text unchanged.
Check for extra spaces, different capitalization, or a different dash character. - Empty search text: An empty
old_textcan produce confusing results.
Check that the search cell is not blank when using cell references. - Extra spaces: A space is a character and can affect both the position in
REPLACEand the match inSUBSTITUTE.
UseTRIMwhen you need to remove excess ordinary spaces, bearing in mind that it does not remove every kind of whitespace.
When a replacement does not look right, compare the source value and search text character by character.
Hidden spaces, nonbreaking spaces, and typographic dashes can look similar on screen while remaining different characters to the formula.