Function Reference: SUBSTITUTE
| Syntax | ||
| =SUBSTITUTE(text,old_text,new_text,[instance_num]) | ||
| Purpose / Description | ||
| Returns a text string within which one or more instances of a sub string have been replaced with new text. | ||
| Arguments | ||
| Name | Type | Description |
| text | text string | A constant, reference or nested expression that returns the text within which the replacement(s) is to be undertaken. |
| old_text | text string | A constant, reference or nested expression that returns the sub string within the text argument which is to be replaced. |
| new_text | text string | A constant, reference or nested expression that returns the new text which is to replace the nominated instance(s) of old_text within text. |
| [instance_num] | number | An optional constant, reference or nested expression that returns the instance of old_text that is to be replaced. Any decimal portion of the number is ignored. If omitted, all instances found are replaced. |
| Return Values | ||
| Type | Specific Value | Description |
| text string | A text string based on the text string passed to the text argument within which one or more instances of old_text have been replaced with new_text, if the nominated instance is found. If the value given is > number of instances, no replacement occurs. | |
| error | #VALUE! | If the value passed to instance_num is not a number and cannot be coerced to number. If the value of instance_num < 1 |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | The length of the text string returned will have no more than 32,767 characters, the limit of a text string within a worksheet cell. | |
| Comment: | This function is very useful in a range of data cleansing tasks. | |
| Examples: | Open / download sample workbook: SUBSTITUTE, REPLACE.xlsx | |