Skip to main content

Replacing Text

Find and replace text within strings using the SUBSTITUTE formula for data cleaning and standardization.

Relevant Formula​

SUBSTITUTE​

Substitutes new text for old text in a text string.

View SUBSTITUTE Formula Reference →

Basic Usage​

=SUBSTITUTE([text], "old_text", "new_text")

Input: "Hello World"
Formula: =SUBSTITUTE([text], "World", "Universe")
Result: "Hello Universe"

Common Use Cases​

Data Cleaning​

// Remove special characters
=SUBSTITUTE([phone], "-", "")
Input: "555-123-4567"
Result: "5551234567"

// Standardize separators
=SUBSTITUTE([date], "/", "-")
Input: "2024/03/15"
Result: "2024-03-15"

Text Normalization​

// Replace abbreviations
=SUBSTITUTE([state], "Calif.", "California")

// Standardize terms
=SUBSTITUTE([status], "completed", "done")

Format Conversion​

// Convert line breaks (platform-specific)
=SUBSTITUTE([text], CHAR(10), " ")

// Remove tabs
=SUBSTITUTE([text], CHAR(9), "")

Advanced Techniques​

Multiple Replacements​

// Chain SUBSTITUTE calls
=SUBSTITUTE(SUBSTITUTE([text], ".", ""), ",", "")

// Remove multiple characters
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([text], "(", ""), ")", ""), "-", "")

Case-Sensitive Replacement​

// SUBSTITUTE is case-sensitive
=SUBSTITUTE([text], "Hello", "Hi")  // Only replaces exact match

Occurrence-Specific Replacement​

// Replace only first occurrence
=SUBSTITUTE([text], "the", "a", 1)

// Replace only second occurrence
=SUBSTITUTE([text], "the", "a", 2)

Best Practices​

  1. Case Sensitivity - Remember SUBSTITUTE is case-sensitive
  2. Test Thoroughly - Verify replacements don't affect unintended text
  3. Chain Carefully - For multiple replacements, order matters
  4. Consider TRIM - Clean whitespace after replacements

Additional Formula References​

Additional examples will be added soon.