how to use substitute function in google sheets
IN SUMMARY
The SUBSTITUTE function in Google Sheets allows you to replace specific text or characters within a string with new text or characters. It's a powerful tool for data cleaning and manipulation.
Syntax and Arguments
The syntax for the SUBSTITUTE function is: SUBSTITUTE(text, old_text, new_text, [occurrence_number]). The first three arguments are required, while the fourth argument (occurrence_number) is optional.
1. text: The string of text that you want to search and replace within. 2. old_text: The text or characters that you want to replace. 3. new_text: The new text or characters that will replace the old_text. 4. occurrence_number (optional): The instance of the old_text that you want to replace. If omitted, all occurrences will be replaced.
The SUBSTITUTE function is case-sensitive by default. If you need to perform a case-insensitive search and replace, you can use the REGEXREPLACE function with the '(?i)' modifier.
Examples
To replace all spaces with hyphens in a string, use: SUBSTITUTE(A1, ' ', '-'). This will replace all occurrences of spaces with hyphens in the value of cell A1.
To replace only the second occurrence of a specific text, use: SUBSTITUTE(A1, 'old', 'new', 2). This will replace the second instance of 'old' with 'new' in the value of cell A1.
To remove leading and trailing spaces from a string, use: TRIM(SUBSTITUTE(SUBSTITUTE(A1, ' ', REPT(' ', LEN(A1))), REPT(' ', LEN(A1)), '')). This nested SUBSTITUTE function replaces leading and trailing spaces with an empty string.
Tips and Considerations
For more advanced search and replace operations, consider using the REGEXREPLACE function, which supports regular expressions. This allows you to perform complex pattern matching and replacements.
The SUBSTITUTE function can be combined with other functions, such as TRIM, PROPER, LOWER, and UPPER, to perform more complex data transformations.
When working with special characters or wildcards, you may need to escape them using the backslash (\) character to ensure they are treated as literal characters and not interpreted as special characters.