Finding text in an equation

[HelenStrand]

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?!?

[olasa]

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

[se1429]

Hi,

Another option:

Create the following function and use it directly without the need to use the FIND function.

Please Login or Register  to view this content.

This function accepts two arguments: the text to look for and the cell reference where the formula is.

If you need help on how to create a user defined function see this link:

http://www.exceldigest.com/myblog/20...ined-function/

[HelenStrand]

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

[HelenStrand]

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

[shg]

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

A minor change:

Please Login or Register  to view this content.