FORMULA.REPLACE

Macro Sheets Only

Equivalent to choosing the Replace command from 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 Functions

FORMULA.FIND Finds text in a workbook

List of Command-Equivalent Functions