Replacing Text Using SUBSTITUTE and REPLACE in Excel

Course: Advanced Excel

Topic: Replacing Text Using SUBSTITUTE and REPLACE

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction

Microsoft Excel provides several text functions that help users clean, modify, standardize, and organize textual information. Two important functions used for replacing text are SUBSTITUTE and REPLACE. Both functions return modified text without requiring users to edit the original value manually.

The SUBSTITUTE function replaces specific text by matching the content you want to change. The REPLACE function replaces characters according to their positions within a text string. Understanding this difference helps users select the correct function for data cleaning, report preparation, product code modification, employee record management, and other business tasks.

For example, if a cell contains "Mumbai City", you can use SUBSTITUTE to replace "Mumbai" with "Pune". If a cell contains "EMP1001" and you want to replace characters starting at a particular position, REPLACE may be more appropriate.

2. Learning Objectives

• Understand the purpose of SUBSTITUTE and REPLACE.
• Learn the syntax and arguments of both functions.
• Replace specific words, phrases, and characters.
• Replace a particular occurrence of text using SUBSTITUTE.
• Replace characters based on their positions using REPLACE.
• Remove unwanted characters by replacing them with empty text.
• Apply these functions to practical data-cleaning scenarios.
• Identify common errors and develop efficient Excel formulas.

3. Understanding Text Replacement in Excel

Text replacement means changing an existing portion of a text string into another value. The replacement may involve a word, a phrase, a number represented as text, a symbol, or a group of characters.

Consider the following examples:

The correct function depends on whether the replacement is based on matching specific content or identifying a fixed character position.

4. Understanding the SUBSTITUTE Function

4.1 Definition

The SUBSTITUTE function replaces existing text with new text by searching for a specified word, character, or sequence of characters. By default, it replaces every occurrence of the specified old text. You can also specify which occurrence should be replaced.

Use SUBSTITUTE when you know exactly what text you want to find and replace, regardless of where it appears in the string.

4.2 Syntax

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

4.3 Explanation of Arguments

4.4 Example: Replacing a Word

Suppose cell A2 contains the text "Mumbai Office". You want to change "Mumbai" to "Pune".

Formula:

=SUBSTITUTE(A2,"Mumbai","Pune")

Output: Pune Office

Excel searches for the exact text "Mumbai" and replaces it with "Pune". The remaining text stays unchanged.

4.5 Example: Replacing All Occurrences

Suppose A2 contains "Red Car, Red Bike, Red Bus". You want to replace every occurrence of "Red" with "Blue".

Formula:

=SUBSTITUTE(A2,"Red","Blue")

Output: Blue Car, Blue Bike, Blue Bus

Because the optional instance_num argument is omitted, every occurrence of "Red" is replaced.

4.6 Example: Replacing Only a Specific Occurrence

Suppose A2 contains "Red, Red, Red". You want to replace only the second occurrence of "Red" with "Blue".

Formula:

=SUBSTITUTE(A2,"Red","Blue",2)

Output: Red, Blue, Red

The fourth argument, 2, tells Excel to replace only the second occurrence of the matching text.

4.7 Example: Removing Unwanted Characters

SUBSTITUTE can remove a character by replacing it with an empty string represented by two quotation marks.

Suppose A2 contains "ABC-123-XYZ" and you want to remove every hyphen.

Formula:

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

Output: ABC123XYZ

This technique is useful for cleaning product codes, phone numbers, reference IDs, and other text fields.

4.8 Example: Standardizing Abbreviations

Suppose A2 contains "Dept. of Sales". You want to replace "Dept." with "Department".

Formula:

=SUBSTITUTE(A2,"Dept.","Department")

Output: Department of Sales

4.9 Example: Replacing a Specific Separator

Suppose A2 contains "2026-10-27". You want to display the date-like text using slashes instead of hyphens.

Formula:

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

Output: 2026/10/27

This formula modifies the text representation. If A2 contains a real Excel date value rather than text, use an appropriate date format or TEXT formula depending on your objective.

5. Understanding the REPLACE Function

5.1 Definition

The REPLACE function replaces a specified number of characters in a text string, starting from a position you choose. Unlike SUBSTITUTE, it does not need to search for a particular word or character. Instead, it works with character positions.

Use REPLACE when you know where the characters are located and how many characters should be changed.

5.2 Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

5.3 Explanation of Arguments

5.4 Example: Replacing Characters in a Product Code

Suppose A2 contains "PRD1001". You want to replace the three characters starting at position 4 with "200".

Formula:

=REPLACE(A2,4,3,"200")

Output: PRD2001

The first three characters, "PRD", remain unchanged. The next three characters, "100", are replaced with "200". The final character, "1", remains unchanged.

5.5 Example: Updating a Year

Suppose cell A2 contains the text "2025" and you want to change the last two characters to "26".

Formula:

=REPLACE(A2,3,2,"26")

Output: 2026

The formula starts at position 3, replaces two characters, and inserts the new text "26".

5.6 Example: Replacing the First Characters

Suppose A2 contains "123456". You want to replace the first three characters with "ABC".

Formula:

=REPLACE(A2,1,3,"ABC")

Output: ABC456

5.7 Example: Removing Characters by Position

Suppose A2 contains "XXPRODUCT101". The first two characters, "XX", are unwanted.

Formula:

=REPLACE(A2,1,2,"")

Output: PRODUCT101

Replacing characters with an empty string removes them from the result.

5.8 Example: Masking Part of an Identifier

Suppose A2 contains the sample identifier "ABCD123456". You want to replace the six digits beginning at position 5 with asterisks.

Formula:

=REPLACE(A2,5,6,"******")

Output: ABCD******

This technique can help create masked display values. However, formula-based masking is not a security control and should not be used as a substitute for protecting sensitive source data.

6. Difference Between SUBSTITUTE and REPLACE

7. Choosing the Correct Function

Use the following decision guide when working with text in Excel.

8. Combining SUBSTITUTE and REPLACE

In some situations, you may need to perform more than one text modification. You can nest one function inside another to apply multiple transformations in a single formula.

8.1 Example: Replace a Word and Remove a Hyphen

Suppose A2 contains "Old-Product-101". You want to replace "Old" with "New" and then remove every hyphen.

Formula:

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

Output: NewProduct101

The inner SUBSTITUTE changes "Old" to "New". The outer SUBSTITUTE then removes all hyphens from the updated result.

8.2 Example: Replace Text and Then Change a Fixed Segment

Suppose A2 contains "OLD-1001". You want to change "OLD" to "NEW" and then replace the four digits with "2002".

Formula:

=REPLACE(SUBSTITUTE(A2,"OLD","NEW"),5,4,"2002")

Output: NEW-2002

First, SUBSTITUTE updates the prefix. REPLACE then changes the four characters starting at position 5.

9. Practical Business Applications

9.1 Cleaning Customer Records

Customer data may contain inconsistent abbreviations, unwanted symbols, or old location names. SUBSTITUTE can standardize known terms.

=SUBSTITUTE(A2,"St.","Street")

Use this formula only when "St." is intended to mean "Street" in the relevant dataset. Abbreviations can have different meanings depending on context.

9.2 Updating Product Codes

Product identifiers often follow fixed formats. REPLACE can modify a known section of a code.

=REPLACE(A2,4,3,"500")

This replaces three characters beginning at position 4 with "500". Confirm the code structure before applying the formula to a large dataset.

9.3 Standardizing Phone Number Text

If phone numbers are stored as text and contain a consistent separator, SUBSTITUTE can remove that separator.

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

For multiple unwanted separators, nest SUBSTITUTE functions. Preserve country codes and leading zeros where required.

9.4 Correcting Repeated Terms

When a known word is repeated throughout a description, SUBSTITUTE can replace every matching occurrence.

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

9.5 Updating Department Codes

If employee IDs use a fixed prefix followed by a numeric code, REPLACE can update the prefix when the prefix length is consistent.

=REPLACE(A2,1,3,"HR-")

Check the length and format of the original identifiers before applying this formula.

9.6 Removing Formatting Characters

When data has been imported from another system, unnecessary punctuation may affect sorting, matching, and reporting.

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

This formula removes hyphens and ordinary spaces from the text. It does not automatically remove every possible hidden or nonprinting character.

10. Common Errors and Troubleshooting

11. Important Considerations

• SUBSTITUTE matches the specified text, while REPLACE works by character position.
• SUBSTITUTE replaces all occurrences by default unless instance_num is specified.
• SUBSTITUTE is case-sensitive. For example, "Excel" and "excel" are treated as different text.
• REPLACE counts character positions starting at 1.
• In REPLACE, num_chars specifies how many existing characters to remove from the original string before inserting the replacement text.
• Both functions return modified text and do not change the original cell value.
• Use a separate result column when cleaning important datasets so that the original data remains available for comparison.
• Test formulas on a small sample before applying them to thousands of records.
• Use TRIM to remove extra ordinary spaces and CLEAN to remove many nonprinting characters when these are also part of the data-cleaning problem.
• SUBSTITUTE does not support wildcard matching; it looks for the specified text.

12. Hands-On Practice Exercises

Exercise 1: Replace a City Name

Cell A2 contains "Mumbai Branch". Replace "Mumbai" with "Pune".

Answer:

=SUBSTITUTE(A2,"Mumbai","Pune")

Expected output: Pune Branch

Exercise 2: Replace All Hyphens

Cell A2 contains "ABC-123-XYZ". Remove every hyphen.

Answer:

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

Expected output: ABC123XYZ

Exercise 3: Replace the Third Occurrence

Cell A2 contains "A-B-A-B-A-B". Replace only the third occurrence of "A" with "X".

Answer:

=SUBSTITUTE(A2,"A","X",3)

Expected output: A-B-A-B-X-B

Exercise 4: Update an Employee Code

Cell A2 contains "EMP1001". Replace the three characters starting at position 4 with "250".

Answer:

=REPLACE(A2,4,3,"250")

Expected output: EMP2501

Exercise 5: Remove a Prefix

Cell A2 contains "XXCUSTOMER105". Remove the first two characters.

Answer:

=REPLACE(A2,1,2,"")

Expected output: CUSTOMER105

Exercise 6: Combine Two Replacements

Cell A2 contains "OLD-PRODUCT-101". Change "OLD" to "NEW" and remove all hyphens.

Answer:

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

Expected output: NEWPRODUCT101

13. Mini Project: Product Data Standardization

Objective: Create a worksheet that standardizes product descriptions and identifiers using SUBSTITUTE and REPLACE.

Create a worksheet with the following columns:

Task 1: Standardize the product name.

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

Task 2: Update the product code.

=REPLACE(B2,4,3,"200")

Task 3: Compare the original and updated values.

• Verify that the intended text was replaced.
• Confirm that unaffected parts of each value remain unchanged.
• Check for unexpected spaces and punctuation.
• Test the formulas on different product names and code lengths.
• Keep the original columns for auditing and error recovery.

Expected learning outcome: Learners will be able to standardize text values, update fixed code segments, and build repeatable data-cleaning workflows in Excel.

14. Interview Questions and Answers

Q1. What is the SUBSTITUTE function in Excel?

Answer: SUBSTITUTE replaces specified text with new text by matching the contents of a string.

Q2. What is the REPLACE function in Excel?

Answer: REPLACE replaces a specified number of characters starting at a particular position in a text string.

Q3. What is the main difference between SUBSTITUTE and REPLACE?

Answer: SUBSTITUTE searches for matching text, whereas REPLACE uses a starting position and character count.

Q4. How do you replace every occurrence of a word?

Answer: Use SUBSTITUTE without the optional instance_num argument.

Q5. How do you replace only the second occurrence of a character?

Answer: Specify 2 as the fourth argument in SUBSTITUTE.

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

Q6. How do you remove a character using SUBSTITUTE?

Answer: Replace the character with an empty string.

=SUBSTITUTE(A2,"#","")

Q7. How do you remove the first three characters using REPLACE?

Answer: Set start_num to 1 and num_chars to 3.

=REPLACE(A2,1,3,"")

Q8. Is SUBSTITUTE case-sensitive?

Answer: Yes. Matching text must have the same letter case for the replacement to occur.

Q9. Can these functions modify the original cell directly?

Answer: No. They return a modified text result in the formula cell. The original value remains unchanged unless you deliberately overwrite it.

Q10. Can SUBSTITUTE and REPLACE be nested?

Answer: Yes. Nesting allows multiple text transformations to be performed in one formula.

15. Quick Revision Notes

• SUBSTITUTE: Replaces text by matching its contents.
• REPLACE: Replaces characters by their positions.
• SUBSTITUTE syntax: =SUBSTITUTE(text,old_text,new_text,[instance_num])
• REPLACE syntax: =REPLACE(old_text,start_num,num_chars,new_text)
• Replace all matching text: Omit instance_num in SUBSTITUTE.
• Replace one occurrence: Specify instance_num in SUBSTITUTE.
• Remove text: Use an empty string, "", as the replacement.
• Position counting: REPLACE starts counting at character 1.
• Case sensitivity: SUBSTITUTE is case-sensitive.
• Best practice: Preserve original data and verify results before replacing source values.

16. Conclusion

SUBSTITUTE and REPLACE are essential text functions for advanced Excel users who need to clean and standardize data efficiently. SUBSTITUTE is ideal when you know the exact word, character, or phrase that must change. REPLACE is more appropriate when you know the position and length of the character segment to modify.

By practicing these functions with product codes, customer records, department names, and report data, learners can reduce manual editing, improve data consistency, and create reliable Excel workflows. Combining both functions can make complex text-cleaning tasks easier to automate and maintain.

Learn Advanced Excel with JustAcademy

Develop your Excel skills through practical exercises and structured learning.

Explore the Advanced Excel Course

Register for a Course Demo

Visit JustAcademy Official Website