Equivalent to clicking the Replace command on the Edit menu. Finds and replaces characters in cells on your worksheet.
Syntax
FORMULA.REPLACE(find_text, replace_text, look_at, look_by, active_cell, match_case)
FORMULA.REPLACE?(find_text, replace_text, look_at, look_by, active_cell, match_case)
Find_text is the text you want to find. You can use the wildcard characters, question mark (?) and asterisk (*), in find_text. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Replace_text is the text you want to replace find_text with.
Look_at is a number specifying whether you want find_text to match the entire contents of a cell or any string of matching characters.
| Look_at | Looks for find_text |
| 1 or omitted | As the entire contents of a cell |
| 2 | As part of the contents of a cell |
Look_by is a number specifying whether to search horizontally (through rows) or vertically (through columns).
| Look_by | Looks for find_text |
| 1 or omitted | By rows |
| 2 | By columns |
Active_cell is a logical value specifying the cells in which find_text is to be replaced.
If active_cell is TRUE, find_text is replaced in the active cell only.
If active_cell is FALSE, find_text is replaced in the entire selection, or, if the selection is a single cell, in the entire sheet.
Match_case is a logical value corresponding to the Match Case check box in the Replace dialog box. If match_case is TRUE, Microsoft Excel selects the check box; if FALSE, Microsoft Excel clears the check box. If match_case is omitted, the status of the check box is unchanged.
Remarks
In FORMULA.REPLACE?, the dialog-box form of the function, omitted arguments are assumed to be the same arguments used in the previous replace operation. If there was no previous replace operation, omitted text arguments are assumed to be "" (empty text).
The result of FORMULA.REPLACE must be a valid cell entry. For example, you cannot replace "=" with "= =" at the beginning of a formula.
If more than a single cell is selected before you use FORMULA.REPLACE, only the selected cells are searched.
Related Function
FORMULA.FIND Finds text in a workbook
Return to index