The Excel Replace function replaces all or part of a text string with another string.
The syntax of the function is :
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 Microsoft Office website
The following spreadsheets show examples of use of the Excel Replace Function.
Further information and examples of the Excel Replace function can be found on the Microsoft Office website.
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.
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: