# Thread: Find a number as part of a formula

1. I need to check a lot of formulas to see if a value is being multiplied by 12. The formula is in an IF statement and looks like "B1415*12". I tried the FIND function but that requires text. How can I check a formula to see if the *12 is in it or not. If it is *8, for instance, it's an error. TYIA

2. Select Edit | Find... or press Ctrl+F.
Enter ~*12 in the "Find what" box. (The ~ tells Excel to treat * as a literal character instead of a wildcard.)
You may want to click Options >> and specify that you want to search the entire workbook in the Within dropdown.
Make sure that Formulas is selected in the Look in dropdown.
Click "Find Next" or "Find All".

3. [quote name='HansV' post='776193' date='20-May-2009 17:30']Select Edit | Find... or press Ctrl+F.
Enter ~*12 in the "Find what" box. (The ~ tells Excel to treat * as a literal character instead of a wildcard.)
You may want to click Options >> and specify that you want to search the entire workbook in the Within dropdown.
Make sure that Formulas is selected in the Look in dropdown.
Click "Find Next" or "Find All".[/quote]
Is there any way I can put this in a formula to return a value I can sort on? I'd like to have this in an audit column as a permanent part of the workbook. I want some cell to tell me "there's a problem on this row"-myself or someone else.

4. You could create a custom function in the Visual Basic Editor:

Code:
Function TestFormula(oCell As Range) As Boolean
If oCell.HasFormula Then
If InStr(oCell.Formula, "*12") > 0 Then
TestFormula = True
End If
End If
End Function
Let's say you have formulas in column A. In another cell, for example in B1, enter the formula

=TestFormula(A1)

and fill down.

Alternatively, use

=IF(TestFormula(A1),"Uh oh!", "")

5. [quote name='HansV' post='776196' date='20-May-2009 17:53']You could create a custom function in the Visual Basic Editor:

Code:
Function TestFormula(oCell As Range) As Boolean
If oCell.HasFormula Then
If InStr(oCell.Formula, "*12") > 0 Then
TestFormula = True
End If
End If
End Function
Let's say you have formulas in column A. In another cell, for example in B1, enter the formula

=TestFormula(A1)

and fill down.

Alternatively, use

=IF(TestFormula(A1),"Uh oh!", "")

That's great, thanks! The Alternative is more accurate evaluation.[/quote]

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•