Excel 2019 SUBSTITUTE Function: Syntax, Examples, and Tips

Formulas & functions · Updated November 19, 2026

Excel 2019 includes the SUBSTITUTE function for replacing specified text inside a cell with different text. Its syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]); by default it replaces every matching occurrence, or you can specify which occurrence to change.

What SUBSTITUTE Does in Excel 2019

SUBSTITUTE searches a text value for a particular sequence of characters and returns a revised text value. It is useful for standardizing imported data, changing punctuation, removing unwanted characters, or correcting a repeated code across many cells. The original cell is not changed: the function returns its result in the cell containing the formula.

The function is available in Excel 2019; it is not a function that requires Microsoft 365 or a newer Excel release. It works with a whole cell reference or text typed directly into a formula. For example, with the text North-QA-QA in A2, the formula =SUBSTITUTE(A2,"QA","Ops") returns North-Ops-Ops. Because no occurrence number is supplied, both matching instances are replaced.

SUBSTITUTE changes matching text wherever it occurs, including inside a longer word or code. If the old text is a single hyphen, for example, it will replace each hyphen it finds; it does not decide whether that hyphen is being used as punctuation or part of an identifier. Check that the chosen search text is specific enough for the data you have.

Syntax and Arguments

The four arguments tell Excel what to search and what to return. The last argument is optional and is enclosed in square brackets in the syntax description; do not type those brackets into the formula.

  • text: The text, cell reference, or formula result to search.
  • old_text: The exact character sequence to find.
  • new_text: The text to insert in place of the match. Use an empty text string to remove matches.
  • instance_num: An optional positive occurrence number. If supplied, only that occurrence is replaced. If omitted, every occurrence is replaced.

Each text argument needs quotation marks when you type a literal string directly into a formula. A cell reference, such as A2, does not go in quotation marks. The occurrence number is a number, not quoted text. Excel returns text, so even a result that looks numeric may not behave like a number in later arithmetic until you convert it appropriately.

Basic Examples: Replace All or One Occurrence

Suppose A2 contains the text North-QA-QA. With that cell layout, this formula replaces both QA entries and returns North-Ops-Ops: =SUBSTITUTE(A2,"QA","Ops"). It will continue replacing every match if more occur in the cell.

To replace only the second match in the same A2 value, use =SUBSTITUTE(A2,"QA","Ops",2). The result is North-QA-Ops. The occurrence count is based on the order in which matches appear from left to right. If the text contains only one match and you request occurrence 2, there is no second match to change, so the returned text remains unchanged.

With A2 containing Invoice 2025-07, this formula changes hyphens to slashes: =SUBSTITUTE(A2,"-","/"). It returns Invoice 2025/07. This can be helpful for preparing a text label, but it does not convert a date or alter the cell’s number format; it creates a text result.

Removing Text and Combining Replacements

To delete a substring, set new_text to an empty string, written as two quotation marks with nothing between them. With ABC-123 in A2, =SUBSTITUTE(A2,"-","") returns ABC123. This is a convenient way to remove a known separator or unwanted marker. It removes every matching hyphen unless you add an occurrence number.

For two different changes, you can put one SUBSTITUTE function inside another. With Miller, Ana in A2, =SUBSTITUTE(SUBSTITUTE(A2,",","")," ","_") first removes the comma and then replaces the space with an underscore, returning Miller_Ana. Nested formulas are evaluated from the innermost function outward. For several edits, consider using helper columns so each step is easier to inspect and troubleshoot.

SUBSTITUTE also works with line breaks and other characters that are not easy to type directly, when those characters are represented by a suitable function such as CHAR. For example, CHAR(10) represents a line-feed character in Windows Excel text. Such formulas can be useful for cleaning text imported from another system, but the source data and the intended line-break format should be checked before applying the replacement to a large range.

Important Behavior and Common Problems

SUBSTITUTE is case-sensitive. If A2 contains Cat cat CAT, then =SUBSTITUTE(A2,"cat","dog") returns Cat dog CAT: only the lowercase match changes. If capitalization should not matter, normalize the text with a function such as UPPER or LOWER before comparing, or use a different cleaning approach appropriate to the data.

The function searches for literal text, not wildcard patterns. An asterisk or question mark in old_text is treated as a character to find, rather than as a wildcard for any text or any single character. This differs from some uses of Find and Replace in Excel. A frequent source of unexpected results is searching for a space or punctuation mark that is not exactly the same character in every row; imported data can contain nonbreaking spaces or visually similar punctuation.

If the formula appears unchanged, check that the old text actually occurs, that its capitalization matches, and that the selected occurrence number exists. Also check whether Excel is displaying the formula as text: the cell may have been formatted as Text before the formula was entered, or the entry may start with an apostrophe. Set the cell to General if appropriate, then re-enter the formula. A formula result is recalculated from its inputs; it does not permanently edit the source text.

Dates are stored as serial values, and their displayed format is not necessarily the text that SUBSTITUTE searches. If A2 contains an actual date and you want a predictable text label, format it explicitly inside TEXT first. With A2 holding July 4, 2025, the formula =SUBSTITUTE(TEXT(A2,"yyyy-mm-dd"),"-","/") returns the text 2025/07/04. The exact visible date before conversion depends on the value in A2, but the explicit TEXT format defines the output pattern.

SUBSTITUTE Compared with REPLACE

SUBSTITUTE finds a specific sequence of characters. Excel’s separate REPLACE function changes characters at a specified position, whether or not those characters match a particular search term. Choose SUBSTITUTE when the value you want to change is identifiable by its content; choose REPLACE when you know where the section begins and how many characters to overwrite.

With ID-483-XY in A2, =REPLACE(A2,4,3,"927") returns ID-927-XY: it starts at character 4 and replaces three characters. Unlike SUBSTITUTE, REPLACE does not search for the digits 483. If the code length varies, a position-based formula may affect the wrong part, so a content-based SUBSTITUTE may be safer.

These functions are also different from Find and Replace. A formula with SUBSTITUTE creates a live result in a worksheet cell and updates when its source changes. Find and Replace edits matching values directly in the selected range. Use a formula when you want a reproducible transformation or need to preserve the original data; use the dialog when you intentionally want to change existing entries.

Choosing a Method and Applying It to a Column

For a repeatable cleanup, place a formula beside the source data and fill it down. If the source strings are in A2:A100, enter the appropriate SUBSTITUTE formula in B2, then copy or fill it through the rows. Review the outputs before using them elsewhere. When the results are correct and you need fixed values, copy the result cells and use Paste Special > Values; keep the original column until you have confirmed the change.

For a one-time edit to existing cells, Excel 2019 for Windows provides Find and Replace from Home > Find & Select > Replace, or with Ctrl+H. Enter the text to find and its replacement, and use the dialog’s options to control where and how Excel searches. On Excel for Mac, the Replace command is available from Edit > Find > Replace in many versions; menu labels can vary by release. The dialog is convenient, but replacing across an entire workbook can alter unintended cells, so scope the selection and review the choices first.

Before deciding, consider these practical factors:

  • Use SUBSTITUTE if the target is a known substring and you want a formula-based result.
  • Use REPLACE if the change is determined by a character position and length.
  • Use Find and Replace if you want to edit the selected cells rather than create a second output column.
  • Use helper columns for complicated cleanup so each transformation is visible and easier to audit.
  • For numbers, dates, or codes with leading zeros, verify that the result’s text-versus-number behavior is suitable for the next step.

Frequently Asked Questions

Is SUBSTITUTE available in Excel 2019?
Yes. SUBSTITUTE is a standard Excel text function and is included in Excel 2019. Its basic syntax and behavior are the same in that release.

How do I replace only the first occurrence?
Set the optional fourth argument to 1. For example, if A2 contains x-x-x, =SUBSTITUTE(A2,"-","/",1) returns x/x-x.

Can SUBSTITUTE remove a character?
Yes. Set the replacement argument to an empty string, such as =SUBSTITUTE(A2,",","") to remove commas from the text in A2.

Why did SUBSTITUTE leave some letters unchanged?
The function is case-sensitive, so a search for cat does not match Cat or CAT. It also looks for literal characters, so visually similar spaces or punctuation may not be identical.

Does SUBSTITUTE change the original cell?
No. It returns a result in the formula cell and leaves the source cell as it was. To replace source values permanently, use Find and Replace or copy the formula results and paste values over the appropriate cells.

Practical Takeaway

In Excel 2019, use SUBSTITUTE when you need to find exact text within a cell and return a version with that text changed or removed. Omit the fourth argument to replace all matches, or specify an occurrence number to change just one. For position-based edits choose REPLACE; for a one-time direct edit choose Find and Replace. Testing the formula on a few representative rows, especially mixed-case text, dates, and imported codes, helps prevent quiet data-cleaning errors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.