MY_REPLACE
Replace all occurrences of a substring in text without specifying occurrence numbers.
=MY_REPLACE(text, search, replacement)Retorna: string
Visão Geral
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.
Parâmetros
| Parâmetro | Tipo | Obrigatório | Descrição |
|---|---|---|---|
text | string | Sim | The original text to modify. |
search | string | Sim | The substring to find and replace. |
replacement | string | Sim | The text to insert in place of each match. |
Exemplos
Replace spaces with underscores
Convert spaces to underscores for file naming or URL-safe strings.
=MY_REPLACE("Hello World Today", " ", "_")Saída
Hello_World_TodayRemove unwanted characters
Strip dollar signs from price strings for numeric processing.
=MY_REPLACE("Price: $49.99", "$", "")Saída
Price: 49.99Standardize date separators
Convert date format from slash-separated to dash-separated.
=MY_REPLACE("2024/01/15", "/", "-")Saída
2024-01-15Replace abbreviations with full text
Expand abbreviations throughout a text string.
=MY_REPLACE("Dept of Sales, Dept of Marketing", "Dept", "Department")Saída
Department of Sales, Department of MarketingClean phone number formatting
Replace hyphens with dots in phone number formatting.
=MY_REPLACE("(555) 123-4567", "-", ".")Saída
(555) 123.4567Casos de Uso
Data cleaning on import
Clean imported data by removing unwanted characters, standardizing separators, or normalizing text formats.
URL and slug generation
Transform page titles into URL-friendly slugs by replacing spaces with hyphens and removing special characters.
Report formatting
Strip currency symbols, commas, or percentage signs from financial data for calculation purposes.
Template processing
Replace placeholder tokens in email or document templates with actual values for personalized output.
Dicas Profissionais
Chain multiple MY_REPLACE calls to perform several replacements in sequence: =MY_REPLACE(MY_REPLACE(text, "old1", "new1"), "old2", "new2").
Use empty string "" as replacement to delete all occurrences of a substring.
For case-insensitive replacement, wrap the text in LOWER() first if case does not matter in the output.
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.
Erros Comuns
Search string not being replacedCausa: The search is case-sensitive and the case does not match the actual text.
Correção: Verify the exact casing of the search string matches the text. Use LOWER() on both if case-insensitive matching is needed.
Returns empty string unexpectedlyCausa: The text parameter was empty, null, or referenced an empty cell.
Correção: Check that the text parameter references a cell with content or contains a non-empty string literal.
Perguntas Frequentes
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.
Funções Relacionadas
Comece a usar MY_REPLACE hoje
Instale o Unlimited Sheets para obter MY_REPLACE e outras 41 funções poderosas no Google Sheets.
