ExcelInsider Team
Sometimes you have a big list of things in Google Sheets, and you need to find a word or phrase inside that list. You may check if something is there, find rows that match, or just want to highlight something important.
This article will show you a few easy ways to search for text in various cells. You’ll learn how to use simple tools and formulas to find precisely what you’re looking for, whether it’s an exact word, part of a word, or anything close.
Steps to search for text in a range:
➤ Open your Google Sheets and press Ctrl + F (or Cmd + F on Mac)
➤ A small search bar will appear in the top-right corner of the sheet
➤ Type the word or phrase you want to find (e.g., Completed)
➤ Use the up and down arrows in the search box to jump between results
➤ Each match will be highlighted in yellow as you scroll through
Use COUNTIF Function to Check If Text Exists in a Range
The COUNTIF function counts how many times a specific word or value appears in a range of cells. It’s a simple way to check if something exists in a column or row by returning how many matches it finds.
We’ll use a sample dataset from a sheet named Task Tracker. The sheet contains columns for Task ID, Status, and Notes. We’ll focus on the Status column to check whether the word “Completed” appears within it.
Steps:
➤ In a blank cell, enter the formula:
=COUNTIF(B2:B20, “Completed”)>0
➤ This returns TRUE if the word “Completed” appears anywhere in the range B2:B20.
➤ Replace “Completed” with any text you want to search for.
➤ Change the range as needed.
This method works well for quick yes/no checks.
Use FILTER Function to Return All Rows That Contain a Specific Text
The FILTER function pulls out rows that meet certain conditions. In this case, it lets you return only the rows where a specific column contains the text you’re looking for. This is useful when you want to see just the matching data without changing the original sheet.
Steps:
➤ In a blank area, enter:
=FILTER(A2:B20, B2:B20 = “Open”)
➤ This returns all rows where column A has the exact value “Open”
To allow partial matching (e.g. contains “open” anywhere in the text), use:
=FILTER(A2:B20, REGEXMATCH(B2:B20, “open”))
Use SEARCH Function to Look for Partial Matches in Each Cell
The SEARCH function checks if a certain piece of text appears anywhere inside another cell’s content. It’s great for finding partial matches because it doesn’t need the whole cell to match. Just part of it is enough.
Steps:
➤ Enter the formula:
=ARRAYFORMULA(IFERROR(SEARCH(“urgent”, C2:C11) > 0, FALSE))
➤ This will return TRUE for each row in column C where “urgent” appears in the text
This is great for identifying partial string matches in a large column.
Highlight Matching Text Using Conditional Formatting
Conditional formatting lets you change how cells look based on their content. You can use it to highlight any cell that contains certain text, which makes it easier to spot matches at a glance.
Steps:
➤ Select the range (e.g., A2:C11)
➤ Go to Format >> Conditional formatting
➤ Under Format cells if, choose Text contains
➤ Type the word or phrase you want to highlight (e.g., “Delayed”)
➤ Set a fill colour
➤ Click Done
This is perfect for visually scanning sheets without formulas.
Use the Find Tool for Quick Text Lookups
If you just want to find a specific word or phrase quickly:
Steps:
➤ Press Ctrl + F on Windows (Cmd + F on Mac)
➤ Type your search term
➤ Use the arrow keys in the pop-up to scroll through each match in the sheet
Note:
This doesn’t give formula-based results as it’s just for manual lookups.
Frequently Asked Questions
How do I check if a cell contains a specific word in Google Sheets?
The easiest way is to use a COUNTIF formula.
➤ Try this: =COUNTIF(A2:A20, “Completed”)
This tells you how many times “Completed” shows up in that range.
➤ Want a simple yes or no instead? Just add > 0 at the end: =COUNTIF(A2:A20, “Completed”) > 0
That’ll return TRUE if the word is found, and FALSE if it isn’t. Quick and to the point.
Can I search for partial matches in a cell, not just exact words?
Yes, you can. If the word you’re after might be part of a longer sentence, like “urgent” inside “This is an urgent request”, then use SEARCH or REGEXMATCH. Those functions are great when the exact position or phrasing of the word isn’t predictable.
How do I filter rows that contain a specific keyword in a column?
You’ll want the FILTER function along with REGEXMATCH.
For example: =FILTER(A2:C20, REGEXMATCH(C2:C20, “urgent”))
That formula will return just the rows where column C includes the word “urgent.” Perfect when you’re digging through a big list and need to pull out only the important bits.
Is there a way to highlight cells that contain certain words automatically?
Definitely. Google Sheets has built-in Conditional Formatting for that.
➤ Click Format, then choose Conditional formatting
➤ Pick “Text contains” as the condition
➤ Enter the word you’re looking for
Any cells that match will be highlighted based on the colour you choose. It’s a helpful way to make key words stand out without scanning line by line.
What’s the fastest way to manually search for a word in Google Sheets?
Just press Ctrl + F (or Cmd + F on a Mac).
➤ A small search bar will pop up
➤ Start typing, and Google Sheets will highlight every match in the sheet
It’s simple, quick, and great when finding something fast without setting up a formula.
Wrapping Up
There are a few solid ways to search for text in Google Sheets, depending on your needs. Ctrl + F is the quickest way if you’re in a rush. Need something more visual? Conditional formatting works well. And if you’re handling data regularly, using formulas like COUNTIF, SEARCH, or FILTER will give you more control and flexibility.
It really depends on how deep you want to go. Keep it simple for quick checks, and use the tools for bigger jobs.
