Find and replace in spreadsheets

If you're simply looking to find something within a spreadsheet, press Ctrl + F (⌘ + F on a Mac) and type a word or phrase you'd like to find. As you type, your search term will be highlighted everywhere it appears in the currently active sheet. If multiple results are found in your document, you can jump to the next one by pressing the down arrow in the find bar, or by pressing Enter. Pressing Shift + Enter, or clicking the up arrow, will take you to the previous result.

If you're looking for a more powerful way to search, use the find and replace tool, which lets you find certain terms within a spreadsheet and gives you the option to replace those terms with other terms. To get started, go to the Edit menu and select Find and replace. (You can also press Ctrl + H, or ⌘ + Shift + H on a Mac.) Then, type the word in the text box next to 'Find,' and click Find.

Google Docs find and replace dialog box

Or you can use one of these options:

  • Match case: Select this to make your search case-senstive.
  • Match entire cell contents: Select this to search for cells that match your search term exactly. With this option checked, searching for "do," for example, will not return a cell in your spreadsheet containing "dog."
  • Search all sheets: Select this to search across all the sheets you're using, not just the sheet you're currently on.
  • Search using regular expressions: Select this to use advanced searching capabilities, allowing you to search for cells that match a pattern.
In the new version of Google Sheets, you'll notice a drop-down menu in the find and replace tool. In addition to searching the current sheet or all sheets, you can alternatively specify which range of cells you want to search through by selecting Specific range and adding a range to search. In the new Google Sheets, you can also find and replace items inside of formulas (instead of only values) by checking the box next to "Search within formulas" in the find and replace tool.

How to replace terms

  • To replace a word or expression in your document, type the text you'd like to replace in the box next to 'Replace with' and then click the Replace button.
  • If you'd like to replace all the selected words, click the Replace all button.

Using regular expressions to search for data in your spreadsheet

Regular expressions provide a way to search for strings of text, including particular characters, numbers, words, or patterns of characters. Regular expressions are particularly useful in pattern matching, as these searches are not restricted to a specific search term. Instead, searches return patterns that match the expression specified.

To use regular expressions in the Find and Replace function, type the expression into the Find box and select the "Search using regular expressions" checkbox.

Terms used in regular expressions

NOTE: The table below shows a sample of just some of the expressions that Google Docs supports. There are, however, many other supported expressions users can employ. Additionally, some of the examples listed below depend on the terms of the Find and Replace query. For instance, examples will behave differently if Match case is unchecked. Examples provided below demonstrate results when Match case is checked, and Match entire cell contents is not checked.

Expression Description Example Matches Does not match
. A period signifies any character in the given position. d. do, dog, dg, ads fog, jog
* An asterisk after a character signifies a search for that preceding character repeated 0 or more times. do*g dog, dg, dooog dOg, doug
+ A plus after a character signifies a search for that character displayed 1 or more times. do+g dog, dooog dg, dOg, doug
? The previous expression is optional. do?g dg, dog dOg, doug
^ A caret must be placed at the beginning of a regular expression and signifies that the string starts with the character(s) or sequence placed after the caret. ^[dh]og dog, hog A dog, his hog
$ A dollar sign must be placed at the end of a regular expression and signifies that the string ends with the character(s) or sequence placed before the dollar sign. [dh]og$ dog, hog, hot dog dogs, hog, doggy
{A, B} The previous expression is repeated between A and B times, where A and B are numbers. d(o{1,2})g dog, doog dg, dooog, dOg
[x], [xa], [xa5] A character set indicates that just one of the given character(s) should occur in the current position. For the most part, any characters are valid within brackets, including characters mentioned previously in expressions: [xa,$5Gg.] d[ou]g dog, dug dg, dOg, dooog
[a-z] A character set range signifies a search for a character within the given range of characters. Common ranges include a-z, A-Z, and 0-9. Ranges can be combined into a single range: [a-zA-Z0-9]. Ranges can also be combined with character sets (mentioned previously): [a-zA-Z,&*]. d[o-u]g dog, dug, dpg, drg dg, dOg, dag
[^a-fDEF] A character set beginning with a ^ signifies a search for a character that is not within the given set. d[^aeu]g dog, dOg, dig, d$g dg, dag, deg, dug
\s Any white space character. d\sg d g, d[TAB]g dg, dog, doug

NOTE: When trying to search for actual instances of any character that has a specific meaning in regular expressions, like ^ and $, you need to "escape" the character in your search query by placing a backslash in front of it. For example, if you wanted to search for an instance of the $ character, you'd write \$.

Below are a couple of examples of how regular expressions could be used to search a spreadsheet:

Search for cells that contain dollar amounts

Enter the following in the Find bar: ^\$([0-9,]+)?[.][0-9]+

This signifies a dollar amount where the first number is any number 0-9 or comma occurring zero or more times, followed by [.], followed by any number 0-9 repeated one or more times. This search would return any of the following: $4.666, $17.86, $7.76, $.54, $900,001.00, $523,877,231.56

Search for cells containing US zip codes

Enter the following into the Find bar: [0-9]{5}(-[0-9]{4})?

This signifies a U.S. zip code consisting of five numbers with an optional hyphen and four-digit add-on.

Search for cells containing names beginning with a lowercase letter

Enter the following into the Find bar: ^[a-z].*

This signifies a cell input that contains a lowercase letter followed by another character 0 or more times. This search would return any of the following: bob, jim, gEORGE, marTin