Can Excel filter cells based on formula text rather than formula results?

Question

I had a colleague ask me yesterday if it was possible to set an auto-filter to display columns based on the text of formulas in the cells instead of their calculated results.

For example: if I had formulas =D3*3, =D3*4 and =D3*5, I'd want to filter based on the *3, *4 and *5 parts of the formulas.

Is this possible? From the choices in the UI, it seems that my colleague may need to use VBA to solve the problem.

Answer 1

to my knowledge there is no built in function that will return the formula contained in a cell.

The simplest solution is to create a small helper function in vba

Function CellFormula(r As Range) As Variant
    CellFormula = r.Cells.Formula
End Function

and put this in a column next to the data, and include that column in the filter range. Then the formula strings are available for filtering

Note: if you have array formulas in your sheet and want to identify that in the result, the helper function will need some modification