The basic syntax of the SUBSTITUTE function is as follows:
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
text_to_search is the text or the cell reference containing the text you want to change.
search_for is the character or string you want to remove.
replace_with is what you want to replace it with. To remove characters, you would set this to an empty string "".
[occurrence_number] is optional. Use it if you want to replace a specific occurrence of the character/string.
Here's how to use it to remove characters in Google Sheets:
Determine which cell contains the text you want to modify and what specific characters you need to remove from it.
Say, for example, we have the text "Hello World!" in A1.
Click on the cell where you want the modified text to appear. For example, let's choose B1.
Enter the SUBSTITUTE formula:
=SUBSTITUTE(A1, "!", "")
This formula searches for "!" in the text in cell A1 and replaces it with nothing (effectively removing it), leaving you with "Hello World".
Below is the result:
The REGEXREPLACE function is powerful for pattern-based text manipulation. It allows you to specify a regular expression (regex) to match parts of the text you want to replace or remove.
The basic syntax is as follows:
=REGEXREPLACE(text, regular_expression, replacement)
text is the cell reference or the text string you want to search.
regular_expression is the regex pattern that matches the part of the text you want to replace.
replacement is the text you want to insert in place of the matched text. To remove characters, set this to "".
Here's how to use it in Google Sheets:
Determine the cell containing the text you want to modify and what pattern or characters you aim to remove or replace.
Suppose you have a string "123-ABC-xyz" in cell A1 and you want to remove all hyphens and numbers, leaving only letters.
In a new or the same cell (careful not to overwrite data you wish to keep), enter the formula:
=REGEXREPLACE(A1, "[0-9-]", "")
This formula removes all digits (0-9) and hyphens (-) from the text, leaving "ABCxyz".
Here's the result:
The REPLACE function is more straightforward and is best used when you know the exact position of the characters you want to replace.
Its basic syntax is as follows:
=REPLACE(old_text, start_position, num_chars, new_text)
old_text is the text or the cell reference containing the original text.
start_position is the position in the text where the replacement will start.
num_chars is the number of characters to replace.
new_text is the text that will replace the specified substring. To remove characters, use "".
Find out the cell containing the text and determine the exact position of the characters you wish to replace.
For example, you have the text "123ABC" in cell A1.
Click on a new or the same cell, then type the formula:
=REPLACE(A1, 1, 3, "")
This formula replaces the first 3 characters of "123ABC" with nothing, resulting in "ABC".
This is the result:
To remove the first n characters from a text string in Google Sheets, you can use the RIGHT function combined with the LEN function:
=RIGHT(text, LEN(text) - n)
This formula works by first calculating the length of the entire text, then subtracting the number of characters you want to remove. The RIGHT function is then used to extract the remaining number of characters from the right end of the text string.
If you have the text "HelloWorld" in cell A1 and you want to remove the first 5 characters, your formula would look like this:
=RIGHT(A1, LEN(A1) - 5)
This would return "World" as the result since the first 5 characters ("Hello") are removed.
To remove the last n characters from a text string in Google Sheets, you can use the LEFT function in combination with the LEN function.
=LEFT(text, LEN(text) - n)
If the text "HelloWorld" is in cell A1 and you wish to remove the last 5 characters, you would use:
=LEFT(A1, LEN(A1) - 5)
This formula will return "Hello" because it removes the last 5 characters ("World") from the text.
We hope that this article has helped you and given you a better understanding of how to remove characters in Google Sheets. If you enjoyed this article, you might also want to check out our Eisenhower matrix in Google Sheets and how to write a recursive formula in Google Sheets.
