SUBSTITUTE and REPLACE Functions in Excel: Complete Guide with Examples | SheetXAI

The Problem

You have text in column A with old company names that need to be updated, or phone numbers in the wrong format that need to be corrected. You need to replace specific text within cells.

The Easy Way: Use SheetXAI

If you don't want to mess with formulas, the fastest way to do this is simply by asking.

With SheetXAI, you can open the sidebar and type:

Replace all instances of 'Old Company' with 'New Company' in the text in A2.

SheetXAI will instantly write the formula or script for you and fill the cells. It handles the syntax so you can focus on the result.

The Manual Way: The Formulas You Need

To do this manually, you need to use: SUBSTITUTE or REPLACE.

1. SUBSTITUTE

Replaces specific text in a text string. Can replace all instances or a specific occurrence.

Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The text string containing the text you want to replace.
• old_text: The text you want to replace.
• new_text: The text you want to replace old_text with.
• [instance_num]: Which occurrence to replace (optional). If omitted, replaces all instances.

Example: Replace all 'cat' with 'dog': SUBSTITUTE('cat and cat', 'cat', 'dog') returns 'dog and dog'.

2. REPLACE

Replaces characters in a text string based on their position (character number), not based on what the text says.

Syntax: =REPLACE(old_text, start_num, num_chars, new_text)

• old_text: The text string you want to replace characters in.
• start_num: The position of the first character to replace (1 = first character).
• num_chars: How many characters to replace.
• new_text: The text to replace the old characters with.

Example: Replace characters 1-3 with 'New': REPLACE('Old Company', 1, 3, 'New') returns 'New Company'.

Understanding the Logic

1. SUBSTITUTE: Replaces text based on what it says. Use when you know the exact text to find and replace.
2. REPLACE: Replaces text based on position. Use when you know where the text is located (character position) but not necessarily what it says.

Key difference:

• SUBSTITUTE finds text by content: 'Replace all instances of the word 'cat''
• REPLACE finds text by position: 'Replace characters 1-3 with something else'

Common use cases:

• SUBSTITUTE: Update company names, fix typos, change formatting
• REPLACE: Update phone number formats, fix area codes, modify specific character positions

The Final Formula:

=SUBSTITUTE(A2, "Old Company", "New Company")

Conclusion

Now you know the "classic" way to solve this using formulas. It's a great skill to have.

But for those times when you just want the job done without the mental math, SheetXAI is there to help.

Related Guides

• FIND and SEARCH in Excel - Locate text before replacing
• CONCATENATE in Excel - Combine text strings
• TEXT Function in Excel - Format numbers and dates
• Excel AI Guide - Learn how AI can automate your Excel workflows
• AI Spreadsheet Tools - Discover how AI transforms spreadsheet work