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 "ç".
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.
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.
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+'.
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.
My way of approaching this would be to use back-references in the third argument:
=REGEXREPLACE(C3,"\s(\d)","ç$1")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With