Table of contents
1.
Introduction
2.
Check if Cells Contain Specific Text
2.1.
Using COUNTIF Function
2.2.
Using ISNUMBER Function
2.2.1.
CASE-SENSITIVE
2.2.2.
CASE-INSENSITIVE
2.3.
Using IF Function
2.3.1.
IF with COUNTIF Function
2.3.2.
IF with ISNUMBER Function
2.3.3.
IF with OR Function
2.4.
Using SUMPRODUCT Function
3.
FAQs
4.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

Contains Specific Text

Author Sneha Mallik
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

When working with a large database, we may need to find a specific text. Excel includes numerous tools that can help us achieve this quickly. The blog provides some formulas to check if a cell has a specified text and return TRUE or FALSE and explanations of the arguments and how the formulas function.

Check if Cells Contain Specific Text

In Excel, conditions may require determining whether or not a cell contains a specific text. We'll understand how to check if a cell has specified text today in the following 4 methods. 

First and foremost, let's learn about the dataset that will serve as the foundation for our examples.

Using COUNTIF Function

We will use a formula that will be based on the COUNTIF function to see if a value or text exists in a set of data.

  • Consider the case where the 'Sentence' column contains a variety of distinct types of texts. We are given specific definite texts in a column called 'Specific Text', which we must find from the 'Sentence' column and show our results in the 'Output' column. This section will use the COUNTIF formula to complete this task.
  • Now in cell ‘C2’, we will apply the COUNTIF function. The formula for the COUNTIF function is,
=COUNTIF(range, value)>0

We will now insert the values into the function and our final form of the function is,

=COUNTIF(A2:A6,’’*”&B2&”*”)>0

Where,

  • Range is A2:A6
  • "*"&B2&"*" is the criterion. The asterisk (*) Is used as a Wildcard for one or more characters in this case. The asterisk has been concatenated before and after the cell reference B2; therefore, it will now be counted as a substring. As a result, if the value appears anywhere in the range, it will be counted.
  • The result is TRUE if the value is found; otherwise, the result is FALSE.

 

Hover your mouse cursor over the formula cell's bottom right corner, and when the Fill handle icon (+) appears, double-click it to apply the same formula to the rest of the cells.

If we add some specific texts to the Sentence column that do not already exist, the formula will return FALSE.

Using ISNUMBER Function

We can find a certain text from a range of cells using the formula based on the (ISNUMBER and FIND) and (ISNUMBER and SEARCH).

CASE-SENSITIVE

Generic Formula:

=ISNUMBER(FIND(substring, text))

Arguments:

Substring: The substring is the text we wish to look for in the cell.

Text: The cell or text string that we wish to see if it contains the specific text (the argument substring).

Return Value:

The result of this formula is a logical value. The formula returns TRUE or FALSE depending on whether the cell contains the substring.

Working:

If you want to see if cell A6 has the text in cell B6, apply the formula below.

=ISNUMBER(FIND(B6, A6))

 

CASE-INSENSITIVE

Generic Formula:

=ISNUMBER(SEARCH(substring, text))

Arguments:

Substring: The substring is the text we wish to look for in the cell.

Text: The cell or text string that we wish to see if it contains the specific text (the argument substring).

Return Value:

The result of this formula is a logical value. The formula returns TRUE or FALSE depending on whether the cell contains the substring.

Working:

If you want to see if cell A3 has the text in cell B3, apply the formula below.

=ISNUMBER(SEARCH(B3, A3))

 

Using IF Function

Whenever we need to find specific texts from a range of cells, we can use the IF function to accomplish it quickly. We may make our job easier by nesting other functions within the IF function.

IF with COUNTIF Function

We will apply the IF formula with the COUNTIF formula to a cell where we want the result.

This formula's final form is:

=IF(COUNTIF(A2:A6,”*”&B2”*”), ”YES”, ”NO”)

Where,

  • The range is A2:A6.
  • "*"&B2&"*" is the criterion. 
  • The result will show "YES" if the value is found.
  • The result will show "NO" if the value is not found.

 

Output:

IF with ISNUMBER Function

We will apply the IF formula with the ISNUMBER function to cell C2, where we want the result.

This formula's final form is:

=IF(ISNUMBER(SEARCH(B2, A2)), ”FOUND”, ”NOT FOUND”)

Where,

  • We will find the text B2 in A2 using the SEARCH function
  • The result will show "FOUND" if the value is found.
  • The result will show "NOT FOUND" if the value is not found.

 

Output:

IF with OR Function

The IF with OR function formula can also be used to find a certain text from a range of cells. In cell E4, we now use the IF with OR formula. 

This formula's final form is:

=IF(OR(COUNTIF(A2,”*”&$B$2:$B$6&”*”)), ”YES”, ”NOT FOUND”)

Where,

  • The range is A2.
  • ”*”&$B$2:$B$6&”*” is the criterion. 
  • The result will show "YES" if the value is found.
  • The result will show "NOT FOUND" if the value is not found.

 

Output:

Using SUMPRODUCT Function

We can use the SUMPRODUCT function to discover specific texts in a group of cells. To learn, follow the below instructions.

In cell C2, use the SUMPRODUCT function. The COUNTIF function is nested within the SUMPRODUCT function in this case. 

The final formula is as follows:

=SUMPRODUCT(COUNTIF(A2:A6,”*”&B2&”*”))>0

Where,

  • The range is A2:A6.
  • ”*”&B2&”*” is the criterion. 
  • The COUNTIF function counts the number of cells that have been matched.
  • The COUNTIF method returns a number, which the SUMPRODUCT function sums together.

 

Output:

FAQs

1. What is MS Excel?

Microsoft Excel is a spreadsheet-based software for analyzing and storing statistical and numerical data. Microsoft Excel has various tools, such as graphing tools, mathematical operators, macro programming, etc. 

A spreadsheet in Excel is made up of rows and columns. The alphabets are called columns, while the numerals are known as rows.

2. What are the benefits of using Microsoft Excel?

We may construct a visual representation of data and information using MS Excel. We can use bar charts, column charts, and graphs to display the data visually. When the data is changed, it immediately updates the charts and graphs.

3. Where does MS Excel come into play?

Excel is a spreadsheet application that can organize data and do financial analysis. It is utilized in all business functions and by companies of all sizes.

4. If cell A1 contains text, which method returns true??

If Cell A1 contains text, we can use the IF and FIND functions in Excel to return TRUE. The formula to return TRUE is as follows.

=IF(ISNUMBER(FIND(“Your_Text”, A1, 1)), TRUE, FALSE)

5. If cell A1 contains a text value, which function returns true??

If cell A1 contains a text value, we can use the Excel IF function with the FIND function to return TRUE. The method to return TRUE based on the text value is shown below.

=IF(ISNUMBER(FIND(“Your_Text_Value”, A1, 1)), TRUE, FALSE)

 

Key Takeaways

In this blog, we learned the concepts of specific text in Excel. Microsoft Excel is a spreadsheet-based software for analyzing and storing statistical and numerical data. Microsoft Excel has various tools, such as graphing tools, mathematical operators, macro programming, and so on. 

Refer here to read more about Introduction to ExcelWorksheets in Excel and Must have Excel Skills.

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Credits: GIPHY

Happy Learning!

Live masterclass