SUBSTITUTE Function in Excel Tutorial

Excel Tutorial

Introduction

The SUBSTITUTE function in Excel is used to replace specific text with new text inside a cell. It is useful when you need to correct employee records, update product codes, change separators, or replace outdated words in business data.

SUBSTITUTE can replace all matching occurrences or only a specific occurrence of the text. This makes it useful for cleaning and updating text without manually editing every cell.

SUBSTITUTE Function Syntax

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

Arguments:

  • text: The original text or cell reference in which you want to make a replacement.
  • old_text: The text you want to replace.
  • new_text: The replacement text.
  • instance_num: Optional. Specifies which occurrence of the old text should be replaced. If omitted, all matching occurrences are replaced.

Important Points:

  • SUBSTITUTE replaces specific text with new text.
  • It is case-sensitive.
  • If instance_num is omitted, all matching occurrences are replaced.
  • If instance_num is provided, only that occurrence is replaced.
  • If the old text is not found, the original text remains unchanged.

Question 1: Replace a Word in a Course Name

Problem Statement

A training institute has updated the name of one of its courses. The old course name contains the word Basic, which needs to be replaced with Advanced.

Old Course NameNew Course Name
Basic Excel CourseAdvanced Excel Course
Basic Python CourseAdvanced Python Course
Basic SQL CourseAdvanced SQL Course
Basic Java CourseAdvanced Java Course

Assume the old course names are in A2.

Excel Formula

=SUBSTITUTE(A2,"Basic","Advanced")

Expected Result: For Basic Excel Course, the result is Advanced Excel Course.

Explanation: SUBSTITUTE searches for Basic and replaces it with Advanced.

Student Task: Enter Basic Power BI Training in A6. Replace Basic with Professional.

New Expected Result: Professional Power BI Training

Question 2: Replace Hyphens with Underscores in Employee IDs

Problem Statement

An HR department stores employee IDs using hyphens. The company now wants to use underscores instead.

Old Employee IDUpdated Employee ID
EMP-HR-101EMP_HR_101
EMP-IT-202EMP_IT_202
EMP-FN-303EMP_FN_303
EMP-MK-404EMP_MK_404

Assume employee IDs are in A2.

Excel Formula

=SUBSTITUTE(A2,"-","_")

Expected Result: For EMP-HR-101, the result is EMP_HR_101.

Explanation: SUBSTITUTE replaces every hyphen with an underscore because no occurrence number is specified.

Student Task: Enter STAFF-IT-205 in A6. Replace all hyphens with underscores.

New Expected Result: STAFF_IT_205

Question 3: Replace a Specific Word in Product Names

Problem Statement

An electronics company wants to update the word Old in its product descriptions to New.

Product DescriptionUpdated Description
Old Laptop ModelNew Laptop Model
Old Keyboard ModelNew Keyboard Model
Old Mouse ModelNew Mouse Model
Old Monitor ModelNew Monitor Model

Assume product descriptions are in A2.

Excel Formula

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

Expected Result: For Old Laptop Model, the result is New Laptop Model.

Explanation: SUBSTITUTE replaces the specified word wherever it appears in the text.

Student Task: Enter Old Printer Series in A6. Replace Old with Latest.

New Expected Result: Latest Printer Series

Question 4: Replace a Specific Occurrence of a Character

Problem Statement

A company uses multiple hyphens in its product codes. The inventory team wants to replace only the first hyphen with an underscore.

Product CodeUpdated Product Code
LAP-IT-105LAP_IT-105
KEY-HW-205KEY_HW-205
MON-DP-310MON_DP-310
USB-AC-450USB_AC-450

Assume product codes are in A2.

Excel Formula

=SUBSTITUTE(A2,"-","_",1)

Expected Result: For LAP-IT-105, the result is LAP_IT-105.

Explanation: The fourth argument 1 tells Excel to replace only the first occurrence of the hyphen. The second hyphen remains unchanged.

Student Task: Enter MOUSE-WL-250 in A6. Replace only the first hyphen with an underscore.

New Expected Result: MOUSE_WL-250

Question 5: Replace a Specific Word Occurrence

Problem Statement

A company uses the word Training multiple times in a course description. The team wants to replace only the second occurrence with Workshop.

Course DescriptionUpdated Description
Excel Training and Training SupportExcel Training and Workshop Support
Python Training and Training GuidePython Training and Workshop Guide
SQL Training and Training MaterialSQL Training and Workshop Material
Java Training and Training SessionJava Training and Workshop Session

Assume course descriptions are in A2.

Excel Formula

=SUBSTITUTE(A2,"Training","Workshop",2)

Expected Result: For Excel Training and Training Support, the result is Excel Training and Workshop Support.

Explanation: The instance number 2 tells Excel to replace only the second occurrence of Training.

Student Task: Enter Excel Training and Training Practice in A6. Replace only the second occurrence of Training with Workshop.

New Expected Result: Excel Training and Workshop Practice

Question 6: Replace Spaces with Hyphens in Course Names

Problem Statement

A training institute wants to convert course names into hyphen-separated text.

Course NameUpdated Course Name
Data AnalyticsData-Analytics
Web DevelopmentWeb-Development
Digital MarketingDigital-Marketing
Computer ScienceComputer-Science

Assume course names are in A2.

Excel Formula

=SUBSTITUTE(A2," ","-")

Expected Result: For Data Analytics, the result is Data-Analytics.

Explanation: SUBSTITUTE replaces the space between words with a hyphen.

Student Task: Enter Full Stack Development in A6. Replace all spaces with hyphens.

New Expected Result: Full-Stack-Development

Question 7: Replace a Specific Character in Phone Numbers

Problem Statement

A customer service department stores phone numbers with spaces. The company wants to replace the spaces with hyphens.

Phone NumberUpdated Phone Number
98765 4321098765-43210
98123 4567898123-45678
91234 5678991234-56789
99887 6655499887-66554

Assume phone numbers are stored as text in A2.

Excel Formula

=SUBSTITUTE(A2," ","-")

Expected Result: For 98765 43210, the result is 98765-43210.

Explanation: SUBSTITUTE replaces the space between the two groups of digits with a hyphen.

Student Task: Enter 98700 12345 in A6 as text. Replace the space with a hyphen.

New Expected Result: 98700-12345

Question 8: Replace Multiple Occurrences of a Character

Problem Statement

A sales department stores transaction codes with forward slashes. The team wants to replace every slash with a hyphen.

Transaction CodeUpdated Transaction Code
2026/10/012026-10-01
2026/11/152026-11-15
2026/12/202026-12-20
2027/01/052027-01-05

Assume transaction codes are in A2.

Excel Formula

=SUBSTITUTE(A2,"/","-")

Expected Result: For 2026/10/01, the result is 2026-10-01.

Explanation: Since the occurrence number is not specified, every forward slash is replaced.

Student Task: Enter 2027/02/15 in A6. Replace all forward slashes with hyphens.

New Expected Result: 2027-02-15

Student Tasks

Solve these exercises independently using only the SUBSTITUTE function.

Task 1: Replace Words

Original TextOld TextNew TextUpdated Text
Basic Excel CourseBasicProfessional?
Old Laptop ModelOldNew?
Basic Python ClassBasicAdvanced?
Old Product SeriesOldLatest?

Your Task: Replace the specified words in each text value.

Task 2: Replace Special Characters

Original CodeOld CharacterNew CharacterUpdated Code
EMP-HR-101–_?
INV/2026/105/–?
LAP-IT-205–/?
SALES/EMP/50/_?

Your Task: Replace all matching characters in each code.

Task 3: Replace a Specific Occurrence

Original TextOld TextNew TextInstance NumberUpdated Text
A-B-C-D–_1?
A-B-C-D–_2?
Excel Training TrainingTrainingWorkshop1?
Excel Training TrainingTrainingWorkshop2?

Your Task: Replace only the specified occurrence.

Final Student Challenge: Build a Company Data Text Updater

Problem Statement

A company wants to standardize its employee records. Some employee IDs contain hyphens, course names contain the word Basic, and phone numbers contain spaces.

Create a worksheet that displays the original information and the updated information.

Given Data

Employee NameEmployee IDCourse NamePhone Number
Amit KumarEMP-HR-101Basic Excel98765 43210
Priya SharmaEMP-IT-202Basic Python98123 45678
Rahul VermaEMP-FN-303Basic SQL91234 56789
Neha GuptaEMP-MK-404Basic Java99887 66554
Rohit SinghEMP-OP-505Basic Power BI98700 12345

Your Task

Add three columns:

  • Updated Employee ID
  • Updated Course Name
  • Updated Phone Number

Apply these changes:

  1. Replace hyphens in employee IDs with underscores.
  2. Replace Basic with Advanced in course names.
  3. Replace spaces in phone numbers with hyphens.

Formula for Updated Employee ID:

=SUBSTITUTE(B2,"-","_")

Formula for Updated Course Name:

=SUBSTITUTE(C2,"Basic","Advanced")

Formula for Updated Phone Number:

=SUBSTITUTE(D2," ","-")

Copy all three formulas down for the five employees.

Expected Result

Employee NameUpdated Employee IDUpdated Course NameUpdated Phone Number
Amit KumarEMP_HR_101Advanced Excel98765-43210
Priya SharmaEMP_IT_202Advanced Python98123-45678
Rahul VermaEMP_FN_303Advanced SQL91234-56789
Neha GuptaEMP_MK_404Advanced Java99887-66554
Rohit SinghEMP_OP_505Advanced Power BI98700-12345

Key Takeaways

  • SUBSTITUTE in Excel replaces specific text inside a cell.
  • Its syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]).
  • It can replace words, numbers stored as text, spaces, and special characters.
  • Without instance_num, all matching occurrences are replaced.
  • With instance_num, only the specified occurrence is replaced.
  • SUBSTITUTE is case-sensitive.
  • If the old text is not found, the original text remains unchanged.

FAQs About the SUBSTITUTE Function in Excel

1. What is the SUBSTITUTE function in Excel?

SUBSTITUTE replaces specific text with new text inside a cell. It is commonly used to update words, characters, codes, and text records.

2. What is the syntax of the SUBSTITUTE function?

The syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]).

3. Does SUBSTITUTE replace all matching occurrences?

Yes. If you omit instance_num, Excel replaces all matching occurrences of the specified text.

4. How can I replace only the first occurrence of a word?

Use 1 as the fourth argument. For example, =SUBSTITUTE(A2,"Old","New",1) replaces only the first occurrence.

5. Is SUBSTITUTE case-sensitive?

Yes. It treats uppercase and lowercase letters differently. For example, Excel and EXCEL are different text values.

6. Can SUBSTITUTE replace spaces?

Yes. You can replace spaces with hyphens, underscores, or other characters.

7. What happens if the old text is not found?

If Excel cannot find the specified old text, it returns the original text without making any changes.

8. Can SUBSTITUTE replace special characters?

Yes. It can replace characters such as hyphens, slashes, spaces, underscores, and other symbols.

9. What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces text based on matching characters or words. REPLACE replaces text based on a specified character position and number of characters.