Explanation
The FORMULATEXT is fully automatic. When given the reference of a cell that contains a formula, it will return the entire formula as text. In the example as shown, the formula:
=FORMULATEXT(C5)
returns the text "=IF(B5>=70,"Pass","Fail")".
Dealing with errors
The FORMULATEXT function will return the #N/A error when a cell does not contain a formula. To trap this error and display nothing when a cell does not contain a formula, you can use the IFERROR function like this:
=IFERROR(FORMULATEXT(A1),"")
Alternately, you can use ISFORMULA and IF like this:
=IF(ISFORMULA(A1),FORMULATEXT(A1),"")
Checking for specific text
To check a formula for a specific text, you can use the ISNUMBER and SEARCH functions. In the formula below, we are checking a formula in A1 to see if it contains "apple":
=ISNUMBER(SEARCH("apple",FORMULATEXT(A1)))
The result is either TRUE or FALSE. See this page for a full explanation.
Related functions
FORMULATEXT Function
The Excel FORMULATEXT function returns a formula as a text string from a given reference. You can use FORMULATEXT to extract the formula as text from a cell. If you use FORMULATEXT on a cell that doesn't contain a formula, it returns #N/A.
IFERROR Function
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
ISFORMULA Function
The Excel ISFORMULA function returns TRUE if a cell contains a formula, and FALSE if not. When a cell contains a formula ISFORMULA will return TRUE regardless of the formula's output or error conditions.
