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 Name | New Course Name |
|---|---|
| Basic Excel Course | Advanced Excel Course |
| Basic Python Course | Advanced Python Course |
| Basic SQL Course | Advanced SQL Course |
| Basic Java Course | Advanced 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 ID | Updated Employee ID |
|---|---|
| EMP-HR-101 | EMP_HR_101 |
| EMP-IT-202 | EMP_IT_202 |
| EMP-FN-303 | EMP_FN_303 |
| EMP-MK-404 | EMP_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 Description | Updated Description |
|---|---|
| Old Laptop Model | New Laptop Model |
| Old Keyboard Model | New Keyboard Model |
| Old Mouse Model | New Mouse Model |
| Old Monitor Model | New 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 Code | Updated Product Code |
|---|---|
| LAP-IT-105 | LAP_IT-105 |
| KEY-HW-205 | KEY_HW-205 |
| MON-DP-310 | MON_DP-310 |
| USB-AC-450 | USB_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 Description | Updated Description |
|---|---|
| Excel Training and Training Support | Excel Training and Workshop Support |
| Python Training and Training Guide | Python Training and Workshop Guide |
| SQL Training and Training Material | SQL Training and Workshop Material |
| Java Training and Training Session | Java 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 Name | Updated Course Name |
|---|---|
| Data Analytics | Data-Analytics |
| Web Development | Web-Development |
| Digital Marketing | Digital-Marketing |
| Computer Science | Computer-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 Number | Updated Phone Number |
|---|---|
| 98765 43210 | 98765-43210 |
| 98123 45678 | 98123-45678 |
| 91234 56789 | 91234-56789 |
| 99887 66554 | 99887-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 Code | Updated Transaction Code |
|---|---|
| 2026/10/01 | 2026-10-01 |
| 2026/11/15 | 2026-11-15 |
| 2026/12/20 | 2026-12-20 |
| 2027/01/05 | 2027-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 Text | Old Text | New Text | Updated Text |
|---|---|---|---|
| Basic Excel Course | Basic | Professional | ? |
| Old Laptop Model | Old | New | ? |
| Basic Python Class | Basic | Advanced | ? |
| Old Product Series | Old | Latest | ? |
Your Task: Replace the specified words in each text value.
Task 2: Replace Special Characters
| Original Code | Old Character | New Character | Updated 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 Text | Old Text | New Text | Instance Number | Updated Text |
|---|---|---|---|---|
| A-B-C-D | – | _ | 1 | ? |
| A-B-C-D | – | _ | 2 | ? |
| Excel Training Training | Training | Workshop | 1 | ? |
| Excel Training Training | Training | Workshop | 2 | ? |
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 Name | Employee ID | Course Name | Phone Number |
|---|---|---|---|
| Amit Kumar | EMP-HR-101 | Basic Excel | 98765 43210 |
| Priya Sharma | EMP-IT-202 | Basic Python | 98123 45678 |
| Rahul Verma | EMP-FN-303 | Basic SQL | 91234 56789 |
| Neha Gupta | EMP-MK-404 | Basic Java | 99887 66554 |
| Rohit Singh | EMP-OP-505 | Basic Power BI | 98700 12345 |
Your Task
Add three columns:
- Updated Employee ID
- Updated Course Name
- Updated Phone Number
Apply these changes:
- Replace hyphens in employee IDs with underscores.
- Replace
BasicwithAdvancedin course names. - 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 Name | Updated Employee ID | Updated Course Name | Updated Phone Number |
|---|---|---|---|
| Amit Kumar | EMP_HR_101 | Advanced Excel | 98765-43210 |
| Priya Sharma | EMP_IT_202 | Advanced Python | 98123-45678 |
| Rahul Verma | EMP_FN_303 | Advanced SQL | 91234-56789 |
| Neha Gupta | EMP_MK_404 | Advanced Java | 99887-66554 |
| Rohit Singh | EMP_OP_505 | Advanced Power BI | 98700-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.

