How to Search Text in Cells in Google Sheets

146

This tip is about the how to Search Text in Cells in Google Sheets. So read this free guide, How to Search Text in Cells in Google Sheets step by step. If you have query related to same article you may contact us.

How to Search Text in Cells in Google Sheets – Guide

Databases are best for joining huge amounts of data in different tables. You can list your product details in one table, list your orders in another, and link each order to purchased products. So instead of entering the price every time, you would link the product name in the order table to the product detail page and let the database extract the data for itself. This interconnectedness makes databases more confusing – but also more powerful when you have time to tame the beast.

Spreadsheets are not as interconnected as databases. They are designed to list dates in one place and calculate SUM and AVERAGE. They can also search your data and find related values ​​- just a little different than in databases. The LOOKUP function is more flexible when your data is sorted—but the SLOOKUP or HLOOKUP functions are more likely to get the results you want because they specify exactly how you want to search your table. Let’s try. For example, let’s say you have a product table like the one shown above, with columns for the item name, price, quantity, and description.

Search using the Find option to highlight all matching cells in the spreadsheet

If you have text cells and you want to quickly discover cells that contain specific text strings, you can do so using the find option in Google Sheets. Suppose you have a dataset as shown below and you want to discover all the cells that have the name ‘Mark’ in them:

Below are steps to search Google Sheets using the find option:

Open the spreadsheet containing the data

Use the keyboard shortcut Control + F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right of the spreadsheet.

Enter the string you want to search across the entire spreadsheet

The steps above would highlight all cells that have the corresponding text string. If you want to scroll through each of the cells one by one, you can use the up and down arrow in the Find field. You can also visually scan the result as all matching cells are highlighted in green color.

Search using Find and Replace

Let’s assume that we have the following data in a single spreadsheet: The dataset shown is very simple and you can obviously find what you need just by looking at it. However, we kept it simple to make the tutorial easy to understand. Now, let’s say we want to find the cells that contain the name “Paul”. Here are the steps you need to follow to find them:

Click on the Edit menu in the menu bar.

Select the ‘Find and Replace’ option.

This will open the Find and Replace dialog. Alternatively, you can open this box directly by pressing CTRL + H (if you are on a PC) or Cmd + H (if you are on a Mac).

In the input box next to the “Find” label, type the word you want to search for (in our example, we can type the word “Paulo” as that’s what we’re looking for).

Click Find.

This will select the first cell containing the word.

If there are more occurrences of the word in the spreadsheet, clicking Find each time selects the next cell containing the word.

When you get to the last occurrence of the word, you’ll see a message that says “No more results found. Looping around ” at the bottom of the dialog.

If you press the Find button button after seeing this message, Find and Replace will return to the first occurrence of the word.

Once you’ve finished searching (and/or replacing), press the ‘Done’ button button to close the Find and Replace dialog.

Find and Replace Options

If you take a look at the Find and Replace dialog, you will see that there are many other options besides just Find, Replace and Search.

Case-sensitive: Selecting this option allows you to make your search case-sensitive. So if you have a cell that contains a “paul” (with a lowercase ‘p’), the search will skip the cell.

Match entire cell content: Selecting this option allows you to search for cells that exactly match your search word. For example, if this box is checked and your search word is “Paul Rodriguez”, the search will only consider the cell that contains the exact full name as a match. However, if the search word is just “Paul”, the search will ignore the full name as it is not an exact match.

This option is used if you have a regular expression in the “Find” field. A regular expression is a kind of string that contains a specific pattern. If the content of a cell fits the pattern, it is considered a ‘match’. Additional explanations on Regular Expressions are beyond the scope of this tutorial, but if you want to know more about it, you can click on the ‘Help’ link next to the option:

Also Search in Formulas: In general, Find and Replace only searches cell contents and formula results. If you also want to see if the search word is contained in the actual cell formula, you need to check this option.

Search using conditional formatting (to find and highlight cells with search string)

The method above allows you to find cells containing a search string and select them one by one. Instead, if you want to search and highlight all cells that contain the search word, you can use Google Sheets conditional formatting feature.

So, for the same dataset, if you want to search for all cells that contain the name “Paulo”, follow these steps:

Click the Format menu on the menu bar.

Select the ‘Conditional Formatting’ option.

This will open the ‘Conditional Formatting Rules’ sidebar on the right side of the window.

In the input box under “Apply to range”, enter the range of cells you want to search or simply select the range of cells. In our example, we can type A2:B11.

Selecting the range of cells will open a dialog displaying the range you selected. When finished, you can click OK. If you prefer to manually enter the range, you can skip this step.

Then, in the Formatting Rules section, under “Format cells if”, click the drop-down arrow.

From the drop-down list that appears, select the “Text contains” option.

You will see an input box below the drop-down list. Enter your search word here. If you are looking for cells that contain the word “Paulo”, type the word “Paulo”

Under “Formatting Style”, click on the fill color button ().

Select the color you want to use to highlight corresponding cells/rows. We selected “yellow”.

Finally, click Done. button, to allow conditional formatting to do its work.

Final note

I hope you like the guide How to Search Text in Cells in Google Sheets. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.