HomeGoogle DocsSpreadsheetGoogle Sheets Regexreplace Function How to and Examples

Google Sheets Regexreplace Function How to and Examples

Published on

Regex Functions give a tough time for Google Sheets users due to the lack of proper guidance. So here I am trying to explain the use of Google Sheets Regexreplace function with the help of several Regexreplace formulas.

There are three Regex Functions using RE2 regular expressions in Google Sheets. They are Regexextract, Regexreplace, and Regexmatch. These text functions are very useful for cleaning texts in Google Sheets. But in my experience, Google Sheets users are using other text functions like Find, Substitute, MID etc. to compensate Regex Functions.

In this Google Sheets tutorial or Regexreplace function how to, I am trying to explain some of the common use of Regexreplace with several examples. Needless to say, regular use of Regex is a must to master it. So let’s begin.

The Powerful Google Sheets Regexreplace Function – How to and Examples

Purpose:

The purpose of Regexreplace function is to replace part of a text string with a different text string. For this, Google Sheets Regexreplace function uses RE2 for regular expressions. What are those RE2 regular expressions? Refer the regular expression syntax reference at GitHub. Bookmark that page for your regular reference. When you have a tough time with Regexreplace or other Regex functions in Google Sheets, it would come useful.

How to Use Regexreplace Function in Google Sheets

See the syntax part first.

REGEXREPLACE(text, regular_expression, replacement)

text – The of which will be replaced.

regular_expression – Use to replace matching instances in the text.

replacement – The replacement text which will be inserted into the original text.

Formulas to Learn Google Sheets Regexreplace Function

The value in Cell A1: Info Inspired Inspirational 2017 Blog

1. Regexreplace Formula to replace any plain text

Formula:

=REGEXREPLACE(A1, "Inspirational "," - ")

Result: Info Inspired – 2017 Blog

2. When you want to replace any number using Regexreplace, use the below formula.

Formula:

=REGEXREPLACE(A1, "[0-9]+","2018")

Result: Info Inspired Inspirational 2018 Blog

3. Regexreplace Formula to remove all Spaces

Formula:

=REGEXREPLACE(A1, "\s","")

Result: InfoInspiredInspirational2017Blog

When you want to replace space with any character, follow this formula. Here I’ve used Comma as the replacement character.

Formula:

=REGEXREPLACE(A1, "\s",",")

Result: Info,Inspired,Inspirational,2017,Blog

4. Regexreplace Formula to replace any of the character (this or that)

Formula:

=REGEXREPLACE(A1, "o\b","ormation")

Result: Information Inspired Inspirational 2017 Blog

5. Regexreplace formula to replace the Last word of a sentence or a certain number of characters from the end. You can either remove it by replacing space or add new text.

Formula:

=REGEXREPLACE(A1, "....\z","")

Result: Info Inspired Inspirational 2017

Now another formula.

Formula:

=REGEXREPLACE(A1, "....\z","Professional Blog")

Result: Info Inspired Inspirational 2017 Professional Blog

OR the below formula can also produce the same result.

=REGEXREPLACE(A1, "....$","Professional Blog")

6. Regex Replace to replace a certain number of characters or word from the beginning.

Formula:

=REGEXREPLACE(A1, "^....","Newly")

Result: Newly Inspired Inspirational 2017 Blog

Cell Reference is Cell A2.

I have paid USD 100 for my blog maintenance last month and USD 60 this month

7. Regexreplace formula to replace all the numbers from a text in Google Sheets.

Formula:

=REGEXREPLACE(A2, "[\d]","")

Result: I have paid USD for my blog maintenance last month and USD this month

Cell Reference is Cell A3.

I have paid USD 100.25 for my blog last month and this month it’s USD 60.50

8. When you want to replace or remove all numbers in a text including decimal places, below is the Regexreplace formula.

Formula:

=REGEXREPLACE(A3, "[\d\.]+", "")

or

=REGEXREPLACE(A3, "[0-9]*\.[0-9]+[0-9]+","")

Result: I have paid USD for my blog last month and this month it’s USD

See the above result. The text USD is still there.

9. You can remove or replace any prefix to a number using Regexreplace as below.

Formula:

=REGEXREPLACE(A3, "(\w*)\s[\d\.]+","")

Result: I have paid for my blog last month and this month it’s

10. When you want to remove all texts and extract numbers, use the below formula.

Formula:

=REGEXREPLACE(A3, "[^\d\.]+", "|")

Result: |100.25|60.50

When there are no decimal places, the formula would be;

=REGEXREPLACE(A3, "[^\d]+", "|")

Please follow my related tutorial below.

How to Extract All Numbers from Text and SUM It in Google Sheets

11. Google Sheets Regex Formula to Replace special characters around a word or text.

Cell A4 Value:

Can you ~replace~ me!

Formula:

=REGEXREPLACE(A4, "\~([A-Za-z]+)\~","replace")

Result: Can you replace me!

Cell A5 Value:

Can you <replace> me!

Formula:

=REGEXREPLACE(A5, "\<([A-Za-z]+)\>","replace")

Result: Can you replace me!

Or

=REGEXREPLACE(A5, "<(.+)>","replace")

That’s all for now! Open a blank Google sheet, apply our above formulas and go to the RE2 syntax and experiment with different expressions. So that you can be an expert in Google Sheets Regex functions.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

33 COMMENTS

  1. Hi Prashanth,

    I’m trying to do an initial capitalization, and I’m having trouble with the replacement text. I can’t use PROPER as that will turn some characters into lowercase, such as the “X” in my example below.

    Sample text: abc de-fgh IX
    Expected result: AbcDeFghIX

    • Hi Brian,

      I don’t think that’s doable with REGEXREPLACE in Google Sheets. As a workaround, you can use the following formula:

      Assume the sample text is in cell A1.

      =JOIN("",MAP(SPLIT(SUBSTITUTE(A1,"-"," ")," "),
      LAMBDA(r,JOIN("",UPPER(LEFT(r)),MID(r,2,LEN(r)-1)))))

      This formula will capitalize the first letter of each word in the text in cell A1, leaving capital letters as they are in the original text.

      I hope this helps! Let me know if you have any other questions.

      • This is great! Thank you. I haven’t used Lambda before, so I need to wrap my head around how this works. The initial test looks good.

  2. Hi Prashanth!

    I’m trying to replace instances of ), with )| so I can easily split them later, but wondering if this is possible with the Regexpreplace?

    For more context, I have some cell entries that are formatted like “Math (CO, 9-6PM, MW), English (CO, 11-12PM, T), Social Studies (LIN, 1-3PM, TH/F)” and no entry is necessarily the same number of classes/length. I want to change just the commas following the end parenthesis to be | or another character that would be easier to use in a split.

    Thank you so much!

  3. Hello,

    I am using regexreplace for replacing special characters:

    EX: “>Ngô Thị Hà Mi” into “Ngo Thi Ha Mi”

    But it doesn’t work. Could you please help me?

    Thank you so much

    • Hi, Ngo Thi Ha Mi,

      I think the function won’t help in this case. You can try Google Apps Script.

      I’m not an expert in Apps Script. I could find one solution HERE (external link).

  4. Hi Prashanth,

    I cannot thank you enough for your answer. It works perfectly!

    I was searching for a more efficient solution (using regexreplace), but this one seems fine. I learn a lot just by reviewing it.

    Thank you very much!

    Have a nice week,
    Yann

  5. Hi Prashanth,

    I discovered your posts yesterday, and they are helpful!

    I have an issue: I would like to extract some numbers in a Google Sheets cell, but I struggle with it.

    Example:

    Lorem ipsum dolor BCN-N:2345 sit amet, consectetur – adipiscing BCN-N:1325-e elit

    The result that I want to have is: 2345, 1325-e

    Knowing that the numbers are always after “BCN-N:”

    So far, I’ve only managed to erase what I need.

    Thank you very much in advance.

    • Hi, Yann,

      There may be a much more efficient way. But you can try this one.

      =textjoin(",",true,ArrayFormula(substitute(filter(flatten(split(B2," ")),regexmatch(flatten(split(B2," ")),"(?i)bcn")),"BCN-N:","")))

  6. Hi there Prashanth.

    I’m new to google sheets and I find this REGEXREPLACE function very useful. My situation is this.

    I am asked to make the 12 character mac addresses to be converted to a format with a colon for every 2 characters. My problem is when I use this function =REGEXREPLACE(A1,".{2}", "$0:") the output will be like this;

    01:23:45:67:89:ab:

    How do I remove the last colon to the right?

  7. Hi Prashanth, already bookmarked your article

    What would be the easiest way to replace special symbols with appropriate letters in the alphabet?
    I am guessing that is too much to ask for and it probably needs to be done per case basis.

    ie:

    sàn franchiscó to san-franchisco

    • Hi, Andrey,

      I would prefer the “Find and Replace” menu command in this case.

      There is another way, but I don’t know how much practical.

      It’s like,

      Keep a table with the special characters in question and its equivalent alphabets.

      Split the word “sàn franchiscó”, transpose it, and use Vlookup to get the replacement. Then join the split text.

      • Yes, that makes sense, that’s the way I would normally do it, or with a script. Just new to Regexreplace. Seems pretty cool.
        Thanks! 🙂

  8. Hi there, Prashanth. Thanks for your article! It really helpful,

    But I struggle to master REGEXREPLACE function for my needs and no luck. Perhaps you could help?

    – I need the sheet to contain only lowercase letters in the English alphabet (no letters like èüį and other symbols, except hyphen), no spaces but can contain – (hyphen) and numeric symbols (0-9).

    So, content can be only like:

    san-francisco-1 (English lowercase letters, number, and hyphen only)

    but can’t be:

    San Francisco 1 (Title cased, space)
    or
    sàn-franchiscó_1 (é-like letters, _ symbol)

    Is there a REGEXREPLACE or REGEXMATCH (or maybe a hybrid, idk) function that will help to validate that or will help to replace è-like letters to normal English ones?

  9. Hi Prashanth,

    Great article on Regexreplace. I was trying to remove a list of stop words from a cell and replace it with a character so that I could split it to get an array of keywords.

    I want to use this array of keywords to search how many rows in a column (containing headlines) have those keywords.

    This is what I am using. I am very new to Regexreplace or google sheets for that matter; it would be great if you could help me out.

    This is my cell content.

    “Era of innovation Square Peg surpasses $1 billion in funding assets as Paul Bassat predicts post”

    I used the following formula to remove stop words

    =REGEXREPLACE(lower(A2),"\b("&'Common Words'!$B$2&")\b","")

    The result I got is this:

    “era innovation square peg $1 paul bassat predicts post”

    But since I figured I can’t split it, I tried replacing it with a character (@), so that I could split it but this is what happens:

    =REGEXREPLACE(lower(A2),"\b("&'Common Words'!$B$2&")\b","@")

    Result:

    @era@ @@ @innovation@ @square@ @peg@ @@ $@1@ @@ @@ @@ @@ @@ @paul@ @bassat@ @predicts@ @post@

    Common Words'!B2 is the list of the stop words that I want to remove which are joined by “|”

    If there is an easier query syntax that would help me solve this that will also work.

    Thanks in advance!

    • Hi, Lima James,

      Since you have not shared the values (regex pattern) in 'Common Words'!B2 I’m unable to replicate the problem in my sheet. I assumed the pattern below.

      of|surpasses|billion|in|funding|assets|as

      But as per this, your formula returns a different result!

      As per the result shown by you, you may be able to substitute the double “@” sings with space characters and trim the result before splitting.

      =split(SUBSTITUTE(trim(SUBSTITUTE(REGEXREPLACE(lower(A2),"\b("&'Common Words'!$B$2&")\b","@"),"@@","")),"@ @","@"),"@")

      I may be able to help you solve this problem if I can access a sample/copy of your sheet.

  10. Hi Prashanth!

    First of all, thank you very much for all your Google Sheets articles. 🙂

    I want to reformat some copy / pasted values like ‘€24.90’ into ‘24,90’ in order to be able to work with them on Google Sheets.

    1. Would you recommend using Regexreplace Function to do so?

    2. If so, how would you do it?

    Thanks in advance and congrats again for your articles!

    Tomas

    • Hi, Tomas,

      Assume the said amount in text format to convert is in cell D1. Try either of the below Regex formulas in cell E1.

      =REGEXREPLACE(D1&"";"\.";",")*1

      or

      =REGEXREPLACE(REGEXEXTRACT(D1&"";"[0-9.]+");"\.";",")*1

      For column D, the array formula equivalent of the same.

      =ArrayFormula(if(D1:D="";;REGEXREPLACE(D1:D&"";"\.";",")*1))

      or

      =ArrayFormula(if(D1:D="";;REGEXREPLACE(REGEXEXTRACT(D1:D&"";"[0-9.]+");"\.";",")*1))

      Best,

  11. Amazing article!

    I am trying to replace a text with a cell with numbers in them using some form of regex but it keeps giving me error messages because it says that it only expects text and 3 is not a text.

    Do you have any advice here?

    I tried the following to circumvent the “numbers” problem, but to no avail, since Substitute just introduces other problems and I figure REGEXREPLACE would just be the simpler way to do it.

    =IF(REGEXMATCH(JH5, "School")=true,Substitute(JH5, "School",C14,1))

    Using Regex;

    =REGEXREPLACE(JH5, "School",C14)

    C14 holds the calculated number which I reference as Schools, and I want to use it to detect if there are Schools, and if so, replace Schools with the number contained in C14

    Ie.
    C14 = 5
    There are School children = there are 5 children
    There are House children = there are house children
    There are School children and House children = there are 5 children and House children

    • Hi, Mads Lundholm,

      You can follow the below method to solve the REGEXREPLACE error saying “parameter 3 expects text values”.

      =REGEXREPLACE(JH5, "School",""&C14)

      Best,

      Prashanth KV

  12. Any way to replace spaces?

    I tried adding \s but it didn’t work, here is my formula:

    =ArrayFormula(IF(C2:C"",ArrayFormula(IF(len(trim(REGEXREPLACE(K2:K, "[A-Za-z-áéíôüöółØÜçë'èãúŁøšÂÇñ]", "")))>0,TRUE,FALSE)),""))

    Thank you!

    • Hi, David,

      =ArrayFormula(REGEXREPLACE(K2:K, "[\s A-Za-z-áéíôüöółØÜçë'èãúŁøšÂÇñ]", ""))

      To convert to numeric;

      =ArrayFormula(if(len(K2:K),(REGEXREPLACE(K2:K, "[\s A-Za-z-áéíôüöółØÜçë'èãúŁøšÂÇñ]", "")*1),))

  13. Great article! I am trying to use the REGEXREPLACE formula to insert a new line between a comma and space and before an uppercase letter. I have tried a bunch of variations but haven’t had any luck so far. Do you have any suggestions on how this could be accomplished?

    For example, I have a cell with the text: Great job!, Keep it up.
    The result should be:
    Great job!
    Keep it up.

    I have tried the following formula below but this removes the K from “Keep it up.”
    =regexreplace(A1,"\,\s+[A-Z]",char(10))

  14. Note that the quote characters used in the web page (left quote and right quote) are not the same as the quote characters expected by the function (straight quote).

    • Hi Phil Miller,
      Welcome! You are absolutely right!
      Actually I’m using the correct double quotes in formulas. I am testing every formulas in my sheet and then copied the same to blog posts. But my blog theme automatically converting the double quotes. So in posts I keep on reminding my readers to re-type the double quotes so that the formula works. But sadly I can’t repeat it in all the posts as it may cause SEO issue.
      Thanks for the drop by.

    • Hey! my one column has a designation like Sr. Marketing Manager or Marketing Associate.

      So in another column, I want that if there is the word “Marketing” in the text, fill in “Marketing Team” beside it and if there is “Sales” in the text, fill in “Sales Team.”

      The problem is designations are not certain. These keywords also contain other texts with them.

      Please help me!

      • Hi, Shashank Singh,

        I assume the designations are in C3:C. If so, try this REGEXMATCH and IF combo in D3.

        =ArrayFormula(ifna(if(REGEXMATCH(C3:C,"(?i)marketing"),"Marketing Team",if(REGEXMATCH(C3:C,"(?i)sales"),"Sales Team",))))

LEAVE A REPLY

Please enter your comment!
Please enter your name here