In this article:

How to Remove Characters in Google Sheets (2024 Guide)

May 8, 2024

How to Remove Characters in Google Sheets Using the SUBSTITUTE Function

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:

Step 1: Identify the Cell and Characters to Remove

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.

google sheets remove characters from string

Step 2: Use the SUBSTITUTE Function

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:

how to remove characters in google sheets

How to Remove Characters in Google Sheets Using the REGEXREPLACE Function

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:

Step 1: Identify Text and Pattern

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. 

google sheets remove first n characters

Step 2: Use the REGEXREPLACE Function

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:

google sheets remove last n characters

How to Remove Characters in Google Sheets Using REPLACE Function

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 "".

Step 1: Identify Text and Position

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.

 

Step 2: Use the REPLACE Function

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:

Google Sheets: Remove the First N Characters

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.

Example

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.

Google Sheets: Remove the Last N Characters

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)

Example

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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->