Function Reference: REPLACE
| Syntax | ||
| =REPLACE(old_text,start_num,num_chars,new_text) | ||
| Purpose / Description | ||
| Returns a text string in which the original text has had specified characters replaced with new text. | ||
| Arguments | ||
| Name | Type | Description |
| old_text | text string | A constant, reference or nested expression that returns the text within which the replacement is to be made. |
| start_num | number | A constant, reference or nested expression that returns the starting position within old_text at which the replacement is to occur, counting from 1 for the first character. |
| num_chars | number | A constant, reference or nested expression that returns the number of characters starting from start_num (inclusively) that are to be replaced with the new text. |
| new_text | text string | The new text that is to replace the specified character positions within old_text. |
| Return Values | ||
| Type | Specific Value | Description |
| text string | A text string based on the text string passed to old_text, where the number of characters have been replaced with the text string passed to new_text. If the value of start_num > the count of characters in old_text, then new_text is appended to old_text. if start_num <= count of characters in old_text but start_num + num_chars -1 > the count of characters in old_text, everything from the start_num position is replaced with new_text. |
|
| error | #VALUE! | If the values passed to start_num or num_chars are not numbers and cannot be coerced to numbers. If the values passed to start_num < 1 or >= 32,768 If the value passed to num_chars is < 0 or >= 32,768 |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | Text strings returned are limited to 32,767 characters, the limit to the length of a text string stored in a worksheet cell. | |
| Comment: | Text can be inserted into a text string, by using the num_chars value 0. No characters are removed, but the new text is added commencing at the position in start_num. | |
| Comment: | Text can be removed from a text string, by setting the number of characters to be replaced in old_text and setting new_text to an empty string (""). | |
| Examples: | Open / download sample workbook: SUBSTITUTE, REPLACE.xlsx | |