Excel Replace Function

Related Function:
SUBSTITUTE

Basic 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:

old_text - The original text string, that you want to replace a part of.
start_num - The position, within the old_text string, of the first character that you want to replace.
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


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.

 Formulas:
  A B
1 test string =REPLACE( A1, 7, 3, "X" )
2 second test string =REPLACE( A2, 8, 4, "XXX" )
 Results:
  A B
1 test string test sXng
2 second test string second XXX string

Further information and examples of the Excel Replace function can be found on the Microsoft Office website.


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:

  • 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
Return to the Excel Text Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net