Regex for Office Users: Find and Replace Like a Pro

📎 This article includes 1 downloadable practice file ↓

Regular expressions (regex) are pattern-matching strings that let you find, validate, and transform text with surgical precision. While most Excel users know basic Find and Replace, regex in Notepad++, VS Code, Google Sheets, and Python can automate text tasks that would otherwise take hours of manual editing — making it an essential skill for data professionals.

In this article

1. What is Regex? The Core Concepts
2. Practical Office Use Cases
3. Notepad++ Find and Replace (Ctrl+H, enable Regular expression)
4. Google Sheets: REGEXEXTRACT, REGEXMATCH, REGEXREPLACE
5. Expert: Regex in Python for Bulk Data Cleaning
6. Continue learning

What is Regex? The Core Concepts

A regex pattern describes what text to match using special characters. Think of it as a mini programming language for describing text patterns. Here are the fundamental building blocks:

Symbol	Meaning	Pattern Example	Matches
.	Any single character	c.t	cat, cut, cot, c3t
*	Zero or more of previous	ca*t	ct, cat, caat, caaat
+	One or more of previous	ca+t	cat, caat (not ct)
?	Zero or one of previous	colou?r	color, colour
^	Start of line	^Hello	Lines starting with Hello
$	End of line	world$	Lines ending with world
d	Any digit 0-9	d{4}	2024, 1234
w	Word character (a-z,A-Z,0-9,_)	w+	hello, world123
s	Whitespace (space/tab/newline)	s+	spaces, tabs
[abc]	One of these characters	[aeiou]	any vowel
(abc)	Capture group	(d{4})-(d{2})	year-month parts

Practical Office Use Cases

Notepad++ Find and Replace (Ctrl+H, enable Regular expression)

# Remove all blank lines from a document
Search:  ^s*

Replace: (empty)

# Reformat dates from DD/MM/YYYY to YYYY-MM-DD
Search:  (d{2})/(d{2})/(d{4})
Replace: $3-$2-$1
Before:  15/11/2024    After: 2024-11-15

# Extract only the numbers from lines like "Invoice #1234 - Amount: $5,678"
Search:  [^0-9]+
Replace: (space)
Result:  isolates all numbers

# Remove HTML tags from pasted web content
Search:  ]+>
Replace: (empty)

# Clean up extra spaces (more than one space between words)
Search:  [ ]{2,}
Replace: (single space)

Google Sheets: REGEXEXTRACT, REGEXMATCH, REGEXREPLACE

=REGEXEXTRACT(A1,"d+")
  Extracts the first number from text. "Invoice 1234" -> "1234"

=REGEXMATCH(A1,"^[6-9]d{9}$")
  Returns TRUE if A1 is a valid Indian mobile number (10 digits, starts 6-9)

=REGEXREPLACE(A1,"s+"," ")
  Replaces multiple spaces with a single space. Cleans imported data.

=REGEXEXTRACT(A1,"[w.]+@[w.]+.[a-z]+")
  Extracts email address from a cell containing mixed text.

=REGEXREPLACE(A1,"[^0-9]","")
  Strips everything except digits. "PH: +91-98765-43210" -> "919876543210"

Expert: Regex in Python for Bulk Data Cleaning

import re
import pandas as pd

# Load Excel file
df = pd.read_excel("customer_data.xlsx")

# 1. Clean phone numbers: extract 10-digit Indian mobile
def clean_phone(phone):
    if pd.isna(phone):
        return None
    digits = re.sub(r'D', '', str(phone))      # Remove non-digits
    if len(digits) == 12 and digits[:2] == '91':  # Remove country code
        digits = digits[2:]
    if re.match(r'^[6-9]d{9}$', digits):         # Validate
        return digits
    return None

df['clean_phone'] = df['Phone'].apply(clean_phone)

# 2. Validate and extract emails
def extract_email(text):
    pattern = r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}'
    matches = re.findall(pattern, str(text))
    return matches[0] if matches else None

df['clean_email'] = df['Email'].apply(extract_email)

# 3. Named capture groups for structured parsing
pattern = r'Invoice #(?Pd+) Date: (?Pd{2}/d{2}/d{4}) Amount: Rs.(?P[d,]+)'
text = "Invoice #4521 Date: 15/11/2024 Amount: Rs.45,000"
match = re.search(pattern, text)
if match:
    print(match.group('inv_no'))    # 4521
    print(match.group('date'))      # 15/11/2024
    print(match.group('amount'))    # 45,000

# 4. Batch replace across all text columns
for col in df.select_dtypes(include='object').columns:
    df[col] = df[col].str.strip()                    # Remove whitespace
    df[col] = df[col].str.replace(r's+', ' ', regex=True)  # Normalize spaces

df.to_excel("customer_data_cleaned.xlsx", index=False)
print("Cleaned", len(df), "rows")

Test your regex patterns at regex101.com — it explains each part of your pattern, shows all matches with highlighting, and lets you switch between Python, JavaScript, and PHP regex flavors.

When NOT to use regex: Parsing HTML (use BeautifulSoup), parsing JSON/XML (use their parsers), or anything where the structure is known and structured parsers exist. Regex shines for semi-structured free-form text.

Continue learning

All Dev Tools guides →

VS Code for Office Users: 10 Features That Beat NotepadVisual Studio Code is a free editor that is brilliant for cleaning text and…What Is an API? A Plain-English Guide With a Real ExampleAPIs explained with a restaurant analogy, then a real example: fetch exchange rates with…Docker for Beginners: Run Any App Without Installing ItWhat containers are, why “works on my machine” disappears, and your first Docker commands…JSON Explained Simply (for Excel People)What JSON is, how to read it, how it maps to rows and columns,…

📎 Practice files for this article

• 📄
  All code from this post1 examples in one file, ready to open in your editor.
  ⬇ TXT · 448 B

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.