editing

Find and replace

Search the column you’re in, or the whole file, with capitals, whole words or regular expressions, and replace one match or all.

Ctrl+F⌘F opens the search bar at the top of the file, with the selected text already in it. Matches are highlighted as you type. Enter goes to the next, Shift+Enter⇧Enter to the previous (also F3 and Shift+F3⇧F3, or the next and previous buttons), and all selects every match, with a cursor on each. Esc closes the bar.

In the column

The search keeps to the column of the cursor: the option in column “city” is ticked when the bar opens, and says which column it is. Matches have to be inside a value of that column, and the header row is left out. Moving to the next match never takes you to another column; click in another column and the search goes there.

The search bar at the top of a file of cities, with united typed in it and in column “country” ticked; United is highlighted in the country column of three rows, United States twice and United Kingdom

Untick it to search the whole file. It stays unticked, the next times you open the bar, until you reload the page; then it starts ticked again.

Options

  • match case: capitals count.
  • regexp: the search is a regular expression, as in JavaScript: ^\s finds values that start with a space, \d{4}-\d{2} finds years and months. In the replacement, $1, $2… are the groups of the match, and $& the whole match.
  • by word: only whole words.

Replace

Ctrl+H⌘H opens the bar with the cursor in the replace field. replace changes the current match and goes to the next one; replace all changes every match, in the column or in the file, and is one step of Ctrl+Z⌘Z.

To change many values at once there are faster ways, too: select them with Ctrl+Shift+L⇧⌘L and type (see Multiple cursors), or use the Cells menu to trim spaces, change capitals or fill empty cells.