Pinterest Pixel

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

Excel is a versatile spreadsheet software used in various industries and professions for data storage, management, and... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Top 3 Methods to Locate Cell that Contains Specific Text in Excel | MyExcelOnline Top 3 Methods to Locate Cell that Contains Specific Text in Excel | MyExcelOnline

Excel is a versatile spreadsheet software used in various industries and professions for data storage, management, and analysis. Among its multitude of applications, one of the common tasks is to identify cells that contains specific text.

Although there isn’t a dedicated in-built Excel formula, there have 3 different ways that can be used to check what a cell contains  –

Let us delve into these functions thoroughly.

Download the Excel Workbook below to follow along and understand how to locate cell that contains specific text in Excel –

download excel workbookExcel-IF-Cell-Contains.xlsx

 

Using Find Feature

By following the steps provided below, it is possible to effectively locate positive feedback by utilizing a find feature in Excel.

STEP 1: Select the range that contains all the feedback (i.e. Column B).
Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 2: Go to Home > Find & Select > Find. Or, simply press Ctrl + F to open the Find dialog box.

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 3: In the Find dialog box, enter the specific text you want to search. Here, it is the word “good”.

See also  The Ultimate Excel Resource Guide - Reward

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 4: Click on the Find All button to display a list of all the cells containing the specified text.

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

All the cells that contain specific word i.e. good are displayed.

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

 

SEARCH Function

The SEARCH function is a text function in Excel used to get the starting position of a specified text in a text string. If the text is found, it returns the position or else it returns an error.

The syntax of the SEARCH function is –

=SEARCH(find_text, within_text, [start_num])

where,

  • find_text: This is the text you want to find.
  • within_text: This is the text string in which you want to search.
  • start_num (optional): This specifies the character position from where the search should start.

You can combine SEARCH with ISNUMBER to get a boolean result i.e. TRUE and FALSE.

=ISNUMBER(SEARCH(find_text, within_text, [start_num]))

There can be 2 scenarios

  • Specified word is present – SEARCH will return the position and ISNUMBER will evaluate it as a number and return TRUE.
  • Specified word is not present – SEARCH will return an error and ISNUMBER will return FALSE.
See also  Your Free Excel Keyboard Shortcuts Template Is On The Way...

In this example, there are various products, and their feedback is mentioned in a table. You can easily find out the ones that are positive feedback (i.e. feedback contains the word “good”) using the SEARCH Function by following the steps below –

STEP 1: Enter the ISNUMBER function

=ISNUMBER(

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 2: Enter the SEARCH function.

=ISNUMBER(SEARCH(

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 3: Enter the first argument of the search function i.e. find_text. Here, it is the word “good”.

=ISNUMBER(SEARCH(“good”,

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 4: Enter the second argument of the search function i.e. within_text. Here, it is the cell B2.

=ISNUMBER(SEARCH(“good”,B2))

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 5: Copy the formula down.

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

 

COUNTIF Function

COUNTIF function is used to count the number of cells that match a specific condition. It can be employed to identify cells that contain the word “good”. Follow the steps below to understand how it can be done –

STEP 1: Enter the COUNTIF formula.

=COUNTIF(

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 2: Enter the first argument i.e. the range of cells you want to search within. Here, it is the cell B2.

See also  What Else Can Excel Pivot Tables Do?

=COUNTIF(B2,

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

STEP 3: Enter the second argument i.e. criteria that you want to check against. Here, it is “*good*”.

=COUNTIF(B2,”*good*”)

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

The asterisks (*) act as wildcard characters, allowing for any text before and after the word “good”. This means that the formula will count cells that contain the word “good” anywhere within their contents, regardless of the surrounding text.

 

STEP 4: Enter the greater than operator followed by 0.

=COUNTIF(B2,”*good*”)>0

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

If the count is greater than zero, it means there is at least one occurrence of the word “good”. The function will return TRUE else FALSE.

 

STEP 5: Copy the formula down.

Top 3 Methods to Locate Cell that Contains Specific Text in Excel

 

While Excel lacks a dedicated formula for identifying a cell that contains specific text, three methods are explored: utilizing the Find feature, combining the SEARCH function with ISNUMBER, and employing the COUNTIF function. Any one of these methods can be used to accomplish the desired result.

If you like this Excel tip, please share it
Top 3 Methods to Locate Cell that Contains Specific Text in Excel | MyExcelOnline Top 3 Methods to Locate Cell that Contains Specific Text in Excel | MyExcelOnline
See also  Data Cleansing Training - Clean & Extract Data Using Formulas & Excel's Analytical Tools
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...