Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

If formula

0

Help!  What is wrong with my formula??? 

=IF(ISNUMBER(SEARCH("aft",A1)),24,C1)

So I have $50 in C1 and A1 says aft 234 but it keeps showing 50 instead of 24

I'm looking for a formula that says if the cell contains "aft" at all then it's 24 but if it doesn't then it's the original number that is entered in a different cell (C1) (I got that part down cause 50 keeps showing up). Why does 24 not show up when A1 says "aft 234", it contains aft!  Can't figure it out🤨!

Thanks!

Ann

Answer
Discuss
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(35% Sale Ends Jan. 26)

Answers

0

Ango

Your formula should do what you describe (it does for me) so I can only think of two possibilities:

1) The Microsoft guidance on SEARCH, SEARCHB functions says "These functions may not be available in all languages." (but it certainly works in English). I tested that by changing SEARCH to SEARCH2 (which doesn't exist) and with that unknown function, the formula behaves as you say.

2) If you don't need a case-insensitive search (e.g. if the cells will always contain "aft" rather than "Aft") then you could try:

=IFERROR(IF(FIND("aft",A1)>0,24), C1)
where the FIND(...) >0 test would produce a #VALUE! error if the "aft" wasn't found and C1 would be the result).

Hope this helps.

Discuss

Discussion

Hi Ango,

I use Excel 2007 and both your formula and John's work fine, and produce the results you are wanting. Double check your formula in your worksheet to make sure it is indentical to the one you posted in this forum.
WillieD24 (rep: 120) Nov 18, '21 at 10:14 am
Did that work, Ango?
John_Ru (rep: 2857) Nov 29, '21 at 6:51 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login