SUBSTITUTE and REPLACE functions in Excel

SUBSTITUTE swaps one piece of text for another wherever it appears; REPLACE swaps characters at a given position.

SUBSTITUTE is Find and Replace as a formula. It changes one piece of text into another, in every place it appears, and the result updates whenever the source cell changes. Use it to remove unwanted characters, to standardise spellings and to prepare text for conversion into numbers.

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

=REPLACE(old_text, start_num, num_chars, new_text)

Argument	What it is
`text`	The text to work on.
`old_text`	For SUBSTITUTE: the piece to find. Capital and small letters must match.
`new_text`	What to put in its place. Use "" to delete.
`instance_num`	Which occurrence to change: 1 for the first, 2 for the second. If left out, all are changed.
`start_num, num_chars`	For REPLACE: where to start and how many characters to replace.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=SUBSTITUTE(C2,"-","/")
Result: PB/10/1234
Every dash in PB-10-1234 becomes a slash.
=SUBSTITUTE(C2,"-","")
Result: PB101234
An empty replacement deletes: the code without dashes.
=SUBSTITUTE(C2,"-"," ",2)
Result: PB-10 1234
With an instance number, only the second dash changes.
=SUBSTITUTE(D2,",","")+0
Result: 1250
"1,250" stored as text: remove the comma and add zero to get a real number.
=SUBSTITUTE(D5," kg","")+0
Result: 12
Strip a unit from "12 kg" to leave the number.
=SUBSTITUTE(B3,"example.org","example.com")
Result: ravi@example.com
Change one domain name for another.
=SUBSTITUTE(A3,"ravi","Ravi")
Result: RAVI KUMAR
Nothing changes: SUBSTITUTE is case sensitive, and the cell holds RAVI in capitals.
=REPLACE(C2,1,2,"HR")
Result: HR-10-1234
REPLACE works by position: from character 1, replace 2 characters with HR.
=REPLACE(C2,7,4,"XXXX")
Result: PB-10-XXXX
Masking: the last four characters hidden.

SUBSTITUTE and REPLACE

The two names sound alike and work differently.

Function	Finds what to change by	Use it when
SUBSTITUTE	the text itself	you know what the characters are: every dash, every "Ltd."
REPLACE	position	you know where they are: characters 1 and 2, the last four

Most cleaning jobs want SUBSTITUTE.

Deleting characters

Give "" as the new text and the old text simply disappears. This is how to remove:

dashes and spaces from phone numbers: =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","");
commas from numbers imported as text;
units such as " kg" or "Rs ";
every space, which TRIM will not do.

To remove several different characters, nest one SUBSTITUTE inside another, as above. Each one handles a single character.

Turning text into numbers

A value such as "1,250" or "12 kg" is text and will not add up. Strip the offending characters and then convert. Adding zero, multiplying by one or wrapping in VALUE all turn the cleaned text into a number, as the fourth and fifth examples show.

One occurrence only

The fourth argument picks out a single occurrence. =SUBSTITUTE(A2,"-"," ",2) changes only the second dash. This is the key to a classic trick for finding the last occurrence of a character: count how many there are with LEN, then substitute that numbered occurrence with a marker that appears nowhere else, and FIND the marker.

It is case sensitive

SUBSTITUTE treats capital and small letters as different. "ravi" does not match "RAVI", as the seventh example shows. To replace regardless of case, convert first: =SUBSTITUTE(LOWER(A2),"ravi","Ravi"), bearing in mind that the whole result is then in small letters.

Counting with SUBSTITUTE

Deleting a character and measuring how much shorter the text became counts its occurrences: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")). See the LEN page.

REPLACE for masking and fixed layouts

REPLACE is right when the position is known. Hiding part of a number is the usual example: =REPLACE(A2,1,6,"XXXXXX") hides the first six characters of an account number. Giving 0 as the number of characters inserts text without removing any: =REPLACE(A2,3,0,"-") puts a dash after the second character.

The Find and Replace command

For a one-time change to the data itself, the Find and Replace box (Ctrl and H) is quicker than a formula. Use SUBSTITUTE when the cleaned result must keep following the source, or when you want to keep the original untouched.

Common mistakes

Expecting SUBSTITUTE to ignore case.
Wildcards. SUBSTITUTE takes * and ? literally. They are ordinary characters to it.
Forgetting that the result is text, even when it looks like a number.
Confusing the argument order of the two functions.

In Google Sheets

SUBSTITUTE and REPLACE are the same in Google Sheets. Sheets also has REGEXREPLACE for pattern-based replacement, which Excel 2021 does not have.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.