0

How to Search for Text in Cells in Google Sheets

Posted by Arch on October 9, 2019
Google Sheets Search for Text in Cell

Editing text in Google Sheets is just as easy as in Word, Excel, or any other similar program. Google’s web-based application can handle all standard editing and search functions.

Finding specific text and replacing it in a spreadsheet is the easy part. If you want to learn how to do that and more, check out the following tips on how to manipulate spreadsheets, as far as text data is concerned.

Use the Classic Search Feature

Unless you haven’t used a computer before, you should be well aware of the classic Ctrl + F search function. You can use this in your spreadsheet to find a specific word, name, object, or phrase you want to highlight.

find replace

In Google Spreadsheets you might want to use Ctrl + H to bring up the ‘Find and replace’ feature. Here’s an alternative method in case your keyboard has issues.

  1. Open a spreadsheet from Google Drive.
  2. Go to a sheet tab you want to search.
  3. Click on ‘Edit’.
  4. Select ‘Find and replace’ from the Edit dropdown menu.
  5. Type the text you want to search for.
  6. You can also type in new text in the ‘Replace with’ box if you want to replace the original text.
  7. Click on ‘Find’ multiple times if there’s more than one result to scroll through all of them.
  8. Click on ‘Done’ or the X icon to exit the search window.

How to Search for Text on Android

  1. Go to ‘Settings’.
  2. Select Apps.
  3. Tap the Google Sheets icon to launch the app.
  4. Log in, if it doesn’t automatically authenticate you.
  5. Scroll through the spreadsheet list until you find the one you want.
  6. Tap the three-dot icon from the top right corner of the screen.
  7. Tap the ‘Find and Replace’ function.
  8. Tap on the search box that will pop up at the header.
  9. Type in a word and tap the search key to find that word in the spreadsheet.
  10. Use the arrow heads to navigate through the list of results.

How to Change the Highlight Color

Once you’ve located the text you were looking for, you can highlight it in whatever color you want in order to spot it easier when reading the spreadsheet.

  1. Click the cell with the text.
  2. Click the bucket icon from the toolbar.
  3. Select a color you want.
  4. Click anywhere outside the selected cell.

Note that this will only work for highlighted cells, not all cells found containing the search term.

Replacing Existing Text with New Text

You can also use the SUBSTITUTE () function. This function has a simple formula which allows you to replace existing text from one cell in a string with new text. The function has the following formula: “SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])”.

Here’s an example that uses this function to replace http with https, assuming you have http in column A, row three.

Type ‘=SUBSTITUTE(A3,”http”, “https”)

More Tips on Manipulating Spreadsheet Text

Here are a few more tips you may find interesting if you want to learn how to manipulate text lines in Google Sheets, the easy way.

Cell Line Breaks

Normally, when you press enter after typing you won’t be able to break the line. You have to force a line break to make the text look natural and fit the cell. Press Ctrl + Enter or Alt + Enter.

Turn Off Gridlines

Turning off gridlines in a spreadsheet will give the sheet a document-like appearance by erasing the lines defining the cells. Here’s how you can do this:

  1. Open a spreadsheet.
  2. Click the ‘View’ menu button on the toolbar.
  3. Find the ‘Gridlines’ option.
  4. Uncheck it to remove gridlines from your sheet.

Calendar example with gridlines

with gridlines

Calendar example without gridlines

without gridlines

Note that this won’t make the line separators between row numbers or column letters disappear. You’ll still be able to see where everything is supposed to go.

Add Drawings

Spreadsheets are known for their text, numbers, and charts. But, did you know that you can also add free-hand drawings to a Google spreadsheet? Something to keep in mind if you want to make your spreadsheet more unique and personal. Or if you want to highlight very specific portions of the sheet.

  1. Open a spreadsheet.
  2. Click on the ‘Insert’ menu button on the toolbar.
  3. Click on the ‘Drawing’ submenu icon, next to the pointer icon.
  4. Select the line tool, first tool in the dropdown menu.
  5. Select the ‘Scribble’ option.
  6. Draw whatever you want on the screen.
  7. Click the ‘Save and Close’ button to add the drawing.

You can also move the drawing and resize it after saving it and closing the drawing tool. What’s cool is that your drawing won’t have to adhere to the same boundaries as text or numbers.

Any drawing you make will be able to go beyond cell limits, past the regular delimitations of cells. Think of it as a watermark you can add anywhere you want on screen on its own unique layer.

Just note that if you use the free-hand drawing tool to add text, that text won’t be recognized by the search function, no matter how well you nail the font.

Google Sheets – Free-for-All Version of Excel

Now that you know just how easy Google Sheets is to use, do you think you’ll use it more often to organize to-do lists, plan events, compare prices, or other kinds of data?

Do you think that given what Google Sheets allows you to do, using Excel for anything less than corporate tasks is worth it anymore? Let us know in the comments section below; share some of your tips on how to make your life even easier when manipulating spreadsheets.

Leave a Reply

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


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.