Join the top marketers who read our newsletter each week.

How to remove non-alphabet characters from a string/cell in Google Sheets ?

Recently, I was asked how to remove all special characters from a cell in Google Sheets. This article is going to be short so here we go.

Using RegexReplace in Google Sheets

For the purpose of this article, we are going to use the formula =REGEXREPLACE. This is how it works:

This formula allows us to replace text found in a cell using RE2. RE2 is short for Regular Expressions 2 and is a variant of the classic Regular Expressions by Google. If you are familiar with regex, you need to know that Google does not support every way you can use regex.

If you’re not familiar with Regular Expressions, you can read the Syntax Documentation that Google has made available on their Github repository at this address. For the purpose of this article, I will just share the answer and not go into details on what you can do with RE2 but you are free to study this document and do your own experimentations.

Our dataset today consists of a list of strong passwords that I created using the Password generator at passwordgenerator.net . There is no purpose to this tutorial other than experimenting with RE2 and having fun.

Here is the dataset:

Replacing Characters with REGEXREPLACE

Now that we have our dataset, here is how we are going to write our formula:

=REGEXREPLACE(A1,"[^A-Za-z]+","")

In common words, this is what it means: In the Cell A1, find every character that is not between a-z and A-Z and replace it with nothing.

The ^ sign basically means opposite of. In this case, opposite of alphabet characters. The + after the brackets means that this formula applies for the whole string and not just for the first time a character matches this formula

And this is the result if we drag down this formula in front of the dataset I showed earlier:

Conclusion

And this is it really. I know this is a short article but it really was just for the benefit of experimentation. I hope this formula is going to reveal itself useful for some of you. If you have any questions, don’t hesitate to leave me a comment in the section below.

Thank you for your time. Until the next one !

Join the top marketers who read our newsletter each week.

Yaniss Illoul

Share on twitter
Share on linkedin
Share on facebook
Share on reddit

You might also like these posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

function external_link_attribute() { ?>

wait!

Get more tutorials, guides and curated content !

In your inbox, once a week.

HELLO!

Get more tutorials, guides and curated content !

In your inbox, once a week.