SUBSTITUTE and REPLACE in Excel - Step-by-Step Guide

Introduction

These two functions help you change text inside a cell. SUBSTITUTE replaces specific text you choose, while REPLACE changes text based on position. They solve the problem of fixing or updating parts of text without rewriting everything.

When you want to change all occurrences of a word in a list of product names.

When you need to fix a typo that appears multiple times in a text column.

When you want to replace a part of a phone number or code based on its position.

When you want to update a date format by replacing characters at certain positions.

When you want to remove or change a specific word inside a sentence without affecting others.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for typing a formula

Step 2: Type

- formula bar

You start entering the formula

💡 Start with =SUBSTITUTE( or =REPLACE( to see the function help

Step 3: For SUBSTITUTE, type

- formula bar

Formula looks like =SUBSTITUTE(text, old_text, new_text, [instance_num])

💡 Text is the cell with original text, old_text is what to replace, new_text is replacement, instance_num is optional to replace only one occurrence

Step 4: For REPLACE, type

- formula bar

Formula looks like =REPLACE(old_text, start_num, num_chars, new_text)

💡 old_text is original text, start_num is position to start replacing, num_chars is how many characters to replace, new_text is replacement text

Step 5: Press Enter

- formula bar

The cell shows the text with replacements applied

Step 6: Copy the formula down

- cells below

All rows update with replaced text based on each row's original text

Before vs After

Before

Cell A2 contains 'apple banana apple', cell A3 contains '1234567890'

After

Using =SUBSTITUTE(A2, "apple", "orange") shows 'orange banana orange' in B2; Using =REPLACE(A3, 4, 3, "XYZ") shows '123XYZ7890' in B3

Settings Reference

old_text

📍 Inside SUBSTITUTE formula

Text you want to replace

Default: Required

new_text

📍 Inside SUBSTITUTE or REPLACE formula

Text to replace with

Default: Required

instance_num

📍 Inside SUBSTITUTE formula

Which occurrence of old_text to replace

Default: All occurrences replaced if omitted

start_num

📍 Inside REPLACE formula

Position in text to start replacing

Default: Required

num_chars

📍 Inside REPLACE formula

How many characters to replace

Default: Required

Common Mistakes

Using REPLACE when you want to replace text by matching words

REPLACE works by position, so it may replace wrong characters if text length changes

Use SUBSTITUTE to replace specific words or text strings

Omitting instance_num in SUBSTITUTE when only one occurrence should change

All occurrences get replaced, which may not be desired

Specify the instance_num argument to replace only the needed occurrence

Using wrong start_num or num_chars in REPLACE causing unexpected text changes

Positions are counted from left, so wrong numbers replace wrong parts

Count characters carefully and test with sample text before applying

Summary

SUBSTITUTE replaces specific text by matching words or phrases.

REPLACE changes text based on position and length inside the string.

Use SUBSTITUTE for word-based changes and REPLACE for position-based edits.

Practice

1. Which function would you use in Excel to replace all occurrences of the word apple with orange in a text string?

easy

A. REPLACE

B. SUBSTITUTE

C. CONCATENATE

D. LEFT

Solution

1. Step 1: Understand the function purpose

   SUBSTITUTE replaces specific text by matching the exact word or phrase in the string.

2. Step 2: Compare with REPLACE

   REPLACE changes text based on position, not matching text, so it is not suitable here.

3. Final Answer:

   SUBSTITUTE -> Option B

4. Quick Check:

   Replace by matching text = SUBSTITUTE [OK]

Hint: Use SUBSTITUTE to replace specific words by matching text [OK]

Common Mistakes:

• Using REPLACE when you need to match text
• Confusing SUBSTITUTE with CONCATENATE
• Trying to use LEFT to replace text

2. Which of the following is the correct syntax to replace the first 3 characters in cell A1 with XYZ using the REPLACE function?

easy

A. REPLACE("XYZ", 1, 3, A1)

B. REPLACE(A1, "XYZ", 1, 3)

C. REPLACE(A1, 3, 1, "XYZ")

D. REPLACE(A1, 1, 3, "XYZ")

Solution

1. Step 1: Recall REPLACE syntax

   REPLACE(text, start_num, num_chars, new_text) replaces characters starting at start_num for num_chars length.

2. Step 2: Match syntax to question

   To replace first 3 characters in A1 with "XYZ", start_num=1, num_chars=3, new_text="XYZ".

3. Final Answer:

   REPLACE(A1, 1, 3, "XYZ") -> Option D

4. Quick Check:

   REPLACE(text, 1, 3, "XYZ") correct syntax [OK]

Hint: REPLACE(text, start, length, new_text) replaces by position [OK]

Common Mistakes:

• Swapping start_num and num_chars
• Putting new_text in wrong position
• Using SUBSTITUTE syntax for REPLACE

3. What will be the result of the formula =SUBSTITUTE("banana", "a", "o", 2)?

medium

A. "bonana"

B. "banana"

C. "banona"

D. "bonono"

Solution

1. Step 1: Understand SUBSTITUTE with instance number

   SUBSTITUTE(text, old_text, new_text, instance_num) replaces only the specified occurrence.

2. Step 2: Apply to "banana" replacing 2nd "a" with "o"

   "banana" has 'a' at positions 2, 4, and 6. The 2nd 'a' is at position 4, replaced by 'o' gives "banona".

3. Final Answer:

   "banona" -> Option C

4. Quick Check:

   Replace 2nd 'a' with 'o' = "banona" [OK]

Hint: SUBSTITUTE with instance replaces only that occurrence [OK]

Common Mistakes:

• Replacing all 'a's instead of just the 2nd
• Confusing instance number with position
• Expecting REPLACE to work by matching text

4. The formula =REPLACE(A1, 2, 3, "XYZ") returns an error. Which is the most likely cause?

medium

A. The start position 2 is greater than the length of text in A1

B. Cell A1 is empty

C. The number of characters to replace is negative

D. The new text "XYZ" is too long

Solution

1. Step 1: Check REPLACE parameters

   REPLACE(text, start_num, num_chars, new_text) requires start_num within text length.

2. Step 2: Identify error cause

   If start_num (2) is greater than text length, REPLACE cannot start and returns error.

3. Final Answer:

   The start position 2 is greater than the length of text in A1 -> Option A

4. Quick Check:

   Start position must be within text length [OK]

Hint: Start position must be inside text length for REPLACE [OK]

Common Mistakes:

• Assuming empty cell causes error
• Thinking new text length matters
• Using negative number of characters

5. You have the text "Invoice12345" in cell A1. Which formula will replace the numeric part with "67890" regardless of how many digits the number has?

hard

A. REPLACE(A1, 8, LEN(A1)-7, "67890")

B. SUBSTITUTE(A1, "12345", "67890")

C. REPLACE(A1, 8, 5, "67890")

D. SUBSTITUTE(A1, MID(A1,8,5), "67890")

Solution

1. Step 1: Identify numeric part position and length

   The text "Invoice12345" has "Invoice" (7 chars) then numbers starting at position 8. Length of numbers is LEN(A1)-7.

2. Step 2: Use REPLACE with dynamic length

   REPLACE(A1, 8, LEN(A1)-7, "67890") replaces from position 8 for all digits, no matter how many.

3. Final Answer:

   REPLACE(A1, 8, LEN(A1)-7, "67890") -> Option A

4. Quick Check:

   Dynamic length replace = REPLACE with LEN() [OK]

Hint: Use LEN() to replace variable-length text with REPLACE [OK]

Common Mistakes:

• Hardcoding length instead of using LEN()
• Using SUBSTITUTE which needs exact text match
• Using MID inside SUBSTITUTE incorrectly