SUBSTITUTE

Text Functions

(4.8/5)

Substitutes new_text for old_text in a text string. Essential for text replacement, data cleaning, and text manipulation in Excel.

Interactive Formula Tester

Test Text:

Formula:

=SUBSTITUTE("Hello old world")

Complete Theory & Understanding

Master the fundamentals of Excel SUBSTITUTE function

Core Concept

The SUBSTITUTE function is Excel's text replacement tool that replaces old_text with new_text in a text string. It's essential for text manipulation, data cleaning, and text formatting tasks.

Why Use SUBSTITUTE?

Clean and standardize text data
Format text with proper characters
Process and transform text data
Generate formatted reports

Key Characteristics

Text Replacement

Replaces old text with new text

SUBSTITUTE("Hello World", "World", "Excel") → "Hello Excel"

All Occurrences

Replaces all occurrences by default

SUBSTITUTE("apple, apple", "apple", "banana") → "banana, banana"

Specific Instance

Can replace specific occurrence

SUBSTITUTE("apple, apple", "apple", "banana", 2) → "apple, banana"

Case Sensitive

Replacement is case sensitive

SUBSTITUTE("Hello", "hello", "Hi") → "Hello"

Function Anatomy

=SUBSTITUTE(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Data Cleaning

Clean and standardize text data

Text Formatting

Format text with proper characters

Data Processing

Process and transform text data

Report Generation

Generate formatted reports

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=SUBSTITUTE(text, old_text, new_text, instance_num)

Required

text:

The text string to substitute in

Required

old_text:

The text to be replaced

Required

new_text:

The text to replace old_text with

Optional

instance_num:

Which occurrence to replace (default: all occurrences)

Returns

Return Value:

Text with substitutions made

Description: Substitutes new_text for old_text in a text string

Interactive Examples

Basic Text Replacement

Replace text in a string

Input Text:

"Hello World"

Formula:

=SUBSTITUTE("Hello World", "World", "Excel")

Result:

Hello Excel

Replaces 'World' with 'Excel' in the text

VBA Implementation & Automation

Basic SUBSTITUTE in VBA

Simple VBA implementation of SUBSTITUTE function

' Basic SUBSTITUTE in VBA
Range("C1").Value = Application.WorksheetFunction.Substitute(Range("A1").Value, "old", "new")

' Using VBA SUBSTITUTE function
Dim result As String
result = Application.WorksheetFunction.Substitute("Hello World", "World", "Excel")

' Loop through range and substitute text
Sub SubstituteText()
    Dim cell As Range
    For Each cell In Range("A1:A10")
        If Not IsEmpty(cell.Value) Then
            cell.Value = Application.WorksheetFunction.Substitute(cell.Value, "old", "new")
        End If
    Next cell
End Sub