locked
SharePoint 2007 List - Calculated Column RRS feed

  • Question

  • Hi everyone! So I've got a question regarding calculated columns.

    I'm trying to generate a calculated column that indicates Yes or No if another column contains certain keywords.

    Let's say I have 10 tasks in a list and Column A is a 'Single line of text'; in that column there are 5 tasks with the word sunshine. There may be other words beginning or ending in Column A, but somewhere in column A is the word sunshine. What formula could I use to make my calculated column B indicate Yes if sunshine is present?

    I've done a bit of searching and I'm finding a lot on if equals, but it doesn't seem to be working for me since column A won't equal what I'm looking for because it only contains it along with other words.

    Thanks!

    Thursday, October 6, 2011 2:36 PM

All replies

  •  

    Column1

    Formula

    Description (possible result)

     

     

     

     

     

     

    I have seen Sunshine Today

    =ISNUMBER(FIND("Sunshine",[Column1]))

    Determines whether Column1 contains Sunshine (Yes)

     

     

    More info at http://msdn.microsoft.com/en-us/library/bb862071.aspx

     

     

    • Marked as answer by iherndon Thursday, October 6, 2011 2:50 PM
    • Unmarked as answer by iherndon Monday, October 10, 2011 3:32 PM
    Thursday, October 6, 2011 2:47 PM
  • Perfect, thank you! After making that work it led me to another thought on the same topic. If I wanted to have it indicated 'Yes' for the letters Sun would it still be able to do that if the word was sunshine?
    Thursday, October 6, 2011 2:50 PM
  • Yes, That's the same
    Thursday, October 6, 2011 2:54 PM
  • Hmm I wonder if I missed something last week. For some reason my Yes/No calculated column doesn't appear to be working. Anything you can see that's off based on the screenshot?


    'Delayed?' is my calculated column.

    Any thoughts?
    • Edited by iherndon Monday, October 10, 2011 4:30 PM
    Monday, October 10, 2011 3:23 PM
  • Alright so it looks like it's only accepting lowercase as the formula. The screenshot above was actually taken after i'd switched the d from capital to lowercase. Is there a way for it to capture both uppercase and lowercase versions?
    Monday, October 10, 2011 5:55 PM
  • you can use search function which is not case sensitive

    =ISNUMBER(SEARCH("delay",[Keywords]))

    Also you can convert all to upper case before comparison

    =ISNUMBER(FIND("DELAY",UPPER([Keywords])))


    Shubham Goyal
    Friday, October 14, 2011 7:38 PM