🛠️ UtilitiesFree Piano

MY_REPLACE

Replace all occurrences of a substring in text without specifying occurrence numbers.

Firma della Formula

=MY_REPLACE(text, search, replacement)

Restituisce: string

Panoramica

MY_REPLACE is a straightforward text replacement function that finds and replaces all occurrences of a substring within a text string. Unlike Google Sheets' built-in SUBSTITUTE function which can optionally target a specific occurrence number, MY_REPLACE always replaces every match -- making it simpler and more predictable for bulk replacements.

Parametri

Parametro	Tipo	Obbligatorio	Descrizione
`text`	string	Sì	The original text to modify.
`search`	string	Sì	The substring to find and replace.
`replacement`	string	Sì	The text to insert in place of each match.

Esempi

Replace spaces with underscores

Convert spaces to underscores for file naming or URL-safe strings.

=MY_REPLACE("Hello World Today", " ", "_")

Output

Hello_World_Today

Remove unwanted characters

Strip dollar signs from price strings for numeric processing.

=MY_REPLACE("Price: $49.99", "$", "")

Output

Price: 49.99

Standardize date separators

Convert date format from slash-separated to dash-separated.

=MY_REPLACE("2024/01/15", "/", "-")

Output

2024-01-15

Replace abbreviations with full text

Expand abbreviations throughout a text string.

=MY_REPLACE("Dept of Sales, Dept of Marketing", "Dept", "Department")

Output

Department of Sales, Department of Marketing

Clean phone number formatting

Replace hyphens with dots in phone number formatting.

=MY_REPLACE("(555) 123-4567", "-", ".")

Output

(555) 123.4567

Casi d'Uso

Data Analytics

Data cleaning on import

Clean imported data by removing unwanted characters, standardizing separators, or normalizing text formats.

Web Development

URL and slug generation

Transform page titles into URL-friendly slugs by replacing spaces with hyphens and removing special characters.

Finance

Report formatting

Strip currency symbols, commas, or percentage signs from financial data for calculation purposes.

Marketing

Template processing

Replace placeholder tokens in email or document templates with actual values for personalized output.

Suggerimenti Professionali

SUGGERIMENTO

Chain multiple MY_REPLACE calls to perform several replacements in sequence: =MY_REPLACE(MY_REPLACE(text, "old1", "new1"), "old2", "new2").

SUGGERIMENTO

Use empty string "" as replacement to delete all occurrences of a substring.

SUGGERIMENTO

For case-insensitive replacement, wrap the text in LOWER() first if case does not matter in the output.

SUGGERIMENTO

Combine with TRIM() to clean up extra spaces after replacements: =TRIM(MY_REPLACE(text, " ", " ")).

The function works by splitting the text on every occurrence of the search string and rejoining with the replacement string. This approach ensures that every instance is replaced in a single operation. The search is case-sensitive, matching the behavior most users expect for text transformations.

MY_REPLACE is ideal for data cleaning tasks like standardizing formats, removing unwanted characters, transforming URL slugs, cleaning imported data, and performing batch text corrections. It handles edge cases gracefully: if the text is empty or null, it returns an empty string; if the search string is not found, the original text is returned unchanged.

For more advanced text transformations like URL slug creation, see the SLUGIFY function. For splitting text into separate columns, use SPLIT_IN_COLUMNS. MY_REPLACE is designed to be the simplest possible "find and replace all" function -- no occurrence parameters, no regex complexity, just straightforward string replacement.

Errori Comuni

Search string not being replaced

Causa: The search is case-sensitive and the case does not match the actual text.

Soluzione: Verify the exact casing of the search string matches the text. Use LOWER() on both if case-insensitive matching is needed.

Returns empty string unexpectedly

Causa: The text parameter was empty, null, or referenced an empty cell.

Soluzione: Check that the text parameter references a cell with content or contains a non-empty string literal.

Domande Frequenti

Yes, MY_REPLACE performs case-sensitive matching. "Apple" and "apple" are treated as different strings. If you need case-insensitive replacement, convert both text and search to the same case first using LOWER() or UPPER().

MY_REPLACE always replaces all occurrences. SUBSTITUTE has an optional fourth argument to replace only a specific occurrence number. If you always want to replace everything, MY_REPLACE is simpler and more readable.

Yes, passing "" (empty string) as the replacement effectively deletes all occurrences of the search string. For example, =MY_REPLACE("Hello World", " World", "") returns "Hello".

The original text is returned unchanged. No error is thrown.

Yes, nest MY_REPLACE calls: =MY_REPLACE(MY_REPLACE(A1, " ", "-"), "&", "and"). Each replacement is applied to the result of the previous one.

No, MY_REPLACE performs literal string matching only. For regex-based replacements, use Google Sheets' REGEXREPLACE function.

If the text parameter is empty or null, MY_REPLACE returns an empty string without any error.

Funzioni Correlate

🔍SEO ToolsFree

SLUGIFY

Convertire testo in slug compatibile con URL (rimuove accenti, minuscole, trattini)

🛠️UtilitiesFree

SPLIT_IN_COLUMNS

Split text into multiple horizontal columns by a separator with automatic trimming.

Inizia a usare MY_REPLACE oggi

Installa Unlimited Sheets per ottenere MY_REPLACE e altre 41 potenti funzioni in Google Sheets.

Installa Add-on Gratuito Visualizza Documentazione