The View menu ‘Show formulae’ (Ctrl+`) in Docs Sheets shows the formulas in a Sheet (try it, if not tried already). What about getting a formula without changing the Spreadsheet View mode? Using the FORMULATEXT function, you can extract a formula from a cell as a text in Google Sheets.

The normal way (the manual way that without using a formula) to extract a formula from a cell is as follows.

1. Click the cell containing the formula to copy. This will open/show the formula in the Formula bar. You can copy the formula from there.
2. Another method is by double-clicking or pressing F2 in a cell and highlighting the formula and then copy it.

You can paste value (right-click on any cell to see paste value option) such copied formula in a cell. But this method has one disadvantage. This pasted text formula won’t reflect any changes that you make in the original formula later.

If you use the FORMULATEXT function to get the formula text from a cell, the returned formula text will be updated based on any future edit on the source formula.

Google Sheets FORMULATEXT Function – Syntax and Example

Syntax:

FORMULATEXT(cell)

Argument:

cell – the cell reference from which to get the formula as text.

Google Sheets Formula to Return Formula from a Cell as a Text String

See the following example.

The formula in cell C2 is =A2*A2. You can get that formula as a text in cell D2 using the formula =FORMULATEXT(C2).

This way you can extract formula from a cell as a text string in Google Sheets. Is there any benefit of using this formula extractor function in Google Sheets?

Yes! Here are some real-life examples or you can say benefits.

The Benefits of Using the Formulatext Function to Get Formula Text in Google Sheets

Here are some of the benefits of this Lookup function (yes, Formulatext is categorized under Lookup) in Google Sheets.

• To see multiple formulas at a time: You can only see the active cell’s formula on the Formula bar (if you single click) or within the same cell (if you double click). The Formulatext function in Google Sheets help you get the formula in a different cell. Also, using this function you can see several formulas at a time.

• To test whether a cell contains a formula: The Formulatext function will return an N/A error if the ‘cell’ doesn’t contain a formula. Please note that there is already a function dedicated to this – How to Use the Function ISFORMULA in Google Sheets.
• In Conditional formatting: You can highlight cells containing specific function using this function. I will detail that in another tutorial and link to that here later.

Update: How to Highlight Cells Containing Specific Function in Google Sheets.

Removing the = Sign from an Extracted Formula in Google Sheets

OK. Now you know how to get formulas from cells as text strings in Google Sheets. What about removing the equal sign from the returned formula?

Using Regexextract you can remove the =sign in front of an extracted formula string.

=REGEXEXTRACT(FORMULATEXT(C2),"^.(.*)$")

The function FORMULATEXT is not an array function. So if you use it in an array/range, the formula would only return the formula text from the very first cell of the range.