---
title: "SUBSTITUTE function in Google Sheets: syntax and examples"
canonical: "https://www.owox.com/blog/articles/substitute-function-google-sheets"
updated: "2026-10-05"
---

# SUBSTITUTE function in Google Sheets: replace text, the nth occurrence and multiple values

SUBSTITUTE replaces every occurrence of a text inside a cell with another text, or only the occurrence you number. This guide covers the syntax, removing characters, substituting multiple values with nested formulas or a lookup table, counting occurrences, the difference from REPLACE and REGEXREPLACE, and the behaviours that surprise: it is case-sensitive, has no wildcards and always returns text.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 7 min read

![SUBSTITUTE function in Google Sheets: replace text, the nth occurrence and multiple values](https://cdn.owox.ai/www/webflow/6a149aa29f6164bdce054b03_THE-SUBSTITUTE-FUNCTION-IN-GOOGLE-SHEETS.png/public)

SUBSTITUTE replaces a text inside a cell with another text:

> `=SUBSTITUTE(C3, "/", " ")`

Every slash in C3 becomes a space. A fourth argument limits the replacement to one occurrence: `=SUBSTITUTE(C3, "/", " ", 1)` changes only the first slash.

## SUBSTITUTE syntax

> `=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])`

*   **text\_to\_search:** the text, or the cell that holds it.
*   **search\_for:** the text to find. It is matched exactly, with its case.
*   **replace\_with:** the text to put in its place. An empty text, `""`, removes what was found.
*   **occurrence\_number:** optional. Which occurrence to replace: 1 for the first, 2 for the second. Without it, all are replaced.

> `=SUBSTITUTE("James Carter", "Carter", "Johnson")`

![Google Sheets with the formula =SUBSTITUTE(“James Carter”, “Carter”, “Johnson”) returning James Johnson](https://cdn.owox.ai/www/webflow/67f3c8c9c87d4fde9df9af3f_AD_4nXeGa357cY7oER0iFuNwgM3NNXSa-Y9TAVwlsFODkMByUtSgdA4_Xf-JOX6Uso-KUC-EsNrNPopkIhe68LseQqiXETzlByTpHvTWqMJPKk69BhCWh5JvdzATDEG-1CD5KTP8IOiFCQ.png/public)

With a cell reference, the formula can be filled down a column. Rows that do not contain the text come back unchanged:

> `=SUBSTITUTE(D6, "Carter", "Miller")`

![Google Sheets with a column of reporting managers and the formula =SUBSTITUTE(D6, “Carter”, “Miller”) changing James Carter to James Miller and leaving the other names as they are](https://cdn.owox.ai/www/webflow/67f3c8c9efdacbef9eb5c93a_AD_4nXfWrqDjvIBoa6ZjhbI22HrdpxouC6W9wv1ncCsFPFd4VgC3cLPMLrAlIN0o5-Xl1VCGyjxvnxmqcd3Nw4z8S-vlCotqJN3lYaGRZwGyytuAt4P-MY0BOc-Xd9c0vvlSET6Y7CQnhg.png/public)

## What SUBSTITUTE does and does not do

All of the following comes from our test sheet.

*   **It is case-sensitive.** `=SUBSTITUTE("Apple apple", "apple", "x")` returns “Apple x”.
*   **It has no wildcards and no regular expressions.** An asterisk, a question mark and a dot are ordinary characters: `=SUBSTITUTE("a*b", "*", "-")` returns “a-b”. Nothing needs escaping.
*   **A text that is not found is not an error.** The original comes back unchanged. The same happens when the occurrence number is larger than the number of occurrences.
*   **It always returns text.** `=SUBSTITUTE(1500, "5", "9")` returns “1900” as a text, which SUM ignores. Wrap the result in VALUE to calculate with it.
*   **On a date it works on what the cell displays.** With 1/5/2023 in B3, `=SUBSTITUTE(B3, "2023", "24")` returned the text “1/5/24”.
*   **Matches do not overlap.** `=SUBSTITUTE("aaa", "aa", "b")` returns “ba”.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Replace or remove text

### Replace every occurrence

> `=SUBSTITUTE(B3, " / ", " ")`

The text to find here is the slash with the spaces around it, so that one space remains where there were three characters.

![Google Sheets with values such as 101 / John Doe / Sales and the formula =SUBSTITUTE(B3, “ / “, “ “) returning 101 John Doe Sales](https://cdn.owox.ai/www/webflow/67f3c8c9a87bc6cb5059c655_AD_4nXdrvTmhXpbYMAmesoiMM48Deh4W9WhsdGVbzDE4U_niZEWThT6uAijLW6Knz7BDuLoFh0k1_LOKzOFJLyvgLTdJIBYC0PzQnVDcQXmhs-TXRGcAuvmxOwNv7FDeEkLFzVstb_KT.png/public)

### Replace only the first occurrence

> `=SUBSTITUTE(B3, " / ", " ", 1)`

![Google Sheets with the same values and the occurrence number 1 in SUBSTITUTE, returning 101 John Doe / Sales with the second slash kept](https://cdn.owox.ai/www/webflow/67f3c8c9a87bc6cb5059c652_AD_4nXeGwLZ7HfqhMjWdkmnXsKrBcLi3lshC_ZVqI1pC_OKGFoTXRFc1wWhIs4m1H7ZQf0qh_odfhzy01Hl8O77tcLOsLL5vOiRj3B2q7hEzSp1Iyq0Qv6PMWdOeNi5u08kUylVpWnZG.png/public)

### Replace the second occurrence

> `=SUBSTITUTE(D3, "excellent", "outstanding", 2)`

![Google Sheets with customer reviews that use the word excellent twice and the formula =SUBSTITUTE(D3, “excellent”, “outstanding”, 2) changing only the second one](https://cdn.owox.ai/www/webflow/67f3c8c92396eec868813ea4_AD_4nXen2k5RWNhryMdLp2be4zZ1CPccc-xBkVlISHBPMNe6mehm6Ez0pOjJe65IoONcNE_K6IcoOx5bTYjWnstxRJvjVtGMGcuFvA2OMTfmt46qQ6OeV7eM__poKJZGLFsBXa4k3gW2Zw.png/public)

### Replace a phrase

The text to find can be several words. Including a neighbouring word is a way to hit one occurrence without counting:

> `=SUBSTITUTE(D3, "truly excellent", "truly outstanding")`

![Google Sheets with a review that reads excellent, truly excellent and a SUBSTITUTE formula on the phrase truly excellent changing only the second mention](https://cdn.owox.ai/www/webflow/67f3c8c95f78eba60ecc8e43_AD_4nXcBazol28qnkD8seb3wLekt2pI2lQJUGz1_hmeh5EmgvYkaFIIinESKaJIwDqhgDzSWBZn2Z_EI9RwvAjw11E8SrOEwPAm3AK1Y3ZGlWmzYINK9mGvVHdqyIfOS2dBkjUbxI41NHQ.png/public)

### Remove characters

Replace with an empty text. Each SUBSTITUTE removes one character, so several are nested:

> `=SUBSTITUTE(SUBSTITUTE(C3, "(", ""), ")", "")`

![Google Sheets with phone numbers in mixed formats and the formula =SUBSTITUTE(SUBSTITUTE(C3, “(”, “”), “)”, “”) returning them without parentheses](https://cdn.owox.ai/www/webflow/67f3c8c997b4fa58a2342ec8_AD_4nXeYPAu1r4ZrET3vF9UdhPQMTCF-UaNnQgmsB8KX1-UHXcy2BP0nuVJMl8m5w3QdVP7d--yCmACzIPQlCmlFN_1kAd1O398oSfABR2Agen0TNkKnDWW9HJXLxvrgjOpZwSK6ZIq_Cw.png/public)

To remove any character of a set in one step, [REGEXREPLACE](/blog/articles/regex-functions-google-sheets) is shorter: `=REGEXREPLACE(C3, "[()\- .]", "")` removes parentheses, hyphens, spaces and dots at once.

To turn a text such as “1,500 USD” into a number, remove the unit and convert: `=VALUE(SUBSTITUTE(C3, " USD", ""))`.

### A whole column from one formula

> `=ARRAYFORMULA(SUBSTITUTE(B3:B10, "OLD-", "NEW-"))`

![Google Sheets with employee IDs from OLD-101 to OLD-108 and the formula =ARRAYFORMULA(SUBSTITUTE(B3:B10, “OLD-”, “NEW-”)) returning NEW-101 to NEW-108](https://cdn.owox.ai/www/webflow/67f3c8c910ba1272d559538e_AD_4nXdA70ABAtTK9_ouOJmJj5YE6czhY0kA5t6cExHLAVQj-oDTjwyF5j3enmHnpw1kK1OGrwAXef2-WnAAa3KwBe2BDuMmZgTmSndqVnkP3kTXIWTrUcVf5BkGLPgj-lCkEg4F-zST.png/public)

See [array formulas](/blog/articles/array-formulas-google-sheets).

## Substitute multiple values

### A few values: nest the formulas

Each SUBSTITUTE works on the result of the one inside it:

> `=SUBSTITUTE(SUBSTITUTE(C3, "John", "J."), "Sarah", "S.")`

The order matters when one replacement produces a text that the next one looks for.

### Many values: a table of pairs

Keep the pairs in a table, what to find in one column and the replacement in the next, and let REDUCE apply them one after another:

> `=REDUCE(C3, SEQUENCE(ROWS(B13:B20)), LAMBDA(acc, i, SUBSTITUTE(acc, INDEX(B13:B20, i), INDEX(C13:C20, i))))`

REDUCE starts with the text in C3 and runs SUBSTITUTE once for every row of the table, each time on the result of the previous step. In our test the same construction turned “John Doe and Jane Smith” into “JD and JS”. A new pair is a new row in the table; the formula stays as it is.

The article’s original example writes the same chain out by hand, one nested SUBSTITUTE with INDEX for each row of the table:

![Google Sheets with a table of names and their short forms in B13:C20 and a nested SUBSTITUTE formula with INDEX returning JD for John Doe, JS for Jane Smith and so on](https://cdn.owox.ai/www/webflow/67f3c8c9dc3c3babf4fc19f7_AD_4nXewOoYJJQKmHC-3PKNrQbi4zcmS0mOCWNZxhJMywyhr0185THXpFew20rQ6_iPRYHNyvYdKMNfnD1K39S0FpDEleC1p3sJeuj6BZlGTsiWjtbfFV8VVTDe4rVendbrmWRNYbS80oA.png/public)

### When the whole cell is the value

If the cell contains nothing but the value to translate, “John Doe” to “JD”, this is a lookup, not a substitution: `=VLOOKUP(C3, B13:C20, 2, FALSE)`. See [VLOOKUP](/blog/articles/vlookup-in-google-sheets).

## SUBSTITUTE with other functions

### Ignore the case

Change the case of the text first, if the result may be all upper or lower case:

> `=SUBSTITUTE(UPPER(D3), "EXCELLENT", "OUTSTANDING")`

![Google Sheets with reviews and the formula =SUBSTITUTE(UPPER(D3), “EXCELLENT”, “OUTSTANDING”) returning the reviews in capitals with the word replaced](https://cdn.owox.ai/www/webflow/67f3c8c9f5cfbefd3fe059ed_AD_4nXeQB3weix6RiqimIMdDwXketIoUNPnXp76olUXOU_fvSldCo1u3PPlP40Mw1oPPd-KSc6iVdO5HulIpLppQ1P4qrs86ORfrmKZ8WDiZVu0o9VgmTPMr9-iIOpXI4hpWNRhYFZxxZw.png/public)

To keep the rest of the text as it was, use REGEXREPLACE with the case-insensitive flag: `=REGEXREPLACE("Apple apple", "(?i)apple", "x")` returns “x x”.

### Count how many times a text occurs

Remove the text and compare the lengths:

> `=(LEN(C3) - LEN(SUBSTITUTE(C3, "/", ""))) / LEN("/")`

For “a/b/c” the formula returns 2. See [LEN](/blog/articles/len-function-google-sheets).

### Line breaks and invisible spaces

Characters that cannot be typed are given by their code with [CHAR](/blog/articles/char-function-google-sheets): `=SUBSTITUTE(C3, CHAR(10), " ")` turns line breaks into spaces, and `=SUBSTITUTE(C3, CHAR(160), " ")` turns non-breaking spaces into ordinary ones, which [TRIM](/blog/articles/trim-clean-t-functions-google-sheets) can then remove.

## SUBSTITUTE, REPLACE or REGEXREPLACE

|  | Finds the text by | Use it when |
| --- | --- | --- |
| SUBSTITUTE | What it is | You know the text to replace |
| [REPLACE](/blog/articles/replace-function-google-sheets) | Position and length | You know where it is: characters 4 to 6 |
| [REGEXREPLACE](/blog/articles/regex-functions-google-sheets) | A pattern | You know its shape: any digits, anything in brackets |

To change the values in place, without a formula column, use [Find and replace](/blog/articles/how-to-find-and-replace-google-sheets).

## Problems and fixes

**⚠️ Problem:** Nothing is replaced.

✅ **Solution:** The text was not found. Check the case, and check for a space or an invisible character inside the cell; a non-breaking space looks like a space and does not match one.

**⚠️ Problem:** Numbers no longer add up.

✅ **Solution:** The result is text. Wrap it in VALUE.

**⚠️ Problem:** #VALUE!

✅ **Solution:** The occurrence number is negative. Use 1 or more, or leave it out.

**⚠️ Problem:** Too much is replaced: “cat” also changes “category”.

✅ **Solution:** SUBSTITUTE has no notion of a word. Include the spaces around the word in both texts, or use REGEXREPLACE with a word boundary, `"\bcat\b"`.

**⚠️ Problem:** #REF! from an ARRAYFORMULA.

✅ **Solution:** The result has no room to expand. Clear the cells below the formula.

**⚠️ Problem:** #ERROR! after pasting a formula.

✅ **Solution:** The quotation marks are curly. Retype them as straight quotes.

## Related Google Sheets functions

*   [REPLACE](/blog/articles/replace-function-google-sheets): Replace by position.
*   [REGEX functions](/blog/articles/regex-functions-google-sheets): Replace by pattern.
*   [Find and replace](/blog/articles/how-to-find-and-replace-google-sheets): The dialog, with no formulas.
*   [TRIM, CLEAN and T](/blog/articles/trim-clean-t-functions-google-sheets): Remove spaces and hidden characters.
*   [UPPER, LOWER and PROPER](/blog/articles/upper-lower-proper-functions-google-sheets): Change the case.
*   [LEFT, RIGHT and MID](/blog/articles/left-right-mid-functions-google-sheets): Take a part of a text.

## A replacement table in a sheet is a mapping that belongs to the data

Old IDs to new ones, campaign names to channels, long names to short ones: a table of replacements applied by formulas is a business rule kept in one spreadsheet. The next report needs the same rule and gets a copy of it, and the copies drift apart as soon as one of them gets a new row.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/substitute-function-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. When the mapping is applied once in that SQL, every report built on the data mart shows the same names, and SUBSTITUTE is left for one-off fixes in a sheet. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Can I use SUBSTITUTE and REPLACE together in the same formula?

Yes, SUBSTITUTE and REPLACE can be combined for advanced text manipulations, such as modifying specific positions and replacing targeted strings within a single formula.

Can I use SUBSTITUTE to remove text in Google Sheets?

Yes, SUBSTITUTE can remove text by replacing it with an empty string (""). This is useful for eliminating unwanted characters or words from cells.

What’s the difference between REPLACE and SUBSTITUTE in Google Sheets?

REPLACE modifies text based on its position in the cell, while SUBSTITUTE identifies and replaces specific text strings within a cell. Both are useful for different needs.

Is SUBSTITUTE case-sensitive?

Yes. SUBSTITUTE matches the text with its case: =SUBSTITUTE("Apple apple", "apple", "x") returns Apple x. To replace regardless of case, use REGEXREPLACE with the (?i) flag, or change the case of the text first with LOWER or UPPER.

Can SUBSTITUTE be used to replace only specific occurrences of text?

Yes, SUBSTITUTE allows you to specify which occurrence of the text to replace using the optional occurrence number parameter in the formula.

How do I use the SUBSTITUTE function in Google Sheets?

Give it the text or cell, the text to find and the replacement: =SUBSTITUTE(C3, "/", " ") replaces every slash with a space. A fourth argument, the occurrence number, replaces only that occurrence. The result is always text.

What is the SUBSTITUTE function in Google Sheets?

The SUBSTITUTE function replaces specific text within a string with another text. It’s ideal for cleaning or standardizing data by dynamically updating text without manual changes.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets)

[Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](/blog/articles/unique-function-google-sheets)

[Google Sheets Tips · How to search in Google Sheets: Ctrl+F and the SEARCH function · October 2, 2026](/blog/articles/search-function-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [REGEXREPLACE](https://www.owox.com/blog/articles/regex-functions-google-sheets) — /blog/articles/regex-functions-google-sheets.md
- [array formulas](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md
- [VLOOKUP](https://www.owox.com/blog/articles/vlookup-in-google-sheets) — /blog/articles/vlookup-in-google-sheets.md
- [LEN](https://www.owox.com/blog/articles/len-function-google-sheets) — /blog/articles/len-function-google-sheets.md
- [CHAR](https://www.owox.com/blog/articles/char-function-google-sheets) — /blog/articles/char-function-google-sheets.md
- [TRIM](https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets) — /blog/articles/trim-clean-t-functions-google-sheets.md
- [REPLACE](https://www.owox.com/blog/articles/replace-function-google-sheets) — /blog/articles/replace-function-google-sheets.md
- [Find and replace](https://www.owox.com/blog/articles/how-to-find-and-replace-google-sheets) — /blog/articles/how-to-find-and-replace-google-sheets.md
- [UPPER, LOWER and PROPER](https://www.owox.com/blog/articles/upper-lower-proper-functions-google-sheets) — /blog/articles/upper-lower-proper-functions-google-sheets.md
- [LEFT, RIGHT and MID](https://www.owox.com/blog/articles/left-right-mid-functions-google-sheets) — /blog/articles/left-right-mid-functions-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md
- [Google Sheets Tips · UNIQUE function in Google Sheets: syntax, examples and errors · October 2, 2026](https://www.owox.com/blog/articles/unique-function-google-sheets) — /blog/articles/unique-function-google-sheets.md
- [Google Sheets Tips · How to search in Google Sheets: Ctrl+F and the SEARCH function · October 2,…](https://www.owox.com/blog/articles/search-function-google-sheets) — /blog/articles/search-function-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md