REGEXREPLACE is a text function in Google Sheets for substring replacement in a string using pattern matching.
The REGEXREPLACE function supports RE2 regular expressions, except for Unicode character class matching.
This tutorial aims to help you perform regular substring replacement tasks. Once you become familiar with these tasks, refer to GitHub for regular expression syntax to become an advanced user.
Two more functions in Google Sheets use RE2: REGEXEXTRACT and REGEXMATCH. The former is used for substring extraction, and the latter is used for substring matching.
REGEXREPLACE Function: Syntax and Arguments
Syntax:
REGEXREPLACE(text, regular_expression, replacement)Arguments:
text– The text string, a part of which will be replaced.regular_expression– The regular expression to match substrings within thetext.replacement– The substring to replace the matched substrings within thetext.
Example 1: REGEXREPLACE To Replace the Exact Match of the Substring within a String
I have a list containing the fruit names apple, pineapple, and mango in A2:A
and want to replace “apple” with “apple US fancy”.
In B2, enter the following formula and drag it down as far as you need:
=REGEXREPLACE(A2, "\bapple\b", "apple US fancy")The REGEXREPLACE function matches “apple” case-sensitively. It doesn’t match “apple” in “pineapple” because of the \b, which denotes a word boundary.
All matching instances of “apple in the text will be replaced.
Example 2: Remove N Substrings from the Start or End of a Delimited List
When you have a delimited list such as the following comma-delimited list, you can use REGEXREPLACE to remove a certain number of items from the beginning or end of the string.
The list in cell A2 is as follows:
Alice, Bob, Charlie, David, Emma, Frank, Grace, Henry, Isabella, Jack
The following formula will remove the last five names:
=REGEXREPLACE(A2,"(\w*\W*){5}$","") // returns Alice, Bob, Charlie, David, Emma, Whereas the following one will remove the first five names:
=REGEXREPLACE(A2,"^(\w*\W*){5}","") // returns Frank, Grace, Henry, Isabella, JackExplanation:
\w*matches any word character zero or more times.\W*matches any non-word character zero or more times.^asserts position at the start of a line, whereas$asserts position at the end of a line.{5}matches the capturing group, i.e.,(\w*\W*), five times.
Example 3: Substitute Contents within Brackets
Another common substring replacement is replacing content within brackets.
Sample Text:
The conference will be held in New York (USA) next month.
How do we replace “USA” (i.e., the content within brackets) with “United States”?
=REGEXREPLACE(A2, "\(.*\)", "(United States)")Explanation:
\(: Escaped parenthesis to match an opening parenthesis literally..*: Matches any character zero or more times. This includes everything inside the parentheses.\): Escaped parenthesis to match a closing parenthesis literally.
Example 4: Swapping Values Using REGEXREPLACE Function
The following example demonstrates how to swap first and last names in a cell using the REGEXREPLACE function in Google Sheets.
Cell A2 contains “Prashanth KV”. Once swapped, it should be “KV Prashanth”.
Formula:
=REGEXREPLACE(A2, "(\w+)(?:\s)(\w+)", "$2 $1")
Explanation:
(\w+)– First capturing group that matches any word character one or more times.(?:\s)– Non-capturing group where\smatches whitespace. Unlike capturing groups( … ), a non-capturing group(?: … )does not create a numbered capturing group. This helps to group part of a pattern without capturing it for later use in back-references or replacements.(\w+)– Second capturing group that matches any word character one or more times.
The replacement string is "$2 $1", where $2 and $1 refer to the content captured in the second and first capturing groups, respectively.
Other Common Use Cases
Here are some other common use cases of the REGEXREPLACE function in Google Sheets.
Replace non-digits and periods with a pipe (|) delimiter: This returns numbers in a text separated by |. You can split them using the SPLIT function.
=REGEXREPLACE(A2, "[^0-9.]+", "|")To remove all spaces:
=REGEXREPLACE(A2, "\s", "")Replace more than one substring at a time:
=REGEXREPLACE(A2, "\b(apple|orange)\b", "fruits")Case-insensitive REGEXREPLACE formula:
=REGEXREPLACE(A2, "(?i)\b(apple|orange)\b", "fruits")Remove certain (three here) characters from the end:
=REGEXREPLACE(A2, "...\z", "")Replace a certain (three here) number of characters from the beginning:
=REGEXREPLACE(A2, "^...", "")And finally, use the following formula to separate each character with a delimiter, in this case, a comma:
=REGEXREPLACE(A2, ".{1}", "$0,")To separate every two characters with a comma, replace {1} with {2}:
=REGEXREPLACE(A2, ".{2}", "$0,")
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 IXExpected result:
AbcDeFghIXHi 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.
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!
Hi, Victoria,
=REGEXREPLACE(B3,"\),",")|")Replace B3 with your actual cell reference.
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).
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
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 elitThe 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:","")))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?
Hi, Marky,
Here you go!
=REGEXREPLACE(A1,".{2}\B", "$0:")or
=REGEXREPLACE(A1,"..\B", "$0:")Exactly what I needed. Thank you for helping me out. Saved another column of formula just to get rid of the extra column.
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! 🙂
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?
Hi, Danny,
The following Regex will extract only lowercase letters, numbers, and hyphen.
=REGEXREPLACE(A1,"[^a-z0-9-]+","")No idea about replacing non-English letters.
Thank you, Prashanth
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'!B2is 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'!B2I’m unable to replicate the problem in my sheet. I assumed the pattern below.of|surpasses|billion|in|funding|assets|asBut 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.
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&"";"\.";",")*1or
=REGEXREPLACE(REGEXEXTRACT(D1&"";"[0-9.]+");"\.";",")*1For 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,
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
Any way to replace spaces?
I tried adding
\sbut 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),))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))See if this is working!
=REGEXREPLACE(A1,"\,\s([A-Z])",char(10)&"$1")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",))))