Hi Andrew,
Quote:
Originally Posted by adj1
Comment: You have to put in the full text of the column being used to sort on, it doesn't seem to like using only the first letter or word, but that's ok as long as it's not too long, though it gets a bit tedious when there are many diffferent texts to deal with. Now if only one could add several text choices into one column - e.g. IF either "yellow" or "blue" then green column acquires the value of the associated cell.....
|
It's possible to do partial matches too. For example, to see if A1 contains the word "blue" you can use:
Code:
=IF(ISNUMBER(SEARCH("blue",A1)),B1,0)
You can then extend it for multiple words. To see if A1 contains the words "blue" or "yellow" you can use:
Code:
=IF(OR(ISNUMBER(SEARCH({"blue","yellow"},A1))),B1,0)
To see if a cell contains "red", "blue" or "orange" you can use:
Code:
=IF(OR(ISNUMBER(SEARCH({"red","blue","orange"},A1))),B1,0)
If you want to put the list of colours in a range and reference them (rather than using an array constant {.....}) then you have to use something slightly different - let me know if you want to do this and I'll show you how.
Note - SEARCH() is not case sensitive. If you want the formula to be case sensitive then use FIND() instead of SEARCH().