Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I use regexreplace in Google Sheets with non-capturing groups?

I've got the following text: "1-1/3 cups warm WATER 2 tablespoons white CHIA SEED MEAL 1/4 cup melted HONEY" - I want to replace all spaces followed by a number with "ç" (1)

Usually regex in GSheets uses parenthesis to form capturing groups: when I use =REGEXEXTRACT(B3,"(\s)\d") I get a single space like I'm supposed to, but if I use =REGEXREPLACE(B3,"(\s)\d","ç") I get Ingredients:ç-1/3 cups warm WATERç tablespoons white CHIA SEED MEALç/4 cup melted HONEY

Does anybody know how to get Google to replace only the space, so that I get output like this: Ingredients:ç1-1/3 cups warm WATERç2 tablespoons white CHIA SEED MEALç1/4 cup melted HONEY? Thanks a ton!

(1) I use "ç" so that I can later use the "SPLIT" command to split on every "ç".

like image 494
Josh Avatar asked Aug 11 '14 19:08

Josh


People also ask

Can you use regex in Google Sheets?

Google products use RE2 for regular expressions. Google Sheets supports RE2 except Unicode character class matching. Learn more on how to use RE2 expressions. This function only works with text (not numbers) as input and returns text as output.

How do I extract specific text from Google Sheets?

Extract data before a certain text — LEFT+SEARCH Whenever you want to extract data that precedes a certain text, use LEFT + SEARCH: LEFT is used to return a certain number of characters from the beginning of cells (from their left) SEARCH looks for certain characters/strings and gets their position.

How do I extract numbers from a string in Google Sheets?

We use the '' symbol so that Google Sheets understands that we mean the meta-character 'd' and not the letter 'd'. So if you want to extract a single numeric digit from a string, you use the expression 'd'. But if you want to extract more than one digits, you can use the expression 'd+'.

How do you use regular expressions in Google Docs?

To use regular expressions in the Find and Replace function, type the expression into the Find box and check the "Match using regular expressions" box. For more details, including info on those expressions that are supported in Docs, check out the Help Center.


1 Answers

My way of approaching this would be to use back-references in the third argument:

=REGEXREPLACE(C3,"\s(\d)","ç$1")

like image 192
AdamL Avatar answered Sep 21 '22 17:09

AdamL