Subsection 2.4
Find and Replace with regex
Reading
Sometimes the dirt is not in one cell — it is everywhere. Every name in the column has a trailing period. Every phone uses dots instead of dashes. For these, you reach for Find and Replace with regular expressions.
Regular expressions look frightening. For the patterns you will face, you only need four.
The four regex patterns you actually need
| Pattern | What it matches | Example use |
|---|---|---|
\s+$ | One or more whitespace characters at the end of a cell. | Strip trailing whitespace across an entire column. |
^\s+ | One or more whitespace characters at the start of a cell. | Strip leading whitespace. |
\. | A literal dot. | Replace dots in phones with dashes. |
[()\s-] | Any parenthesis, space, or dash. | Strip all phone formatting before standardizing. |
How to use it in Sheets:
- Select the column you want to clean.
- Edit › Find and replace (or Cmd/Ctrl + Shift + H).
- Check Search using regular expressions.
- Enter the pattern in the Find box and the replacement in the Replace box.
- Click Replace all.
Always run on a copy first
Regex Find and Replace affects the entire selection at once. Run it on a duplicate sheet (right-click the tab · Duplicate) before you trust it on the source roster. The Undo stack only goes so far.
Action: Mark this page complete when you have finished the activity above.