Thread: Finding text in an equation

I want to find a text string within another cell. I can do this easily using an IF function and a FIND function if the other cell contains a simple text string. However, this doesn't work if the text I'm trying to find is embedded in an equation.

For example, I want to find the text "Sheet1" in another cell that has an equation =Sheet1!A7. When it finds the text there, then I want the result TRUE. If it doesn't find the text then I want FALSE.

Any ideas?!?

One option is to use a so called User Defined Function. In other words, your make your own function.

1. Press Alt+F11 and Select from the menu: Insert, Module
2. Copy and paste the below in that module:
Function GetFormula(Cell as Range) as String
GetFormula = Cell.Formula
End Function
3. Close the VBA window and go back to your Spreadsheet and start use the new formula (UDF). I.e =FIND("Sheet";GetFormula)

Hope it helped
//Ola

Thanks for the tip. I was hoping I would be able to avoid VBA. The people who will be using the spreadsheet have a bit of a VBA phobia! Is there any chance that there's an existing Excel function that will do the same job? If not, then I think I will do some sort of less elegant work-around!

Thanks again, Helen

Thanks for the other option se1429! I copied the code into a new module. Then closed and went back to Excel. I put the formula "=Sheet2!C2" into cell A1. Then in cell A2 I put the formula "=FoundInFormula("Sheet",A1)" and it returned the result TRUE. Perfect!

The only funny thing I've found with it is that it's case sensitive so if I type "sheet" instead of "Sheet" then it returns FALSE. But for what I need it for that's no problem.

Thanks so much! Helen

The only funny thing I've found with it is that it's case sensitive ...

A minor change:

Function FoundInFormula(sText As String, ByVal Target As Range) As Boolean
    With Target
        If .HasFormula Then FoundInFormula = InStr(1, .Formula, sText, vbTextCompare) > 0
    End With
End Function