Search the string of a formula

Question

Is there a function similar to Search that can search within formulas, rather than the displayed cell contents? e.g. being able to find 'myWorksheet' within ='myWorksheet'!$A$1. I need an actual formula, rather than using Show Formulas.

I can make one in VBA easily enough but curious to know if there's one ready made and my Google-Fu has let me down.

Answer 1

There is no Excel worksheet function that does that. Both Find() and Search() will evaluate the value of a cell, not its underlying formula.

The Find/Replace command (Ctrl-F or Ctrl-H) has the option to search in formulas, but it will only find the cell with the search term, not the position of the search term within the cell.

Answer 2

Since teylyn has confirmed no built in function exists I created my own which I'll share here in case it's of use to anyone. Place it in a code module of a macro enabled workbook and use it in the same way as Search. It will search the cell formula if it has one or the cell value if not and return a -1 if no match is found:

Function SearchFormula(rng As Range, val As String) As Integer

Dim f As String
Dim pos As Integer

f = rng.Formula

If f <> "" Then
    pos = InStr(1, f, val)
    If pos = 0 Then pos = -1
Else
    pos = -1
End If

SearchFormula = pos

End Function