Blog

The SUBSTITUTE function in Excel is your go-to when you need to replace specific text within a string. Whether you're cleaning up data, replacing common mistakes, or updating specific values, SUBSTITUTE makes the job super easy. Imagine you have a list of names, but the formatting isn’t quite right, or you need to replace all instances of a certain word in your data—SUBSTITUTE can handle it with ease. Let’s dive into how it works!

Syntax

Here's the syntax for the SUBSTITUTE function, which you can use to replace text in a cell:

• text: The original text or cell reference containing the text.
• old_text: The text you want to replace.
• new_text: The text you want to substitute in place of old_text.
• [instance_num]: (Optional) Specifies which occurrence of the old_text to replace if it appears more than once. If omitted, all occurrences will be replaced.

Example

Let’s say you have a dataset that includes various addresses, but all the street abbreviations are inconsistent. You want to replace "St" with "Street" and "Rd" with "Road" to standardize the format.

In this example, the SUBSTITUTE function replaces "St" with "Street" and "Rd" with "Road" to standardize the addresses.

Practice Exercise 1

Task: You have a list of product codes, but some contain hyphens that need to be replaced with underscores for consistency. Use the SUBSTITUTE function to replace all hyphens (-) with underscores (_).

Hint: Use the SUBSTITUTE function to replace each hyphen with an underscore to match the required format.

Practice Exercise 2

Task: You are given a list of full names, but some names include a middle initial that needs to be removed. The middle initial is always surrounded by spaces, so you need to replace the middle initial with an empty string using the SUBSTITUTE function.

Hint: Use the SUBSTITUTE function to remove the middle initial by replacing it with a space.

Solution to Exercise 1

Replacing hyphens with underscores in product codes:

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

Solution to Exercise 2

Removing middle initials from full names:

=SUBSTITUTE(A2, " A ", " ")
=SUBSTITUTE(A3, " B ", " ")
=SUBSTITUTE(A4, " C ", " ")

The SUBSTITUTE function is incredibly handy when you need to clean up or standardize data quickly. Whether it's removing unwanted characters or replacing parts of a string, this function gets the job done with minimal effort!