Asked by:
SharePoint 2007 List - Calculated Column

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
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 sameThursday, 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 GoyalFriday, October 14, 2011 7:38 PM