The Excel REPLACE Function

Home » Excel-Built-In-Functions » Excel-Replace-Function

Search this site:

Custom Search

Related Function :

Substitute Function

Basic Description

The Excel REPLACE function replaces all or part of a text string with another string.

The format of the function is :

REPLACE( Old_text, Start_num, Num_Chars, New_text )

Where the arguments are as follows :

Old_text	-	The original text string to be searched for
Start_num	-	The number of the character in the search string from which to start replacing text
Num_chars	-	The number of characters to replace
New_text	-	The replacement text

The Excel Replace function is similar to the Excel Substitute Function, but the Replace function replaces text in a specified position of a supplied string, while the Substitute function replaces one or more instances of a given text string.

Note that the Excel Replace Function is not suitable for languages that use the double-byte character set (eg. Chinese, Japanese, Korean). These languages should use the ReplaceB function, which is explained on the official Microsoft website

Example

The following spreadsheets show examples of use of the Excel Replace Function. The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results.

Formulas
Example of use of the Excel Replace Function

Result

Common Problem:

The Excel Replace function is designed for use with text strings and returns a text string. Therefore, if you attempt to use the replace function with a date or a number, it will give you unexpected results.

If your initial value is in a cell and you want to convert it to text, you can use the Excel Text To Columns tool:

Use the mouse to select the cell(s) you want to convert to text (this must not span more than one column)
From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
Make sure the Delimited option is selected and click next
Make sure all the delimiter options are unselected and then click next again
You should now be offered a selection of Column Data Formats. Select Text and click the Finish button