This guide would help you to find out whether a cell contains specific text. This can be extended to multiple cells and give you flexibility on your calc operation.
We would use two Calc functions – SEARCH and FIND to do this.
Table of Contents
Using SEARCH to check if cell contains specific text [not case sensitive]
Syntax
=SEARCH(Find text, Text, Position); Returns the position of the Find text
- Find Text: the text you want to search
- Text: Where to search
- Position [optional]: Starting position of the search
Example#1
In the below example, we search for the substring in the Text column. Using SEARCH function, it returns the position of the occurrence of the search string.
=ISNUMBER(SEARCH(C5,B5))
Example#2
You can use the ISNUMBER function to provide more readable test results – instead of numbers. ISNUMBER returns TRUE if a valid number is found, i.e. the search is successful.
Example#3
You can also easily do it with named ranges. So that you don’t need to type out cell values for all. Named ranges are a way to give a name to any range of cells, and you can refer to that range across the workbook using that name.
In the above example, let’s give a name to the text column. Select the range and press CTRL+F3 – OR, from Menu – Sheet -> Named Ranges and Expressions -> Define. On the Defined name window, give the name planets.
Now, change the formula below. So, you get the same result, however, your formula is more readable and you can use this range anywhere in the workbook.
=ISNUMBER(SEARCH(C5,planets))
Using FIND to check if a cell contains specific text [case sensitive]
The FIND function does exactly the same what SEARCH does, however – it is case sensitive. Remember SEARCH function is not case-sensitive.
Syntax
=FIND (Find text, Text, Position); Returns the position of the Find text
- Find Text: the text you want to search
- Text: Where to search
- Position [optional]: Starting position of the search
Example
In the same example above, if you use the FIND function, you can see the case-sensitive result. The Substring “Pluto” C8 is case sensitive whereas “mars” C5 is not case sensitive.
=ISNUMBER(FIND(C5,B5))
Usage Notes
- Both SEARCH and FIND returns #VALUE! Error when there is no match.
- For invalid ranges, SEARCH and FIND return #N/A error.
I hope this clarifies the concept. Do let me know in the comment box if you have any questions.
