Text in formula (2007)

Topic

New Reply

Have a column which may or may not contain the word “lunch” when it’s completed – need a formula that looks in a cell in that column and if the cell contains the word “lunch”, puts the value of another cell in the cell containing the formula.

Help me! Can’t believe this can be difficult – as usual – but I can’t figure it out.

Thanks again

Aunt Linda

Reply | Quote

Replies

Enter the following formula in E1:

=IF(ISERROR(SEARCH("lunch",A1)),"",B1)

and fill down as far as needed. SEARCH returns the position of the word “lunch” in A1 if found, or an error if the text is not found. ISERROR returns TRUE if SEARCH returns an error (i.e. if the text is not found), FALSE otherwise. IF returns either an empty string or the value of B1 depending on the TRUE/FALSE value.

Reply | Quote

Apparently I forgot to mention the words that may or may not contain the word “lunch” are the result of a dropdown box – I’m guessing that’s why this formula won’t work – correct????

Grrrrrrrrrrrrrrrrrrr

Reply | Quote

That’s not the problem – of course – attaching the actual portion of the worksheet this time – not a pretend version . . .

Reply | Quote

I suspect that you attached the same workbook as in the first post – I don’t see any difference.

Reply | Quote

Yep – may you live in interesting times!

Reply | Quote

You didn’t tell us what you want in this workbook, so I’m guessing:

– Enter the following formula in G4:

=IF(ISERROR(SEARCH("lunch",C4)),"",F4)

– Fill down to G10.

This is the same formula I posted before, but with different cell references, of course.

Reply | Quote

Perfect – thank you again.

Reply | Quote

Obviously this – again – is not my workbook – I’m doing someone else a favor who couldn’t possibly join the lounge and post this herself –

After all your help – she now tells me she needs a formula in H4 that will subtract the lunch minutes from the Time in column D – I thought this one would be a snap but – see attached.

One more time, please?

Reply | Quote

Change the formula in F4 to

=IF(ISERROR(SEARCH("lunch",C4)),0,D4)

It now returns 0 instead of an empty string if “lunch” doesn’t occur in column C, so that you can use it in calculations.

Reply | Quote

Thank you – again. Perfect.

Aunt Linda

Reply | Quote

Jeez! Can you tell I only have 13 more working days until I retire????

Obviously this – again – is not my workbook – I’m doing someone else a favor who couldn’t possibly join the lounge and post this herself –

After all your help – she now tells me she needs a formula in H4 that will subtract the lunch minutes from the Time in column D – I thought this one would be a snap but – see attached.

One more time, please?

Reply | Quote

>> Can you tell I only have 13 more working days until I retire????

And I hope that your retirement is everything you have hoped for and then some.

Reply | Quote