Check If Cell Contains Specific Text

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.

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))
Search Function
Search Function

Example#2

You can use the ISNUMBER function to provide more readable test results – instead of numbers. ISNUMBER returns TRUE is 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 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 name planets.

Named-ranges
Named-ranges

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 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.

SEE ALSO:  How to find out empty, blank cells or rows in LibreOffice Calc

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 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 returns #N/A error.

Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Related

Support Us


Be an expert on LibreOffice.

Small and quality tutorials. Delivered. Thrice a week.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top