REPLACE Function

The REPLACE function replaces text at a known location inside a text string. Unlike SUBSTITUTE, which replaces text by matching content, REPLACE works by position and character count. REPLACE is ideal in cases where the text to replace can't easily be matched, but the location is predictable.

Key features

• Works by position, not by matching text content
• Is not case-sensitive and does not support wildcards
• Useful when the text to replace has many variations
• Will accept an empty string ("") to remove text completely
• Works in all versions of Excel

You can't use the REPLACE function by itself to "find and replace" text — it works at a more granular level and swaps text based on position only. For basic find and replace tasks, see the SUBSTITUTE function. For true pattern matching, see the REGEXREPLACE function (Excel 365).

Table of contents

• Example #1 - Code examples
• Example #2 - Replace different text at same location
• Example #3 - Change first letter
• Example #4 - Remove first characters
• Example #5 - Conditionally remove text
• Example #6 - Capitalized first letter
• Example #7 - Mask credit card numbers
• Related functions

Example #1 - Code examples

REPLACE function takes four separate arguments in a generic syntax like this:

=REPLACE(old_text,start_num,num_chars,new_text)

The first argument, old_text, is the text string to be processed. The second argument, start_num, specifies the numeric position where replacement should begin. The third argument, num_chars, indicates how many characters to replace. The final argument, new_text, provides the replacement text. You can see how these arguments work in the formulas below:

=REPLACE("C:\docs",1,1,"D") // returns "D:\docs"
=REPLACE("ABC123",4,3,"456") // returns "ABC456"
=REPLACE("XYZ",1,1,"") // returns "YZ"
=REPLACE("www.google.com",1,4,"") // returns "google.com"

1. Replace "C" at the start of the path with "D".
2. Replace 3 characters starting at the 4th character ("123" to "456").
3. Replace the "X" at the start with nothing ("X" to "").
4. Remove the first 4 characters ("www." to "")

Example #2 - Replace different text at same location

In the example below, the goal is to replace the year values in the middle of the text strings in column B with the year 2025. This is a scenario where the REPLACE function shines. Instead of matching the various year values (as with a function like SUBSTITUTE), we can simply tell replace to replace 4 characters starting at character 5. The formula in cell D5. copied down, looks like this:

=REPLACE(B5,5,4,"2025")

Example #3 - Change first letter

In the worksheet below, the goal is to change the first letter of each path in column B to the letter "Z". This is another good use case for the REPLACE function because the text we want to change is always fixed at character one, even though it is different in each case. The formula in D5 copied down is:

=REPLACE(B5,1,1,"Z")

Notice this is a spill range example. Because we give the REPLACE function 12 values in the range B5:B16 for old_text, it returns 12 results that spill into the range D5:D16. 

Example #4 - Remove first characters

The REPLACE function can be used to remove text by providing an empty string ("") for the new_text argument. In the example below, the goal is to strip the "www." from each domain name in column B. The formula in cell D5, copied down, looks like this:

=REPLACE(B5,1,4,"")

Notice we use 1 for start_num and 4 for num_chars, since the text string "www." contains 4 characters and always starts at character 1. You may need to change these inputs depending on your use case.

Example #5 - Conditionally remove text

In the example below, we have the same problem as above, except the www isn't always present. This means we need to check for the presence of these characters before we remove them. Otherwise, we'll remove characters we don't want to remove. There are a variety of ways to do this in Excel. In the formula below, we're using the LEFT function inside the IF function to perform this check. The formula in cell D5 looks like this:

=IF(LEFT(B5,4)="www.",REPLACE(B5,1,4,""),B5)

Note: In a current version of Excel, you can also use the TEXTAFTER function to solve this problem and the previous problem.

Example #6 - Capitalized first letter

It is possible to combine the REPLACE function with other functions that do more sophisticated text manipulation. For example, in the worksheet below, we use the REPLACE function together with the UPPER and LEFT functions to capitalize the text strings in column B. The formula in cell D5 copied down is:

=REPLACE(B5,1,1,UPPER(LEFT(B5)))

This page explains this formula in more detail and provides some alternatives.

Mask credit card numbers

In the worksheet below, the goal is to mask credit card numbers so that only the last four digits are visible. This is accomplished by combining the REPLACE function with the LEN and REPT functions in a clever formula like this in cell D5:

=REPLACE(B5,1,LEN(B5)-4,REPT("*",LEN(B5)-4))

The start number is always 1. To work out the number of characters to replace, we calculate the length of the text string, then subtract 4 to account for the numbers we don't want to replace: LEN(B5)-4. To generate the replacement text, we use the REPT function configured to repeat an asterisk (*) once for each number we are replacing, like this: REPT("*",LEN(B5)-4).

Related functions

Excel contains several functions that can help you find and replace text:

• REPLACE – Replace text by position when you know the starting point.
• FIND / SEARCH – Find the numeric position of text.
• SUBSTITUTE - replace text with simple matching.
• REGEXREPLACE – Pattern‑based find and replace (Excel 365 only).

Notes

• To remove text, use an empty string ("") for new_text.
• REPLACE returns #VALUE is start_num or num_chars is not a positive number.
• REPLACE works on numbers, but the result is text.