Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text in formula (2007)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in formula (2007)

    Enter the following formula in E1:
    <code>
    =IF(ISERROR(SEARCH("lunch",A1)),"",B1)
    </code>
    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    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

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    That's not the problem - of course - attaching the actual portion of the worksheet this time - not a pretend version . . .
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in formula (2007)

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

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    Yep - may you live in interesting times!
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in formula (2007)

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

    - Enter the following formula in G4:
    <code>
    =IF(ISERROR(SEARCH("lunch",C4)),"",F4)
    </code>
    - Fill down to G10.

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

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    Perfect - thank you again.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    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?
    Attached Files Attached Files

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    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?
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Text in formula (2007)

    Change the formula in F4 to
    <code>
    =IF(ISERROR(SEARCH("lunch",C4)),0,D4)
    </code>
    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.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in formula (2007)

    Thank you - again. Perfect.

    Aunt Linda

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text in formula (2007)

    >> 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. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    Regards
    Don

Posting Permissions

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