IF(OR(ISNUMBER(SEARCH Help Request

[dparteka]

I’m using the formula below that works well; I’ve been unsuccessfully trying to slightly alter it. I’m looking for the whole formula which resides in J42 to do the following.

If F42 has the text “Angle” then G42
If F42 has the text “Surface Finish” then G42/40
If F42 has anything other then the above text then G42*25.4

=IF(OR(ISNUMBER(SEARCH("Surface Finish",F42)),ISNUMBER(SEARCH("Angle",F42))),G42,G42*25.4))

 

[prasaddn]

Hi,

I hope you would not have F42 with both Angle and Surface finish together.

here is what I suggest as formula:
=IF(IFERROR(SEARCH("Angle",F42),0)>0,G42,IF(IFERROR(SEARCH("Surface Finish",F42),0)>0,(G42/40),G42*25.4))

REgards,
Prasad DN

 

[dparteka]

Hi there Prasad DN... thank you for the formula… "Angle" & "Surface Finish" never appear together in F42.

I'm a little embarrassed to say this but we are in the process of upgrading but are currently still using Excel 2003. Your formula returns a #NAME?, would it be possible to use ISERROR, I did try that but the formula has to many arguments... Dennis

 

[Somendra Misra]

HI,

Can you try:

=IF(ISNUMBER(SEARCH("Surface Finish",F42)),G42/40,IF(ISNUMBER(SEARCH("Angle",F42)),G42,G42*25.4))

Regards,

 

[dparteka]

SM... pretty cool, it works perfectly... maybe someday I'll be that smart... thank you so much, Dennis

 

[Khalid NGO]

Hi,
This simple one too:
=IF(F42="Angle",G42,IF(F42="Surface Finish",G42/40,G42*25.4))

But it will not search specific words between strings. e.g. if your text = abcdAngle...

Regards,

 

[dparteka]

Thank you Khalid NGO