Exceljet

Quick, clean, and to the point

Show formula text with formula

Excel formula: Show formula text with formula
Generic formula 
Summary 

To show the text of one formula with another formula, you can use the FORMULATEXT function. In the example shown, the formula in D5, copied down, is:

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 show, the formula:

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:

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.