Thursday, 21 January 2010

How to sort rows on the basis of whether they contain a specific word/string

Lets say you have a glossary in 2 columns (A and B) and you want to check all the cells that refer you to another headword with the string see x. or βλέπε or even a specific character like a parenthesis. Let's say you want to check for instances of this string on column B with all the rows that contain the given string.

1. In column C, header row (C1), enter the phrase/string/character to find.
2. In C2 enter =ISNUMBER(SEARCH(C$1; B2)) and then drag C2 all the way down (if that gives you an error try with this: =ISNUMBER(SEARCH(C$1, B2)) ).
3. If you dragged, you will see that column C cells change to TRUE or FALSE.
4. Sort on column C.
5. Go to the bottom where you will most likely find all the cells that contain the phrase/string/character.

If you want to check for a term on column A, use this instead:

=ISNUMBER(SEARCH(C$1; A2)) or =ISNUMBER(SEARCH(C$1, A2))

No comments: