Excel - Microsoft Excel Replace Function

Description

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

The syntax of the function is : 

REPLACE( old_text, start_num, num_chars, new_text ) 

Where the function arguments are: 

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. 

Replace Function Examples

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. 

Common Problem - Use of the Excel Replace Function with Numbers, Dates and Times

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, time or a number, it will give you unexpected results. 

One solution to this problem is to convert the date, time or number into text, before applying the Replace function to it. This can be done using the Excel Text To Columns tool: