Finding Formulas Containing a Specific Function

Hilary has formulas scattered throughout a workbook that contain a particular function. She wants to replace that function with a different approach, but the first step is to locate the formulas she needs to change. Hilary wonders how she can locate every formula containing a specific function without also finding cells that merely contain the function's name as text.

If you only need to find specific functions once in a while, then you can use Find and Replace to do the searching. Let's say, for instance, that you want to find formulas that contain the GAMMA function. You can follow these steps:

1. Press Ctrl+F. Excel displays the Find tab of the Find and Replace dialog box.
2. Click the Options button, if necessary, to enlarge the dialog box. (See Figure 1.)



Figure 1. The Find tab of the Find and Replace dialog box.

3. In the Find What box, enter this: GAMMA(
4. Make sure the Look In drop-down list is set to Formulas.
5. Click Find Next.

At this point, Excel selects the first cell that includes the GAMMA function. It is important to use the opening parenthesis in step 4, as this will help ensure that text-only mentions of GAMMA are skipped.

If you need to repeat the same process regularly, then your best bet may be to create a macro to assist. All you need to do is have the macro ask for the function to be located, and then it can do the rest. Here's an example:

Sub FindTextInFormulas()
    Dim c As Range
    Dim searchText As String
    Dim searchRange As Range
    Dim numFound As Long
    Dim sMsg As String

    searchText = InputBox("Enter the function name to find in formulas:")
    If Len(searchText) > 0 Then
        searchText = Ucase(searchText) & "("

        On Error Resume Next
        Set searchRange = Selection.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If searchRange Is Nothing Then
            sMsg = "No formulas found in the selected range"
        Else
            numFound = 0
            For Each c In searchRange
                If InStr(1, c.Formula, searchText, vbBinaryCompare) > 0 Then
                    c.Interior.Color = vbYellow
                    numFound = numFound + 1
                End If
            Next c
            sMsg = "No matches found"
            If numFound > 0 Then
                sMsg = "Found " & numFound & " cells with that function"
            End If
        End If

        MsgBox sMsg, vbInformation
    End If
End Sub

To use the macro, select the cells you want to check and then run it. The only thing the macro changes is the color fill of any cells that contain the text you want. Notice that the macro also converts the user-entered text to uppercase and appends an opening parenthesis to the string. When combined with setting the fourth parameter of the InStr function to vbBinaryCompare, this helps ensure that there are very few "false positives" in what the macro does.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (14001) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.