Excel SUBSTITUTE Function: Replace Text and Clean Up Data Easily

If you’ve ever found yourself spending far too much time fixing typos, updating codes, or cleaning up data in Excel, you’re not alone! Many business professionals I work with know how frustrating it can be to manually search and replace text—especially when you’re dealing with large spreadsheets or recurring data issues.

The Excel SUBSTITUTE function is your solution for automatic find and replace. Instead of manually updating your data every time something changes, SUBSTITUTE allows you to set up a formula that automates the process for you. Whenever your source data is updated, your replacements happen instantly—no more repetitive manual edits!

For example, imagine you receive a supplier report where the status is sometimes “Dispatched”, sometimes “Sent”, and sometimes “shipped”. Instead of running Find and Replace three times, you can use the Excel SUBSTITUTE function to automatically standardise all these terms to “Shipped” in one go. It’s a real game-changer for anyone who wants to keep their data clean, accurate, and ready for analysis—without the headache of manual editing.

Excel Practice Files

What Does SUBSTITUTE Do?

• The SUBSTITUTE function replaces specific text within a string with new text.
• You can choose to replace all instances or just a specific occurrence.

Syntax

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

• text: The cell or text you want to change.
• old_text: The text you want to replace.
• new_text: The text you want to use instead.
• [instance_num]: (Optional) Which occurrence to replace. If omitted, all occurrences are replaced.

Example 1: Automatically Correct a Commonly Misspelled Word

Let’s say your team often enters “recieve” instead of “receive” in order notes or customer communications. Instead of manually fixing each mistake, you can use the SUBSTITUTE function to automatically correct it in a new column.

Formula: In cell B2, =SUBSTITUTE(A2, “recieve”, “receive”)

Explanation: This formula searches for every instance of “recieve” in cell A2 and replaces it with the correct spelling, “receive”.

Formula: In cell B3, =PROPER(SUBSTITUTE(A3, “recieve”, “receive”))

Explanation: This formula searches for every instance of “recieve” and replaces it with the correct spelling, “receive”. The PROPER function is used to ensure the word appears with a capital letter.

Business Scenario: Perfect for anyone who wants to ensure professional, error-free reports—without manual editing.

Skill Level: Beginner

Example 2: Standardise Multiple Status Terms at Once

If you receive data from different sources, you might find the same status described in several ways—like “Dispatched”, “Sent”, or “shipped”. Standardising these terms is essential for accurate reporting, but Find and Replace can only do one at a time, so you would need to repeat the process three times! With nested SUBSTITUTE functions, you can tidy up all variations in a single formula.

Formula: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, “Dispatched”, “Shipped”), “Sent”, “Shipped”), “shipped”, “Shipped”)

Explanation: This formula replaces “Dispatched”, “Sent”, and “shipped” (lowercase) with “Shipped” in cell A2, standardising your data in one go.

Business Scenario: Perfect for admin or logistics staff who need to clean up and standardise supplier or status data for reporting.

Skill Level: Intermediate

Example 3: Automatically Correct Date Format in Data Entry

If your team sometimes enters dates as 23.11.25 instead of 23/11/2025, you can use Excel to automatically convert these to the correct format in a new column. This ensures your formulas and reporting work as expected, without manual editing.

Formula: =VALUE(SUBSTITUTE(A2, “.”, “/”))

Explanation: SUBSTITUTE replaces all dots with slashes, turning 23.11.25 into 23/11/2025. The VALUE function converts the text string into an actual Excel date value. Format the result column as a date (e.g. dd/mm/yyyy) for proper display and calculations.

Business Scenario: Perfect for admin, sales, or logistics staff who need to clean up inconsistent date entries for reporting, filtering, or calculations—without manual editing.

Skill Level: Intermediate

Example 4: Removing Soft Returns (Line Breaks) from Cells

If you’ve ever copied data from emails or web forms into Excel, you might have noticed that some cells contain unexpected line breaks (soft returns). These can make your data look messy and cause problems when sorting or analysing. Luckily, you can use the SUBSTITUTE function with the CHAR function to remove these line breaks and replace them with a comma and space, making your data much easier to read and work with.

Formula: =SUBSTITUTE(C2, CHAR(10), “, “)

Explanation: This formula finds every line break (represented by CHAR(10)) in cell C2 and replaces it with a comma and space, so each item appears neatly separated on a single line.

Business Scenario: Perfect for admin or sales staff who need to clean up addresses, comments, or notes that have been pasted into Excel with unwanted line breaks, making the data ready for export or reporting.

Skill Level: Intermediate

Related Blog & Video: Use TRIM with SUBSTITUTE to Clean Up Messy Data

Have you ever imported data from a website or external system, only to find that the TRIM function doesn’t remove all the extra spaces? That’s likely because the data contains non-breaking spaces (also known as hard spaces), which TRIM can’t detect.

In my blog “Use TRIM with SUBSTITUTE Function in Excel to Remove Spaces the TRIM Function Misses”, I’ll show you how to use the SUBSTITUTE function to replace these hard spaces (represented by CHAR(160)) with regular spaces—so TRIM can do its job correctly.

👉 If you’re dealing with messy customer lists, addresses, or product descriptions, this tip will save you loads of time and frustration. Go check it out!

Troubleshooting SUBSTITUTE

When working with the Excel SUBSTITUTE function, you may occasionally encounter unexpected results or error messages. Understanding the most common issues and how to resolve them will help you use the SUBSTITUTE function with confidence and accuracy. Let’s look at some typical problems and their solutions.

Text Not Replaced

Sometimes the function doesn’t seem to work because the text you want to replace doesn’t match exactly. Often this is due to the text being entered in a different case, e.g. UPPER, lower, or Proper case (case-sensitive).

Example:
=SUBSTITUTE(A2, “cancelled”, “completed”)
If A2 contains “Cancelled” (with a capital C), it won’t match “cancelled”.

Fix:

• Make sure your old_text matches the case and spelling exactly.
• Catch every case for the word using a nested SUBSTITUTE formula, e.g.
  =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, “cancelled”, “completed”), “Cancelled”, “completed”), “CANCELLED”, “completed”)

Only Some Occurrences Replaced

If you use the [instance_num] argument, only that specific occurrence is replaced.

Example:
=SUBSTITUTE(A2, “Free”, “Complimentary”, 1)
If the cell contained the text “Free Sample with Free Shipping”, only the first “Free” is changed. The result will be “Complimentary Sample with Free Shipping”

Fix:
Remove the [instance_num] argument to replace all occurrences.

Excel SUBSTITUTE Function FAQs

Q. Can SUBSTITUTE replace multiple different words at once?
A. No, SUBSTITUTE only replaces one word or phrase at a time. Use multiple SUBSTITUTE functions nested together for more replacements.

Q. Is SUBSTITUTE case-sensitive?
A. Yes, it matches the exact case of the old_text.

Q. What’s the difference between SUBSTITUTE and REPLACE?
A. SUBSTITUTE replaces specific text, while REPLACE changes text based on position (start and number of characters).

Q. Can I use SUBSTITUTE on numbers?
A. Yes, but it treats numbers as text. Ensure your cell is formatted as text if necessary.

Related Functions

• REPLACE – Change text based on position and length
• TEXT – Format numbers as text
• LEFT, RIGHT, MID – Extract parts of text
• FIND, SEARCH – Locate text within a string
• CONCAT, TEXTJOIN – Combine text from multiple cells

Excel Practice File Download

Conclusion – Excel Substitute Function

The Excel SUBSTITUTE function is a great way to tidy up and standardise your data without the hassle of manual editing or repeatedly using Find and Replace. Once you master it, you’ll save time, minimise errors, and feel much more confident managing your spreadsheets. Give it a go —you’ll be surprised at how much easier your work becomes!