Automating Text Transformations in Spreadsheets: SUBSTITUTE, REGEXREPLACE, and Bulk Editing

Excel and Google Sheets have powerful text transformation functions that most users never discover

Most spreadsheet users know Find & Replace (Ctrl+H) for basic substitutions. Far fewer know about SUBSTITUTE, REGEXREPLACE, PROPER, TRIM, and their combinations — functions that can automate text transformations that would otherwise require manual editing of thousands of cells.

For anyone working with data exports, CRM outputs, bulk content editing, or any spreadsheet containing text that needs systematic transformation, these functions replace hours of manual work with a formula.

---

SUBSTITUTE: targeted string replacement

SUBSTITUTE replaces specific text within a cell, optionally targeting only a specific occurrence:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Basic use:

=SUBSTITUTE(A1, "Ltd", "Limited")

Replaces every instance of "Ltd" with "Limited" in cell A1.

Target a specific occurrence:

=SUBSTITUTE(A1, "-", "/", 2)

Replaces only the second hyphen with a forward slash. Useful for reformatting date strings: "2024-06-15" → "2024/06/15" while keeping other hyphens.

Chain substitutions with nesting:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ",", ""), ".", ""), " ", "-")

Removes commas, removes periods, replaces spaces with hyphens — three transformations in one formula.

---

REGEXREPLACE (Google Sheets): pattern-based replacement

Google Sheets supports REGEXREPLACE for regex-powered substitution. Excel users need to use VBA or Power Query for regex (not available as a worksheet function in standard Excel):

REGEXREPLACE(text, regular_expression, replacement)

Remove all non-numeric characters:

=REGEXREPLACE(A1, "[^0-9]", "")

Extracts just the digits from a cell — useful for cleaning phone numbers, IDs, or codes with mixed formatting.

Reformat phone numbers:

=REGEXREPLACE(A1, "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")

Converts "2025551234" to "(202) 555-1234". The capture groups (\d{3}) are referenced as $1, $2, $3 in the replacement.

Remove HTML tags:

=REGEXREPLACE(A1, "<[^>]+>", "")

Strips HTML tags from a cell — useful for cleaning exported web content.

Extract the domain from an email address:

=REGEXREPLACE(A1, "^[^@]+@", "")

Removes everything up to and including the @ sign, leaving just the domain.

---

Excel TEXT functions for transformation

Excel has a rich set of text functions for common transformations:

PROPER — title case:

=PROPER(A1)

Converts "JOHN SMITH" or "john smith" to "John Smith". Useful for cleaning all-caps name fields from legacy databases.

TRIM — remove extra spaces:

=TRIM(A1)

Removes leading, trailing, and multiple internal spaces. Essential for cleaning pasted data.

CLEAN — remove non-printable characters:

=CLEAN(A1)

Removes characters that don't print (control characters), often introduced by copying from other systems.

UPPER / LOWER / PROPER — case conversion:

=UPPER(A1)   → ALL CAPS
=LOWER(A1)   → all lowercase
=PROPER(A1)  → Title Case

LEFT, RIGHT, MID — extract substrings:

=LEFT(A1, 3)       → first 3 characters
=RIGHT(A1, 5)      → last 5 characters
=MID(A1, 3, 4)     → 4 characters starting from position 3

---

Practical workflow: bulk content transformations

Scenario 1: Cleaning a product export

A CSV from a legacy system contains product names in ALL CAPS with extra spaces and an old category prefix:

GARDEN > OUTDOOR FURNITURE > CHAIR , FOLDING  , ALUMINIUM

Target format: Outdoor Furniture - Folding Aluminium Chair

=PROPER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "> ", "- "), "  ", " "), "GARDEN - ", "")))

Step by step:

1. SUBSTITUTE: replace "> " with "- "
2. SUBSTITUTE: replace double spaces with single space
3. SUBSTITUTE: remove "GARDEN - " prefix
4. TRIM: clean up any remaining edge whitespace
5. PROPER: convert to title case

Scenario 2: Generating URL slugs from titles

=LOWER(REGEXREPLACE(SUBSTITUTE(TRIM(A1), " ", "-"), "[^a-z0-9-]", ""))

Step by step:

1. TRIM: clean whitespace
2. SUBSTITUTE: replace spaces with hyphens
3. LOWER: convert to lowercase
4. REGEXREPLACE: remove any character that isn't a-z, 0-9, or hyphen

Scenario 3: Standardising phone number format

A column contains phone numbers in multiple formats:

020 7946 0000
+44 20 7946 0000
(020) 7946-0000

Step 1: strip to digits only (Google Sheets):

=REGEXREPLACE(A1, "[^0-9+]", "")

Step 2: normalise to a consistent format from there.

---

Power Query (Excel) for complex transformations

For Excel users without VBA, Power Query provides a transformation interface for complex text operations:

• Split columns by delimiter
• Extract text between characters
• Apply regular expressions (via custom functions)
• Merge columns with specified separators

Power Query transformations are recorded as steps that can be reapplied when data refreshes — useful for repeated data cleaning tasks.

---

How to use the Find & Replace tool on sadiqbd.com

1. Paste your text
2. Enter the search term (literal text or regex pattern)
3. Enter the replacement — leave empty to delete matches
4. Apply options — case-sensitive, whole word, regex mode
5. Review and copy — the transformed text

Useful for one-off bulk text transformations when you don't have spreadsheet data — paste any text, apply multiple substitutions, and copy the cleaned output.

---

Frequently Asked Questions

How do I use regex in Excel (not Google Sheets)? Standard Excel doesn't support regex in worksheet functions. Options: (1) Power Query's Text.Select or Text.Remove for simple pattern extraction; (2) VBA/VBSCRIPT with RegExp object for full regex; (3) Excel 365 users can access LAMBDA and LET to create custom functions; (4) consider using Python/pandas or Google Sheets for regex-heavy work.

What's the difference between SUBSTITUTE and REPLACE in Excel? SUBSTITUTE replaces specified text content anywhere in the string. REPLACE replaces characters at a specific position by character number (start position, length). SUBSTITUTE is generally more useful for text cleaning; REPLACE is useful when you know the position of what needs changing.

Is the Find & Replace tool free? Yes — completely free, no sign-up required.

---

Spreadsheet text functions turn manual editing tasks into reproducible formulas. A transformation that takes an hour manually becomes a formula that runs in seconds and can be reapplied to updated data indefinitely.

Try the Find & Replace tool free at sadiqbd.com — search and replace in any text with literal matching or regex, with case sensitivity and whole-word options.