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

Excel Replace Function

Excel Replace vs. Excel Substitute

The Excel Replace function is similar to the Excel Substitute Function. The difference between the two functions is:

  • The Replace function replaces text in a specified position of a supplied string;
  • The Substitute function replaces one or more instances of a given text string.
Related Function:
SUBSTITUTE

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:

old_text - The original text string, that you want to replace a part of.
start_num - The position, within old_text, of the first character that you want to replace.
num_chars - The number of characters to replace.
new_text - The replacement text.


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


Replace Function Examples

Column B of the following spreadsheet shows two examples of the Excel Replace Function.

 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

For further information and examples of the Excel Replace function, see the Microsoft Office website.


Replace Function Error

If you get an error from the Excel Replace function, this is likely to be the #VALUE! error:

Common Error
#VALUE! -

Occurs if either:

  • The supplied start_num argument is negative or is a non-numeric value
or
  • The supplied num_chars argument is negative or is non-numeric.


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, you may get unexpected results.

If you are not planning to use the date, time or number in further calculations, you could solve this problem by converting these values into text, using the Excel Text To Columns tool. To do this:

  • Use the mouse to select the cell(s) you want to convert to text (this must not span more than one column);
  • Excel Text To Columns Option Button
    From the Data tab of the Excel ribbon, select the Text to Columns ... option (see rightabove);
  • 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.

The Replace function should now work as expected on the values that have been converted to text.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions