Find a number as part of a formula

Topic

New Reply

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

Reply | Quote

Replies

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”.

Reply | Quote

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”.

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.

Reply | Quote

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!”, “”)

Reply | Quote

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.

Reply | Quote